SQL多维度分组：组间关联失效时的成员排除需求

我明白你的核心需求了：当一条数据同时属于多个分组时，只要其中任意一个分组最终不满足有效性条件（比如Group1要求总Units≥7、Group2要求总Units≥9），这条数据就得从所有它所属的分组里排除；之后再重新计算各分组的有效总和，判断分组是否有效。你的初始SQL只单独判断了每个分组的原始总和，没考虑这种跨分组的依赖排除，所以结果不符合预期。

先回顾下你的测试数据和规则：

CREATE TABLE #test (Item VARCHAR(1), Group1 BIT, Group2 BIT, Units INT)
INSERT INTO #test (Item, Group1, Group2, Units) VALUES 
('A', 1, 0, 2), 
('B', 1, 1, 2), 
('C', 1, 1, 2), 
('D', 1, 1, 2), 
('E', 0, 1, 2)

• Group1有效条件：最终纳入计算的条目总Units ≥7
• Group2有效条件：最终纳入计算的条目总Units ≥9

核心思路拆解 #

这个需求本质是个迭代筛选的过程：

1. 先假设所有条目都是有效的，计算各分组的总和，判断哪些分组不符合条件；
2. 把所有属于无效分组的条目全部排除；
3. 用剩下的条目重新计算分组总和，再次判断有效性；
4. 重复步骤2-3，直到没有新的条目被排除，分组状态稳定为止；
5. 最后用最终的有效条目集合计算分组结果。

具体实现（CTE迭代版） #

用CTE递归可以很清晰地实现这个筛选过程：

WITH ValidItems AS (
    -- 第一步：初始把所有条目都标记为有效
    SELECT Item, Group1, Group2, Units
    FROM #test
    UNION ALL
    -- 第二步：迭代排除那些所属分组当前不满足条件的条目
    SELECT t.Item, t.Group1, t.Group2, t.Units
    FROM #test t
    JOIN ValidItems vi ON t.Item = vi.Item
    WHERE 
        -- 如果条目属于Group1，且当前Group1的有效总和不达标，就排除它
        (t.Group1 = 1 AND (SELECT SUM(Units) FROM ValidItems WHERE Group1 = 1) < 7) = 0
        AND
        -- 如果条目属于Group2，且当前Group2的有效总和不达标，就排除它
        (t.Group2 = 1 AND (SELECT SUM(Units) FROM ValidItems WHERE Group2 = 1) < 9) = 0
)
-- 去重得到最终的有效条目集合（递归过程可能产生重复）
SELECT DISTINCT Item, Group1, Group2, Units
INTO #ValidItems
FROM ValidItems
OPTION (MAXRECURSION 10); -- 小数据量下10次递归足够

-- 计算最终的分组结果
SELECT 
    'Group1' AS GroupName,
    SUM(Units) AS TotalUnits,
    CASE WHEN SUM(Units) >=7 THEN '有效' ELSE '失效' END AS Status
FROM #ValidItems WHERE Group1 = 1
UNION ALL
SELECT 
    'Group2' AS GroupName,
    SUM(Units) AS TotalUnits,
    CASE WHEN SUM(Units) >=9 THEN '有效' ELSE '失效' END AS Status
FROM #ValidItems WHERE Group2 = 1;

-- 清理临时表
DROP TABLE #ValidItems;
DROP TABLE #test;

执行过程解释 #

1. 第一次递归：所有5条条目都有效，计算得Group1总和8≥7（有效），Group2总和8<9（无效）→ 所有属于Group2的条目（B、C、D、E）被排除；
2. 第二次递归：只剩条目A有效，计算得Group1总和2<7（无效）→ 条目A也被排除；
3. 第三次递归：没有有效条目，分组总和都是0，状态稳定，循环结束；
4. 最终结果：两个分组的TotalUnits都是0，Status都是失效，完全符合你的预期。

备选实现（循环判断版） #

如果你觉得递归CTE不好理解，也可以用循环来实现相同逻辑：

DECLARE @Group1Valid BIT, @Group2Valid BIT;

-- 先假设所有条目有效，计算初始分组有效性
SELECT 
    @Group1Valid = CASE WHEN SUM(CASE WHEN Group1=1 THEN Units ELSE 0 END) >=7 THEN 1 ELSE 0 END,
    @Group2Valid = CASE WHEN SUM(CASE WHEN Group2=1 THEN Units ELSE 0 END) >=9 THEN 1 ELSE 0 END
FROM #test;

-- 循环迭代，直到分组有效性不再变化
WHILE 1=1
BEGIN
    DECLARE @NewGroup1Valid BIT, @NewGroup2Valid BIT;
    -- 用当前有效分组的规则筛选条目，重新计算分组有效性
    SELECT 
        @NewGroup1Valid = CASE WHEN SUM(CASE WHEN Group1=1 THEN Units ELSE 0 END) >=7 THEN 1 ELSE 0 END,
        @NewGroup2Valid = CASE WHEN SUM(CASE WHEN Group2=1 THEN Units ELSE 0 END) >=9 THEN 1 ELSE 0 END
    FROM #test
    WHERE 
        (Group1=1 AND @Group1Valid=1) OR (Group1=0)
        AND
        (Group2=1 AND @Group2Valid=1) OR (Group2=0);

    -- 如果分组状态没有变化，退出循环
    IF @NewGroup1Valid = @Group1Valid AND @NewGroup2Valid = @Group2Valid
        BREAK;

    -- 更新分组状态，继续循环
    SET @Group1Valid = @NewGroup1Valid;
    SET @Group2Valid = @NewGroup2Valid;
END;

-- 输出最终结果
SELECT 
    'Group1' AS GroupName,
    SUM(CASE WHEN Group1=1 THEN Units ELSE 0 END) AS TotalUnits,
    CASE WHEN @Group1Valid=1 THEN '有效' ELSE '失效' END AS Status
FROM #test
WHERE 
    (Group1=1 AND @Group1Valid=1) OR (Group1=0)
    AND
    (Group2=1 AND @Group2Valid=1) OR (Group2=0)
UNION ALL
SELECT 
    'Group2' AS GroupName,
    SUM(CASE WHEN Group2=1 THEN Units ELSE 0 END) AS TotalUnits,
    CASE WHEN @Group2Valid=1 THEN '有效' ELSE '失效' END AS Status
FROM #test
WHERE 
    (Group1=1 AND @Group1Valid=1) OR (Group1=0)
    AND
    (Group2=1 AND @Group2Valid=1) OR (Group2=0);

DROP TABLE #test;

这个循环的逻辑和CTE完全一致，都是逐步排除不符合条件的条目，直到分组状态稳定。

内容的提问来源于stack exchange，提问作者fullstrum