使用自定义记录集参与查询(VALUES关键字,适用于SQL Server 2008)
请参考:没有数据源的情况下自定义记录集(VALUES关键字,适用于SQL Server 2008)
declare @t1 table
(
[id] bigint,
[str] varchar(50)
);
Insert into @t1([id], [str]) values(1, 's1,s2,s3');
Insert into @t1([id], [str]) values(2, 's3,s5');
Insert into @t1([id], [str]) values(3, 's1,s4,s7');
Insert into @t1([id], [str]) values(4, 's2,s6');
Insert into @t1([id], [str]) values(5, 's4,s7,s8');
select distinct str from @t1 as TS
INNER JOIN (values('s1'),('s2'),('s3')) as TT(TT_Name) ON
CHARINDEX(',' + TT_Name + ',', ',' + TS.str + ',', 0) > 0 ;
结果集:
str
-------------------
s1,s2,s3
s1,s4,s7
s2,s6
s3,s5