sql - 如何测试一行中引用特定列的一系列相等的 X 配对行？

Question

我使用的是 SQL Server 2008 R2。

考虑此表@t(ORDER BY PK DESC 的前 20 名):

PK  SK  VC  APP     M   C
==  ==  ==  ====    ==  ==================
21  7   79  NULL    0   NULL
20  9   74  1       3   20=14, 18=13, 15=2
19  6   79  1       2   19=11, 17=7
18  9   77  1       0   NULL
17  6   74  1       0   NULL
16  7   79  1       0   NULL
15  9   74  1       0   NULL
14  9   74  1       0   NULL
13  9   77  1       0   NULL
12  7   77  1       0   NULL
11  6   79  1       0   NULL
10  7   79  1       0   NULL
9   7   74  1       0   NULL
8   7   79  1       0   NULL
7   6   74  1       0   NULL
6   6   74  1       0   NULL
5   7   79  1       0   NULL
4   7   77  1       0   NULL
3   6   79  1       0   NULL
2   9   74  1       0   NULL

用这个创建:

DECLARE @t TABLE(PK INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, SK INT NOT NULL, VC INT NULL,  APP INT NULL, M INT NOT NULL,  C NVARCHAR(111) NULL);

INSERT @t (SK,VC,APP,M,C) VALUES
(7,77,1,0,NULL),
(9,74,1,0,NULL),
(6,79,1,0,NULL),
(7,77,1,0,NULL),
(7,79,1,0,NULL),
(6,74,1,0,NULL),
(6,74,1,0,NULL),
(7,79,1,0,NULL),
(7,74,1,0,NULL),
(7,79,1,0,NULL),
(6,79,1,0,NULL),
(7,77,1,0,NULL),
(9,77,1,0,NULL),
(9,74,1,0,NULL),
(9,74,1,0,NULL),
(7,79,1,0,NULL),
(6,74,1,0,NULL),
(9,77,1,0,NULL),
(6,79,1,2,'19=11, 17=7'),
(9,74,1,3,'20=14, 18=13, 15=2'),
(7,79,NULL,0,NULL)

我的任务是如果最新行(其中 APP IS NOT NULL)匹配则返回 true完成一系列X匹配对或同一组(相同当前SK)的最新行。

例如，当仅测试 2 对时，考虑到当前所需的测试是 SK=6，一旦达到 PK = 19，就会出现匹配。

比赛结果为 VC(19)=VC(11)=79 AND VC(17)=VC(7)=74

通过执行以下命令查看:

DECLARE @PairsToTest int = 2
DECLARE @SK int = 6
SELECT 
    TOP (2*@PairsToTest) 
    * 
    FROM @t 
    WHERE 
            APP IS NOT NULL 
        AND SK = @SK 
    ORDER BY SK, PK DESC

结果:

PK  SK  VC  APP M   C
19  6   79  1   2   19=11, 17=7
17  6   74  1   0   NULL
11  6   79  1   0   NULL
7   6   74  1   0   NULL

另一个例子:

测试 3 对时，在 SK=9 中查找时在 PK=20 上找到匹配项(虽然这本身就是一个有趣的问题，但对于我的任务来说，不需要测试所有 SK。给定 SK 的结果对我来说就足够了。

要查看匹配，请执行以下操作:

DECLARE @PairsToTest int = 3
DECLARE @SK int = 9
SELECT 
    TOP (2*@PairsToTest) 
    * 
    FROM @t 
    WHERE 
            APP IS NOT NULL 
        AND SK = @SK 
    ORDER BY SK, PK DESC

结果:

PK  SK  VC  APP M   C
20  9   74  1   3   20=14, 18=13, 15=2
18  9   77  1   0   NULL
15  9   74  1   0   NULL
14  9   74  1   0   NULL
13  9   77  1   0   NULL
2   9   74  1   0   NULL

如您所见:VC(20)=VC(14)=74、VC(18)=VC(13)=74 和 VC(15)=VC(2)

我想到以正确的顺序选择所需的行集，并在 VC 中计算相等的行。如果计数与 @PairsToTest 相同，则这是举旗的标志。

我尝试过:

DECLARE @PairsToTest int = 3
DECLARE @SK int = 9
;with t0 as
(
SELECT 
    TOP (2*@PairsToTest) 
    * 
    FROM @t 
    WHERE 
            APP IS NOT NULL 
        AND SK = @SK 
    ORDER BY SK, PK DESC
),
t1 AS
(
SELECT TOP (@PairsToTest) * FROM t0
),
t2 AS
(
SELECT TOP (@PairsToTest) * FROM t0 ORDER BY PK ASC 
)
,t3 AS
(
SELECT TOP 99999999 * FROM t2 ORDER BY PK DESC
)

IF (SELECT COUNT(*) FROM t1 LEFT OUTER JOIN t3 ON t1.VC = t3.VC) = @PairsToTest 
    SELECT 1
ELSE
    SELECT 0

但是这也有太多缺陷:

1. VC 不包含唯一数据(纯属偶然)
2. 不允许使用 IF
3. 我应该去掉 t3 中的 TOP 99999999(尽管我可以接受)

为了解决这个问题，我需要进行哪些更改？

Answer 1

DECLARE @t TABLE(PK INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, SK INT NOT NULL, VC INT NULL,  APP INT NULL, M INT NOT NULL,  C NVARCHAR(111) NULL);

INSERT @t (SK,VC,APP,M,C) VALUES
(7,77,1,0,NULL),
(9,74,1,0,NULL),
(6,79,1,0,NULL),
(7,77,1,0,NULL),
(7,79,1,0,NULL),
(6,74,1,0,NULL),
(6,74,1,0,NULL),
(7,79,1,0,NULL),
(7,74,1,0,NULL),
(7,79,1,0,NULL),
(6,79,1,0,NULL),
(7,77,1,0,NULL),
(9,77,1,0,NULL),
(9,74,1,0,NULL),
(9,74,1,0,NULL),
(7,79,1,0,NULL),
(6,74,1,0,NULL),
(9,77,1,0,NULL),
(6,79,1,2,'19=11, 17=7'),
(9,74,1,3,'20=14, 18=13, 15=2'),
(7,79,NULL,0,NULL)


DECLARE @PairsToTest int = 3
DECLARE @SK int = 9

IF ((SELECT COUNT(*) FROM @t WHERE APP IS NOT NULL AND SK = @SK)-@PairsToTest) >=0
    BEGIN
        DECLARE @swapData  TABLE(PK1 INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, PK INT NOT NULL, SK INT NOT NULL, VC INT NULL,  APP INT NULL, M INT NOT NULL,  C NVARCHAR(111) NULL);
        DECLARE @olderData TABLE(PK2 INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, PK INT NOT NULL, SK INT NOT NULL, VC2 INT NULL,  APP INT NULL, M INT NOT NULL,  C NVARCHAR(111) NULL);
        DECLARE @newerData TABLE(PK3 INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, PK INT NOT NULL, SK INT NOT NULL, VC3 INT NULL,  APP INT NULL, M INT NOT NULL,  C NVARCHAR(111) NULL);


        INSERT @swapData  SELECT TOP ((SELECT COUNT(*) FROM @t WHERE APP IS NOT NULL AND SK = @SK)-@PairsToTest) * FROM @t WHERE APP IS NOT NULL AND SK = @SK ORDER BY PK
        INSERT @olderData SELECT TOP (@PairsToTest) PK,SK,VC,APP,M,C FROM @swapData ORDER BY PK1 DESC
        INSERT @newerData SELECT TOP (@PairsToTest) * FROM @t WHERE APP IS NOT NULL AND SK = @SK ORDER BY SK, PK DESC



        DECLARE @Matches int = (SELECT COUNT(*)FROM @newerData INNER JOIN @olderData ON PK2 = PK3 WHERE VC2=VC3)

        IF @Matches = @PairsToTest 
            SELECT 1 AS Match 
        ELSE 
            SELECT 0 AS Match
    END
ELSE
    SELECT 0 AS Match

/*
SELECT TOP (2*@PairsToTest) * FROM @t WHERE APP IS NOT NULL AND SK = @SK ORDER BY SK, PK DESC
SELECT * FROM @olderData
SELECT * FROM @newerData
*/