动态多值数据表生成全值组合的SQL查询需求

针对你的需求——同一ID下各CODE对应VALUE的所有可能组合（笛卡尔积），且CODE数量不固定无法硬编码，以下是基于递归CTE的通用解决方案：

测试数据 #

先创建并插入测试数据模拟你的场景：

CREATE TABLE test_data (
    ID INT,
    CODE VARCHAR(20),
    VALUE VARCHAR(50)
);

INSERT INTO test_data VALUES
(1, 'CODE_A', 'V1'),
(1, 'CODE_A', 'V2'),
(1, 'CODE_B', 'X1'),
(1, 'CODE_B', 'X2'),
(1, 'CODE_C', 'Y1'),
(2, 'CODE_A', 'A'),
(2, 'CODE_B', 'B');

解决方案SQL #

WITH code_groups AS (
    -- 按ID分组，给每个ID下的唯一CODE分配序号，用于递归遍历
    SELECT 
        ID,
        CODE,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY CODE) AS code_seq
    FROM test_data
    GROUP BY ID, CODE
),
recursive_combinations AS (
    -- 递归起始：取每个ID下第一个CODE的所有VALUE作为初始组合
    SELECT 
        c.ID,
        c.code_seq,
        CAST(t.VALUE AS VARCHAR(MAX)) AS full_combination
    FROM code_groups c
    JOIN test_data t ON c.ID = t.ID AND c.CODE = t.CODE
    WHERE c.code_seq = 1
    
    UNION ALL
    
    -- 递归拼接：将已有组合与下一个CODE的所有VALUE进行笛卡尔积拼接
    SELECT 
        rc.ID,
        c.code_seq,
        CONCAT(rc.full_combination, ', ', t.VALUE) AS full_combination
    FROM recursive_combinations rc
    JOIN code_groups c ON rc.ID = c.ID AND c.code_seq = rc.code_seq + 1
    JOIN test_data t ON c.ID = t.ID AND c.CODE = t.CODE
),
final_output AS (
    -- 仅保留每个ID下处理完所有CODE的完整组合
    SELECT 
        ID,
        full_combination
    FROM recursive_combinations rc
    WHERE code_seq = (SELECT MAX(code_seq) FROM code_groups WHERE ID = rc.ID)
)
SELECT * FROM final_output ORDER BY ID;

逻辑说明 #

1. code_groups：先对每个ID下的CODE去重并按顺序编号，确定每个ID下的CODE数量和遍历顺序，为递归提供基础。
2. recursive_combinations：
   • 初始阶段：取每个ID下第一个CODE的所有VALUE作为初始组合。
   • 递归阶段：将当前已生成的组合，与下一个序号的CODE的每个VALUE拼接，生成新的组合，直到遍历完该ID下所有CODE。
3. final_output：筛选出每个ID下处理完最后一个CODE的结果，这些就是该ID下所有CODE的VALUE的全组合。

扩展优化 #

如果需要更便于后续数据处理的格式（如结构化数组），可以将拼接逻辑改为JSON数组：

-- 修改递归部分的组合生成逻辑
WITH code_groups AS (
    SELECT 
        ID,
        CODE,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY CODE) AS code_seq
    FROM test_data
    GROUP BY ID, CODE
),
recursive_combinations AS (
    SELECT 
        c.ID,
        c.code_seq,
        CAST(JSON_ARRAY(t.VALUE) AS VARCHAR(MAX)) AS combination_json
    FROM code_groups c
    JOIN test_data t ON c.ID = t.ID AND c.CODE = t.CODE
    WHERE c.code_seq = 1
    
    UNION ALL
    
    SELECT 
        rc.ID,
        c.code_seq,
        JSON_ARRAY_APPEND(rc.combination_json, '$', t.VALUE) AS combination_json
    FROM recursive_combinations rc
    JOIN code_groups c ON rc.ID = c.ID AND c.code_seq = rc.code_seq + 1
    JOIN test_data t ON c.ID = t.ID AND c.CODE = t.CODE
),
final_output AS (
    SELECT 
        ID,
        combination_json
    FROM recursive_combinations rc
    WHERE code_seq = (SELECT MAX(code_seq) FROM code_groups WHERE ID = rc.ID)
)
SELECT * FROM final_output ORDER BY ID;

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