多选项查询，适用于爱好等的搜索，有时何必行转列

来源：互联网发布：微软字体打包下载 mac 编辑：程序博客网时间：2024/06/10 10:25

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