SQL如何根据id1去重数量动态生成组合查询，避免硬编码连接与列数

解决方案 #

要实现根据id1去重数量动态生成多列组合结果，最通用的方案是使用动态SQL拼接查询语句，核心思路如下：

• 第一步：先统计id1的去重数量，记为n
• 第二步：根据n的值，动态拼接CTE、SELECT返回列、FROM关联表、WHERE过滤条件的代码段
• 第三步：执行拼接完成的完整SQL语句

MySQL 实现示例 #

-- 1. 统计id1去重数量，存入变量
SELECT COUNT(DISTINCT id1) INTO @n FROM test;

-- 2. 拼接SELECT部分的返回列
SET @select_cols = GROUP_CONCAT('c', rn, '.id2 AS id2_', rn SEPARATOR ', ')
FROM (
    SELECT DISTINCT DENSE_RANK() OVER (ORDER BY id1) AS rn FROM test
) t;

-- 3. 拼接FROM部分的关联表
SET @from_tables = GROUP_CONCAT('combinationTable c', rn SEPARATOR ', ')
FROM (
    SELECT DISTINCT DENSE_RANK() OVER (ORDER BY id1) AS rn FROM test
) t;

-- 4. 拼接WHERE部分的rn大小判断条件
WITH RECURSIVE rn_list AS (
    SELECT 1 AS rn
    UNION ALL
    SELECT rn + 1 FROM rn_list WHERE rn < @n
)
SELECT GROUP_CONCAT('c', rn, '.rn < c', rn + 1, '.rn' SEPARATOR ' AND ') INTO @where_cond
FROM rn_list WHERE rn < @n;

-- 5. 拼接完整SQL语句
SET @sql = CONCAT('
WITH combinationTable AS (
    SELECT DENSE_RANK() OVER (ORDER BY id1) AS rn, id2, id1 FROM test
)
SELECT ', @select_cols, '
FROM ', @from_tables, '
WHERE ', @where_cond, '
ORDER BY ', REPLACE(@where_cond, ' AND ', ', '), ';
');

-- 6. 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

方案说明 #

• 当id1去重数量为3时，自动生成你原有的3表关联、3列返回的查询逻辑
• 当id1去重数量为4/5时，自动对应生成4/5个关联表、4/5列返回的查询，不需要修改硬编码逻辑
• 如果使用PostgreSQL、SQL Server等其他数据库，只需要对应修改动态SQL的变量声明、执行语法即可，核心拼接逻辑完全一致

注意事项 #

• 该方案默认兼容id1去重数量≥2的场景，如果需要兼容去重数量为1的场景，可以额外加判断逻辑直接返回单列表即可
• 如果id1去重数量过大（超过10），会导致关联后的结果集指数级膨胀，建议根据业务场景增加数量限制逻辑

内容的提问来源于stack exchange，提问作者Harshitha S V