create table #t(
ids int identity,
name varchar(100)
)
insert into #t(name)
select '1,2,3'
union all
select '4,2,3'
union all
select '5,2,3'
union all
select '6,2,3'
union all
select '1,4,3'
union all
select '1,5,3'
union all
select '1,6,3'
union all
select '1,2,4'
union all
select '1,2,5'
union all
select '1,2,6'
union all
select '7,8,9'
union all
select '1,8,9'
union all
select '2,8,9'
union all
select '3,8,9'
union all
select '4,8,9'
union all
select '5,8,9'
union all
select '6,8,9'
union all
select '5,6,9'
union all
select '5,7,9'
union all
select '5,3,9'
union all
select '5,0,9'
select * from #t where exists(
select * from dbo.split(#t.name,',') as n
inner join (select * from dbo.split('5,9',',')) as m
on n.f1=m.f1
)
结果
同理:
select * from #t where exists(
select * from dbo.split(#t.name,',')
where f1 in (5,9)
)
你看懂了吗?
付:split 函数
ALTER function [dbo].[split](
@SourceSql varchar(8000),
@StrSeprate varchar(10)
)
returns @temp table(F1 varchar(100))
as
begin
declare @i int
set @SourceSql = rtrim(ltrim(@SourceSql))
set @i = charindex(@StrSeprate,@SourceSql)
while @i >= 1
begin
if len(left(@SourceSql,@i-1))>0
begin
insert @temp values(left(@SourceSql,@i-1))
end
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql <> ''
insert @temp values(@SourceSql)
return
end