需要先拆分字段的值,然后再进行判断: declare @t table(id int,v varchar(30)) insert into @t select 1, 'a,b' union all select 2, 'd' union all select 3, 'a,d'select distinct id, v from @t t,master..spt_values s where s.number >=1 and s.type = 'P' and SUBSTRING(','+t.v,s.number,1) = ',' and ','+'a,b,c'+',' like '%,'+SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-1) +',%' /* id v 1 a,b 3 a,d */
上面的有点问题,修改一下:declare @t table(id int,v varchar(30)) insert into @t select 1, 'a,b' union all select 2, 'd' union all select 3, 'a,d'select distinct id, v from @t t,master..spt_values s where s.number >=1 and s.type = 'P' and SUBSTRING(','+t.v,s.number,1) = ',' and ','+'a,b,c'+',' like '%,'+SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) +',%' /* id v 1 a,b 3 a,d */
declare @t table(id int,v varchar(30))
insert into @t
select 1, 'a,b' union all
select 2, 'd' union all
select 3, 'a,d'select distinct
id,
v
from @t t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','
and ','+'a,b,c'+',' like '%,'+SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-1) +',%'
/*
id v
1 a,b
3 a,d
*/
insert into @t
select 1, 'a,b' union all
select 2, 'd' union all
select 3, 'a,d'select distinct
id,
v
from @t t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','
and ','+'a,b,c'+',' like '%,'+SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) +',%'
/*
id v
1 a,b
3 a,d
*/