如何生成表格单列所有值组合并计算组合的非空列数与总和

你要的结果本质是原始单列值的所有非空子集，每个原始值对应固定输出列，子集包含该值则对应列填充数值，否则留空。普通Cross Join仅返回笛卡尔积排列结果，未做位置匹配和子集筛选，因此达不到预期效果，可通过二进制掩码法实现，具体方案如下：

前置假设 #

你的原始存储数值的表名为num_table，数值列名为val，示例数据为：

val
---
1
2
3
4

实现代码（支持MySQL 8.0+/PostgreSQL等支持CTE的数据库） #

-- 第一步：给原始数值按顺序分配固定位置编号，对应后续的A/B/C/D列
WITH ranked_nums AS (
    SELECT val, ROW_NUMBER() OVER (ORDER BY val) AS pos
    FROM num_table
),
-- 第二步：生成所有非空子集的二进制掩码，4个数值对应掩码范围1~15（2^4 -1）
masks AS (
    SELECT 1 AS mask UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
    SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
)
-- 第三步：按掩码匹配数值，行转列输出并计算统计列
SELECT
    MAX(CASE WHEN pos = 1 AND mask & (1 << (pos-1)) THEN val END) AS A,
    MAX(CASE WHEN pos = 2 AND mask & (1 << (pos-1)) THEN val END) AS B,
    MAX(CASE WHEN pos = 3 AND mask & (1 << (pos-1)) THEN val END) AS C,
    MAX(CASE WHEN pos = 4 AND mask & (1 << (pos-1)) THEN val END) AS D,
    COUNT(CASE WHEN mask & (1 << (pos-1)) THEN val END) AS 非空列数,
    SUM(CASE WHEN mask & (1 << (pos-1)) THEN val ELSE 0 END) AS 行总和
FROM masks
CROSS JOIN ranked_nums
GROUP BY mask
ORDER BY mask;

扩展说明 #

如果你的原始数值数量大于4，不需要手动改写掩码列表，可用递归CTE自动生成掩码，替换上面代码中的masks段即可：

-- 把@total_num替换为你的实际数值总数
WITH RECURSIVE masks AS (
    SELECT 1 AS mask, POWER(2, @total_num) AS max_mask
    UNION ALL
    SELECT mask + 1 FROM masks WHERE mask < max_mask - 1
)

生成的结果和你给出的示例完全匹配，共15行，新增的两列会自动统计每行的非空值数量和总和。

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