如何在SQL中返回列值为1的所有列名组合列表？

核心逻辑 #

先将每行数据转换为「ID-有效列名」的记录（仅保留列值为1的列），再为每个ID的有效列名集合生成所有非空子集（包括单个列、多列组合）。

PostgreSQL 实现 #

利用数组和递归CTE实现，自动避免重复组合：

-- 第一步：提取每个ID的有效列名（替换your_table和列名列表为实际值）
WITH valid_cols AS (
    SELECT 
        id,
        unnest(array['A', 'B', 'C']) AS col_name,
        unnest(array[A, B, C]) AS col_value
    FROM your_table
    WHERE unnest(array[A, B, C]) = 1
),
-- 第二步：递归生成所有非空组合
combinations AS (
    SELECT 
        id,
        array[col_name] AS col_array,
        col_name AS combination,
        1 AS level
    FROM valid_cols
    UNION ALL
    SELECT 
        c.id,
        c.col_array || vc.col_name,
        c.combination || ',' || vc.col_name,
        c.level + 1
    FROM combinations c
    JOIN valid_cols vc 
        ON c.id = vc.id 
        AND vc.col_name > c.col_array[array_upper(c.col_array, 1)]
)
SELECT id, combination
FROM combinations
ORDER BY id, level, combination;

MySQL 8.0+ 实现 #

通过UNION ALL提取有效列，再用递归CTE生成组合：

-- 第一步：提取每个ID的有效列名（替换your_table和列名列表为实际值）
WITH valid_cols AS (
    SELECT id, 'A' AS col_name FROM your_table WHERE A = 1
    UNION ALL
    SELECT id, 'B' AS col_name FROM your_table WHERE B = 1
    UNION ALL
    SELECT id, 'C' AS col_name FROM your_table WHERE C = 1
),
-- 第二步：递归生成所有非空组合
combinations AS (
    SELECT 
        id,
        col_name AS combination,
        col_name AS last_col,
        1 AS level
    FROM valid_cols
    UNION ALL
    SELECT 
        c.id,
        CONCAT(c.combination, ',', vc.col_name),
        vc.col_name,
        c.level + 1
    FROM combinations c
    JOIN valid_cols vc 
        ON c.id = vc.id 
        AND vc.col_name > c.last_col
)
SELECT id, combination
FROM combinations
ORDER BY id, level, combination;

大量列/数据的优化建议 #

1. 动态生成列名列表：如果有100多列，手动写列名太繁琐，可通过查询information_schema.columns生成行转列的SQL代码（比如PostgreSQL里生成数组元素，MySQL里生成UNION ALL语句）。
2. 提前过滤无效行：先删除或过滤掉所有列值全为0的行，减少后续处理数据量。
3. 索引优化：为ID列建立索引，加快递归连接时的查询效率。

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