ID NAME
1,2,3,4,5 Name1
1,2,4,5 Name1
2,3 Name2
1,4 Name3
3 Name4
2 Name5
1,2,3,4 Name6
我现在传一个值"1,2,3,4" 返回的应该是
ID NAME
2,3 Name2
1,4 Name3
3 Name4
2 Name5
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))
/*
ID name
-------------------
2,3 Name2
1,4 Name3
3 Name4
2 Name5
1,2,3,4 Name6
*/
------解决方案--------------------
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,',',''))