我创建了表值函数,它接受两个参数,第一个是带有 ID 的字符串,第二个是字符串中的分隔符。
CREATE FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
returns @temptable TABLE (items nvarchar(4000))
as
begin
declare @idx int
declare @slice nvarchar(4000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
创建函数后,只需使用UNION
按此方式设置运算符:
EDITED
WITH ListCTE AS
(
select items from dbo.split('400,600,150,850,160,250', ',')
union
select items from dbo.split('600,150,900', ',')
)
SELECT TOP 1
MemberList = substring((SELECT ( ', ' + items )
FROM ListCTE t2
ORDER BY
items
FOR XML PATH( '' )
), 3, 1000 )FROM ListCTE t1
With UNION
您将自动从两个字符串中获取不同的值,因此您不需要使用DISTINCT
clause