SQL Server查询：如何筛选满足Ref1与Ref2关联规则的分组并返回对应行

看起来你已经接近目标了，问题出在当前的逻辑没有严格限制分组里必须同时存在符合配对条件的Ref1 NULL行和Ref1非NULL行，而且没有排除分组里那些不满足配对规则的额外行。咱们一步步修正这个问题：

首先明确核心规则（结合你的示例和需求）：

• 分组必须包含至少一对符合规则的行：
  • 一行是Ref1 IS NULL且Ref2非空（否则无法匹配）
  • 同组内至少有一行Ref1 IS NOT NULL，且该行的Ref1等于LEFT(Ref1-NULL行的Ref2, LEN(Ref1))
• 最终只返回分组里的这些符合配对规则的行，无关行（比如Group3的第三行）不返回
• 满足配对条件的分组自然符合“至少2行”的要求

你的当前代码问题 #

你的finalCTE第一部分直接选取了所有分组里Ref1为NULL的行，哪怕这个分组里没有任何能和它配对的Ref1非NULL行（比如Group5），所以Group5的行被错误保留了。咱们需要先找出所有有效配对，再基于配对来筛选返回的行。

修正后的查询方案 #

WITH ValidPairs AS (
    -- 先找出所有符合配对规则的行组合：Ref1 NULL行 关联 同组内匹配的Ref1非NULL行
    SELECT 
        R0.GroupId,
        R0.ItemId AS NullItemId,
        R0.Ref2 AS NullRef2,
        R1.ItemId AS NonNullItemId,
        R1.Ref1 AS NonNullRef1,
        R1.Ref2 AS NonNullRef2
    FROM @TEST R0
    INNER JOIN @TEST R1 
        ON R0.GroupId = R1.GroupId
        AND R0.Ref1 IS NULL 
        AND R1.Ref1 IS NOT NULL
        -- 核心匹配规则：R1的Ref1等于R0的Ref2取前LEN(R1.Ref1)位
        AND LEFT(R0.Ref2, LEN(R1.Ref1)) = R1.Ref1
),
ValidItems AS (
    -- 收集所有需要返回的行ID：包括配对里的NULL行和非NULL行
    SELECT GroupId, NullItemId AS ItemId FROM ValidPairs
    UNION
    SELECT GroupId, NonNullItemId AS ItemId FROM ValidPairs
)
-- 从原表中取出所有有效行，用DISTINCT避免同一行被多次匹配的情况
SELECT DISTINCT T.*
FROM @TEST T
INNER JOIN ValidItems VI 
    ON T.GroupId = VI.GroupId 
    AND T.ItemId = VI.ItemId
ORDER BY T.GroupId, T.ItemId;

效果验证 #

• Group1：Row1（Ref1 NULL）和Row2（Ref1=A123）配对成功，两行都返回，符合预期
• Group2：无Ref1 NULL行，无有效配对，不返回
• Group3：Row1和Row2配对成功，Row3无对应配对，仅返回前两行，符合预期
• Group4：无Ref1 NULL行，无有效配对，不返回
• Group5：所有行都是Ref1 NULL，无配对的非NULL行，不返回
• Group6：仅一行且无配对对象，不返回

关键逻辑说明 #

1. ValidPairs CTE：专门定位所有满足Ref1与Ref2关联规则的行对，从根源上过滤掉无效分组
2. ValidItems CTE：汇总所有需要保留的行ID，确保不遗漏配对里的任何一行
3. 最终查询：关联原表取出有效行，用DISTINCT处理极端情况（比如一个NULL行对应多个非NULL行的场景）

备注：内容来源于stack exchange，提问作者EMAW2008