(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