如何返回按Id分组且包含相关引用字段的行？

编程语言 2026-07-12

假设我有这张表和一些值：

DECLARE @TEST AS TABLE
(
   GroupId INT NOT NULL,
   ItemId INT NOT NULL,
   Ref1 NVARCHAR(10) NULL,
   Ref2 NVARCHAR(10) NULL
)

INSERT INTO @TEST 
VALUES
   (1, 1, NULL, N'A123X'),
   (1, 2, N'A123', N'10999'),

   (2, 1, N'A456', N'10998'),   -- should not be returned
   (2, 2, N'A789', N'10997'),   -- should not be returned

   (3, 1, NULL, N'A0134X'),
   (3, 2, N'A0134', N'10996'),
   (3, 3, N'A0144', N'10995'),  -- should not be returned

   (4, 1, N'A15', N'10994'),    -- should not be returned
   (4, 2, N'A17', N'10994'),    -- should not be returned

   (5, 1, NULL, N'00108P'),     -- should not be returned
   (5, 2, NULL, N'00109P'),     -- should not be returned

   (6, 2, NULL, N'00107P');     -- should not be returned

需要返回的结果

每个分组要至少有两行
在每个分组内：
第1行：对于任意行 where Ref1 = null
第2行：Ref1 = LEFT(Ref2, LEN(Ref1)) from Row1

在此示例中的最终结果将返回来自 GroupId = 1 的两项，以及来自 GroupId = 3 的前两项。GroupIds 2、4、5或 6应完全不返回。

最终结果应如下所示：

分组ID	项目ID	参考1	参考2
1	1	空值	A123X
1	2	A123	10999
3	1	空值	A013X
3	2	A013	10996

我当前使用表表达式的方法已经实现了大部分目标。

WITH groupCte(GroupId, ItemId, Ref1, Ref2 ) AS
(
   SELECT T.GroupId, T.ItemId, T.Ref1, T.Ref2 
   FROM @TEST T
      INNER JOIN
         (
            SELECT GroupId 
            FROM @TEST 
            GROUP BY GroupId 
            HAVING COUNT(*) >= 2 
         ) K ON K.GroupId = T.GroupId
),

finalCTE(GroupId, ItemId, Ref1, Ref2) AS
(
   SELECT D.GroupId, D.ItemId, D.Ref1, D.Ref2 
   FROM groupCte D
   WHERE D.Ref1 IS NULL

   UNION

   SELECT D.GroupId, D.ItemId, D.Ref1, D.Ref2 
   FROM groupCte D 
      INNER JOIN 
         (
            SELECT GroupId, Ref2
            FROM groupCte E 
            WHERE E.Ref2 IS NOT NULL
         ) K ON K.GroupId = D.GroupId
                  AND LEFT(K.Ref2, LEN(D.Ref1)) = D.Ref1    
   WHERE D.Ref1 IS NOT NULL
)

SELECT * 
FROM finalCTE

然而这仍然会返回来自 GroupId = 5 的项。

我该如何过滤掉这种情况？

解决方案

如果你对每个 Ref1 IS NULL 行，总是从每个分组中恰好选择两行（或两行都不选）的话，可以：

先用简单的 Ref1 IS NULL 条件选出第一行。
通过在 GroupId 和 Refx 值上进行匹配，与第二行进行内连接
将结果在一个 CROSS APPLY 中重新组织。

SELECT T.GroupId, T.ItemId, T.Ref1, T.Ref2
FROM @TEST T1
JOIN @TEST T2
    ON T2.GroupId = T1.GroupId
    AND T2.Ref1 = LEFT(T1.Ref2, LEN(T2.Ref1))
CROSS APPLY (
    SELECT T1.GroupId, T1.ItemId, T1.Ref1, T1.Ref2
    UNION ALL
    SELECT T2.GroupId, T2.ItemId, T2.Ref1, T2.Ref2
) T  
WHERE T1.Ref1 IS NULL;

结果如下：

分组ID	项目ID	参考1	参考2
1	1	空值	A123X
1	2	A123	10999
3	1	空值	A0134X
3	2	A0134	10996

上述假设对每个初始行至多只有一个其他行匹配，且不存在歧义。例如，下面的数据可能会导致对第一行产生多重匹配：

INSERT INTO @TEST
VALUES
   (7, 1, NULL, N'X1234Z'),
   (7, 2, N'X123', N'20997'),   -- Both are matching prefixes to 'X1234Z'
   (7, 3, N'X1234', N'20996');  -- Both are matching prefixes to 'X1234Z'

如果这种情况有可能，你可能需要改进你的匹配条件，以更好地执行前缀/后缀命名约定。也许可以用类似于 T1.Ref2 LIKE T2.Ref1 + '[A-Z]'，如果被去掉的后缀始终只是一个字母。或者也可以用一个 CROSS APPLY(SELECT TOP 1 ...)，在有多个候选项时选择“最佳”匹配值。

CROSS APPLY (
    -- Select at most one best match
    SELECT TOP 1 T2.*
    FROM @TEST T2
    WHERE T2.GroupId = T1.GroupId
    AND T2.Ref1 = LEFT(T1.Ref2, LEN(T2.Ref1))
    ORDER BY LEN(T2.Ref1) DESC  -- Prefer longest
) T2

请参阅这个db<>fiddle 以获取演示。

SQL