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

190 阅读 0 评论 126 点赞

我是靠谱客的博主懦弱眼睛，最近开发中收集的这篇文章主要介绍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 