如何在PostgreSQL中通过多表关联实现数据扁平化（行转列）

现有数据表结构 #

T1表 #

+----+--------+--------+
| id | f1     | f2     |
+----+--------+--------+
| 1  | a      | c      |
| 2  | b      | d      |
+----+--------+--------+

T2表 #

+----+----+--------+--------+----+
| id | T1 | f1     | f2     | T3 |
+----+----+--------+--------+----+
| 1  | 1  | aa     | 100    | 1  |
| 2  | 1  | bb     | 200    | 1  |
| 3  | 2  | aa     | 56     | 2  |
| 4  | 2  | bb     | 550    | 2  |
| 5  | 2  | cc     | -120   | 3  |
+----+----+--------+--------+----+

T3表 #

+----+--------+--------+
| id | f4     | f5     |
+----+--------+--------+
| 1  | aaa    | x      |
| 2  | bbb    | y      |
| 3  | ccc    | z      |
+----+--------+--------+

目标查询结果 #

+-------+----+----+-----+-----+-------------+------+-----+-----+-----+
| T1_id | f1 | f2 | aa  | bb  | sum(aa, bb) | cc   | aaa | bbb | ccc |
+-------+----+----+-----+-----+-------------+------+-----+-----+-----+
| 1     | a  | c  | 100 | 200 | 300         |      | x   |     |     |
| 2     | b  | d  | 56  | 550 | 610         | -120 |     | y   | z   |
+-------+----+----+-----+-----+-------------+------+-----+-----+-----+

实现方案 #

PostgreSQL没有原生的PIVOT语法，我们可以通过条件聚合（CASE WHEN结合聚合函数）实现行转列，同时关联三张表完成数据整合。

完整查询语句 #

SELECT
    t1.id AS T1_id,
    t1.f1,
    t1.f2,
    -- 处理T2中f1为aa、bb、cc的行转列
    MAX(CASE WHEN t2.f1 = 'aa' THEN t2.f2 END) AS aa,
    MAX(CASE WHEN t2.f1 = 'bb' THEN t2.f2 END) AS bb,
    -- 计算aa和bb的和，用COALESCE避免NULL值影响
    COALESCE(MAX(CASE WHEN t2.f1 = 'aa' THEN t2.f2 END), 0) + COALESCE(MAX(CASE WHEN t2.f1 = 'bb' THEN t2.f2 END), 0) AS "sum(aa, bb)",
    MAX(CASE WHEN t2.f1 = 'cc' THEN t2.f2 END) AS cc,
    -- 处理T3中f4为aaa、bbb、ccc的行转列
    MAX(CASE WHEN t3.f4 = 'aaa' THEN t3.f5 END) AS aaa,
    MAX(CASE WHEN t3.f4 = 'bbb' THEN t3.f5 END) AS bbb,
    MAX(CASE WHEN t3.f4 = 'ccc' THEN t3.f5 END) AS ccc
FROM
    T1 t1
LEFT JOIN
    T2 t2 ON t1.id = t2.T1
LEFT JOIN
    T3 t3 ON t2.T3 = t3.id
GROUP BY
    t1.id, t1.f1, t1.f2
ORDER BY
    t1.id;

关键逻辑说明 #

1. 行转列核心：使用MAX(CASE WHEN ... THEN ... END)，针对每个需要转成列的字段值（如aa、bb），筛选出对应行的数值并聚合。由于每个T1_id下同一f1值唯一，用MAX或MIN都能得到正确值。
2. 求和处理：用COALESCE将可能的NULL值转为0，避免NULL + 数值得到NULL的问题。
3. 表关联：通过T1.id = T2.T1关联主表与T2，再通过T2.T3 = T3.id关联T3获取对应字段值。
4. 分组依据：按T1的主键和字段分组，确保每个T1记录对应一行结果。

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