SQL 比较一个集合是否在另一个集合里存在的方法分享
前端之家收集整理的这篇文章主要介绍了
SQL 比较一个集合是否在另一个集合里存在的方法分享，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
<div class="codetitle"><a style="CURSOR: pointer" data="30331" class="copybut" id="copybut30331" onclick="doCopy('code30331')"> 代码如下:
<div class="codebody" id="code30331">
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
