SQL 比较一个集合是否在另一个集合里存在的方法分享

复制代码 代码如下:

declare @c int

declare @c2 int

select @c = count(1)

from dbo.splittotable(‘1|2|3|4’, ‘|’)

select @c2=count(1)

from dbo.splittotable(‘1|2|3|4’, ‘|’) a

inner join dbo.splittotable(‘1|2|3|’, ‘|’) b on a.value = b.value

if @c = @c2

select ‘ok’

else

select ‘no’

splittotable这个函数如下：

set ansi_nulls on

set quoted_identifier on

go

alter function [dbo].[splittotable]

(

@splitstring nvarchar(max) ,

@separator nvarchar(10) = ‘ ‘

)

returns @splitstringstable table

(

[id] int identity(1, 1) ,

[value] nvarchar(max)

)

as

begin

declare @currentindex int ;

declare @nextindex int ;

declare @returntext nvarchar(max) ;

select @currentindex = 1 ;

while ( @currentindex <= len(@splitstring) )

begin

select @nextindex = charindex(@separator, @splitstring,

@currentindex) ;

if ( @nextindex = 0

or @nextindex is null

)

select @nextindex = len(@splitstring) + 1 ;

select @returntext = substring(@splitstring,

@currentindex,

@nextindex – @currentindex) ;

insert into @splitstringstable

( [value] )

values ( @returntext ) ;

select @currentindex = @nextindex + 1 ;

end

return ;

end