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