(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