SQL server 2005 语句 高手请进
ID NAME
1,2,3,4,5 Name1
1,2,4,5 Name1
2,3Name2
1,4Name3
3Name4
2Name5
1,2,3,4 Name6
我现在传一个值"1,2,3,4" 返回的应该是
ID NAME
2,3Name2
1,4Name3
3Name4
2Name5
1,2,3,4 Name6
说明：就是返回的应该是包含在"1,2,3,4"中的，即他的几种组合。 sql?语句
[解决办法]
create table zh
(ID varchar(15),NAME varchar(10))
insert into zh
select '1,2,3,4,5', 'Name1' union all
select '1,2,4,5', 'Name1' union all
select '2,3', 'Name2' union all
select '1,4', 'Name3' union all
select '3', 'Name4' union all
select '2', 'Name5' union all
select '1,2,3,4', 'Name6'
declare @x varchar(10)
select @x='1,2,3,4'
select ID,NAME from
(select a.ID,a.NAME,
case when charindex(substring(a.ID,b.number,charindex(',',a.ID+',',b.number)-b.number),@x,1)>0
then 1 else 0 end 'h'
from zh a, master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.ID) and substring(','+a.ID,b.number,1)=',') t
group by ID,NAME
having count(1)=sum(h)
/*
ID NAME
--------------- ----------
2,3 Name2
1,4 Name3
3 Name4
2 Name5
1,2,3,4 Name6
(5 row(s) affected)
*/
;with cte(ID,name) as
(
select '1,2,3,4,5','Name1'
union all select '1,2,4,5','Name1'
union all select '2,3','Name2'
union all select '1,4','Name3'
union all select '3','Name4'
union all select '2','Name5'
union all select '1,2,3,4','Name6'
),
cte1 as
(
SELECT SUBSTRING(a.ID,number,CHARINDEX(',',a.ID+',',number)-number) as ID,a.Name
from cte a, master..spt_values
where number >=1 and type='p' and number<=len(a.ID) and substring(','+a.ID,number,1)=','
),
cte2 as
(
SELECT SUBSTRING(a.Name,number,CHARINDEX(',',a.Name+',',number)-number) as Name
from (select '1,2,3,4' as Name) a, master..spt_values
where number >=1 and type='p' and number<=len(a.Name) and substring(','+a.Name,number,1)=','
)
select *
from cte
where name not in(select name from cte1 where ID not in(select name from cte2))
/*
IDname
-------------------
2,3Name2
1,4Name3
3Name4
2Name5
1,2,3,4Name6
*/
with a(a)as(
select '1,2,3,4,5' union
select '1,2,4,5' union
select '2,3' union
select '1,4' union
select '3' union
select '2'
)
select a from a,master..spt_values b
where type='p' and
substring(','+a,number,1)=',' and
charindex(','+substring(a+',',number,charindex(',',a+',',number)-number)+',',
','+'1,2,3,4'+',')>0
group by a
having sum(len(substring(a+',',number,charindex(',',a+',',number)-number)))
=len(replace(a,',',''))
