使用递归CTE生成子组所有组合的实现报错求助

问题描述 #

需要生成分组记录的所有组合（包含排除整个组的情况），示例场景如下：

1. 表结构：

CREATE TABLE tbl_myTable(
   [id] INT NOT NULL,
   [group] VARCHAR(10) NOT NULL,
   [value] INT NOT NULL
);

2. 测试数据：

3. 预期未聚合输出：5行（对应A组5、B组2、B组6、A组5+B组2、A组5+B组6）
4. 遇到的错误：递归CTE触发SQL Server限制，提示GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression

错误原因 #

SQL Server对递归CTE的递归分支（即UNION ALL后跟随的部分）有硬限制：禁止使用GROUP BY、HAVING或聚合函数，之前的写法违反了此规则。

解决方案 #

将组合构建逻辑完全放在递归分支中，仅处理行的组合/跳过逻辑，不进行聚合操作；通过给分组分配序号，按顺序递归处理每个组的选择/跳过，最终过滤得到目标结果。

完整实现代码 #

-- 创建表并插入测试数据
CREATE TABLE tbl_myTable(
   [id] INT NOT NULL,
   [group] VARCHAR(10) NOT NULL,
   [value] INT NOT NULL
);

INSERT INTO tbl_myTable VALUES
(1, 'A', 5),
(2, 'B', 2),
(3, 'B', 6);

-- 递归CTE生成所有组合
WITH GroupedItems AS (
    -- 为每个分组分配唯一序号，确保递归按组顺序处理
    SELECT 
        [id], [group], [value],
        DENSE_RANK() OVER (ORDER BY [group]) AS GroupSeq
    FROM tbl_myTable
),
RecursiveCombos AS (
    -- 锚点：空组合（作为递归起点）
    SELECT 
        CAST(NULL AS INT) AS id,
        CAST(NULL AS VARCHAR(10)) AS [group],
        CAST(NULL AS INT) AS [value],
        0 AS CurrentGroupSeq,
        CAST('' AS VARCHAR(MAX)) AS ComboKey -- 标记唯一组合，避免重复
    UNION ALL
    -- 分支1：选择下一组的某条记录，加入现有组合
    SELECT 
        gi.id,
        gi.[group],
        gi.[value],
        gi.GroupSeq AS CurrentGroupSeq,
        rc.ComboKey + '|' + CAST(gi.id AS VARCHAR(10)) AS ComboKey
    FROM RecursiveCombos rc
    JOIN GroupedItems gi ON gi.GroupSeq = rc.CurrentGroupSeq + 1
    UNION ALL
    -- 分支2：跳过下一组，不添加任何记录
    SELECT 
        rc.id,
        rc.[group],
        rc.[value],
        rc.CurrentGroupSeq + 1 AS CurrentGroupSeq,
        rc.ComboKey AS ComboKey
    FROM RecursiveCombos rc
    WHERE rc.CurrentGroupSeq < (SELECT MAX(GroupSeq) FROM GroupedItems)
)
-- 过滤空组合，得到预期的5行结果
SELECT DISTINCT
    id, [group], value, ComboKey
FROM RecursiveCombos
WHERE id IS NOT NULL
ORDER BY ComboKey;

代码说明 #

1. GroupedItems：通过DENSE_RANK给每个分组分配唯一序号，确保递归时按组顺序处理，避免生成重复组合。
2. RecursiveCombos锚点：定义空组合作为递归的初始起点。
3. 递归分支：
   • 分支1：将现有组合与下一组的每条记录进行拼接，生成包含该组记录的新组合。
   • 分支2：跳过当前组，保留原有组合，生成不包含该组任何记录的新组合。
4. 最终查询：过滤掉空组合，得到符合预期的5行结果，且该方案支持任意数量的分组和每组任意数量的记录。

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