如何在PostgreSQL中将创建者姓名设为列头展示汇总数据？

嘿，你要实现的是典型的行转列（透视表）效果，用PostgreSQL的话，用条件聚合就能轻松搞定，刚好匹配你想要的输出格式。下面针对你的示例数据给出具体实现：

核心思路 #

1. 先整合roads和Blocks表的数据，分别计算每个创作者在两类数据中的总长度（用LEFT JOIN确保所有创作者都被包含，COALESCE把无数据的情况转为0）
2. 用CASE语句做条件聚合，把创作者名称（jack/doge/cardano）转换成单独的列
3. 最后计算每行的total总和

完整SQL代码 #

WITH combined_data AS (
    -- 计算roads表中每个创作者的总长度
    SELECT 
        'roads' AS type,
        c.name,
        COALESCE(SUM(r.length), 0) AS total_length
    FROM Creator c
    LEFT JOIN roads r ON c.id = r.creator_id
    GROUP BY c.name, 'roads'
    
    UNION ALL
    
    -- 计算Blocks表中每个创作者的总长度
    SELECT 
        'blocks' AS type,
        c.name,
        COALESCE(SUM(b.length), 0) AS total_length
    FROM Creator c
    LEFT JOIN Blocks b ON c.id = b.creator_id
    GROUP BY c.name, 'blocks'
)
-- 行转列并计算total
SELECT
    type,
    SUM(CASE WHEN name = 'jack' THEN total_length ELSE 0 END) AS jack,
    SUM(CASE WHEN name = 'doge' THEN total_length ELSE 0 END) AS doge,
    SUM(CASE WHEN name = 'cardano' THEN total_length ELSE 0 END) AS cardano,
    SUM(total_length) AS total
FROM combined_data
GROUP BY type
ORDER BY type;

代码解释 #

• COALESCE(SUM(...), 0)：确保没有对应数据的创作者（比如doge没有roads和blocks记录）显示0而不是NULL
• CASE语句：针对每个创作者名称，只累加对应的数据，实现行转列的效果
• SUM(total_length)：自动计算每行所有创作者的长度总和，得到total列

动态场景扩展（可选） #

如果你的创作者列表是动态变化的（比如会新增创作者），可以用动态SQL自动生成列，或者使用PostgreSQL的crosstab函数（需要先安装tablefunc扩展）。不过针对你当前的固定创作者列表，上面的条件聚合写法最直观、易维护。

运行上述代码后，你就能得到完全符合预期的输出：

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