PostgreSQL中将多记录合并为单记录并新增列填充对应数据

需求说明 #

将每个app_id对应的多条记录合并为单条，把rank为2、3的app_status和balance值分别填入新增的2ndrank_app_status、2ndrank_balance、3rdrank_app_status、3rdrank_balance列。

基础表 #

app_id    app_status  balance      rank
 xyz          1          500         1
 xyz          1          200         2
 xyz          1          200         3
 abc          2          300         1
 abc          9          200         2
 klm          1          800         1

期望输出 #

app_id    app_status  balance    rank  2ndrank_app_status 2ndrank_balance 3rdrank_app_status   3rdrank_balance
 xyz          1         500        1              1              200           1                200  
 abc          2         300        1              9              200           null               null     
 klm          1         800        1          null              null           null               null 

解决方案（通用SQL写法） #

用条件聚合实现，兼容绝大多数SQL数据库（MySQL、PostgreSQL、SQL Server等）：

SELECT
    app_id,
    MAX(CASE WHEN rank = 1 THEN app_status END) AS app_status,
    MAX(CASE WHEN rank = 1 THEN balance END) AS balance,
    MAX(CASE WHEN rank = 1 THEN rank END) AS rank,
    MAX(CASE WHEN rank = 2 THEN app_status END) AS 2ndrank_app_status,
    MAX(CASE WHEN rank = 2 THEN balance END) AS 2ndrank_balance,
    MAX(CASE WHEN rank = 3 THEN app_status END) AS 3rdrank_app_status,
    MAX(CASE WHEN rank = 3 THEN balance END) AS 3rdrank_balance
FROM
    your_table_name  -- 替换成你的实际表名
GROUP BY
    app_id
ORDER BY
    app_id;

逻辑说明 #

1. 通过GROUP BY app_id将同一app_id的记录分组
2. 用CASE WHEN筛选对应rank的字段值，配合MAX聚合函数提取唯一值（每个app_id下的rank唯一，用MIN/SUM也能达到同样效果）
3. 无对应rank的记录会返回NULL，与期望输出完全匹配

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