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

最新推荐文章于 2025-10-13 04:02:29 发布

原创最新推荐文章于 2025-10-13 04:02:29 发布 · 757 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #table #function #join

sql server 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一种使用SQL自定义函数split处理字符串的方法，并演示了如何通过子查询筛选特定条件的数据记录。通过实例展示了复杂SQL语句的构建过程。

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