心有法竹
创建此函数(sqlserver 2005+)CREATE function [dbo].[f_split](@param nvarchar(max), @delimiter char(1))returns @t table (val nvarchar(max), seq int)asbeginset @param += @delimiter;with a as(select cast(1 as bigint) f, charindex(@delimiter, @param) t, 1 sequnion allselect t + 1, charindex(@delimiter, @param, t + 1), seq + 1from awhere charindex(@delimiter, @param, t + 1) > 0)insert @tselect substring(@param, f, t - f), seq from aoption (maxrecursion 0)returnend使用此语句SELECT *FROM yourtable WHERE account in (SELECT val FROM dbo.f_split(@account, ','))比较我的split函数和XML split:测试数据:select top 100000 cast(a.number as varchar(10))+','+a.type +','+ cast(a.status as varchar(9))+','+cast(b.number as varchar(10))+','+b.type +','+ cast(b.status as varchar(9)) txt into a from master..spt_values a cross join master..spt_values bXML: SELECT count(t.c.value('.', 'VARCHAR(20)')) FROM ( SELECT top 100000 x = CAST('<t>' + REPLACE(txt, ',', '</t><t>') + '</t>' AS XML) from a ) a CROSS APPLY x.nodes('/t') t(c)Elapsed time: 1:21 secondsf_split:select count(*) from a cross apply clausens_base.dbo.f_split(a.txt, ',')Elapsed time: 43 seconds这会随着时间的流逝而变化,但是您会明白