PostgreSQL查询如何调整输出布局将多行结果转为单行展示

原查询出现同一条评审记录拆分为多行的问题，根源是peerreviewanalyticsview（评审主表）和peerreviewscore（评分表）为一对多关系，直接左连接时每匹配到一条评分记录就会生成一行结果。要实现单条评审记录单行展示、所有评分信息横向排列的效果，可通过窗口函数编号+条件聚合的方式实现。

修改后可直接运行的SQL语句 #

WITH ranked_score AS (
    SELECT
        peerreviewid,
        score,
        initiator,
        addedon,
        -- 为同一评审下的评分按添加时间升序编号，可按需调整排序规则
        ROW_NUMBER() OVER (PARTITION BY peerreviewid ORDER BY addedon) AS rn
    FROM workflowquality.peerreviewscore
)
SELECT
    prav.id,
    prav.accessionnumber,
    prav.status,
    prav.reviewedon AS "Review Date",
    prav.reviewedby,
    prav.type,
    -- 提取第1条评分信息
    MAX(CASE WHEN rs.rn = 1 THEN rs.score END) AS "Score 1",
    MAX(CASE WHEN rs.rn = 1 THEN rs.initiator END) AS "Initiator 1",
    MAX(CASE WHEN rs.rn = 1 THEN rs.addedon END) AS "Score Date 1",
    -- 提取第2条评分信息
    MAX(CASE WHEN rs.rn = 2 THEN rs.score END) AS "Score 2",
    MAX(CASE WHEN rs.rn = 2 THEN rs.initiator END) AS "Initiator 2",
    MAX(CASE WHEN rs.rn = 2 THEN rs.addedon END) AS "Score Date 2",
    -- 提取第3条评分信息，若单条评审关联评分更多，可按此格式继续追加列
    MAX(CASE WHEN rs.rn = 3 THEN rs.score END) AS "Score 3",
    MAX(CASE WHEN rs.rn = 3 THEN rs.initiator END) AS "Initiator 3",
    MAX(CASE WHEN rs.rn = 3 THEN rs.addedon END) AS "Score Date 3"
FROM workflowquality.peerreviewanalyticsview prav
LEFT JOIN ranked_score rs ON rs.peerreviewid = prav.id
WHERE prav.reviewedon > '2022-02-01 00:00:00.000-07'
-- 按主表字段分组，将同一评审的多条评分聚合到同一行
GROUP BY
    prav.id,
    prav.accessionnumber,
    prav.status,
    prav.reviewedon,
    prav.reviewedby,
    prav.type;

调整说明 #

• 写法逻辑：先用CTE对评分表的数据按所属评审ID分区，给每个评审下的评分记录编排序号，再关联主表后通过条件判断把不同序号的评分、评分人、评分日期映射为独立列，最后按主表主键及属性字段分组聚合，即可实现单条评审记录单行输出。
• 扩展方式：如果业务中单条评审对应的评分记录超过3条，参照现有CASE WHEN的格式，继续追加对应序号的字段即可。
• 排序调整：如果需要按其他规则（比如评分值、评分人ID）排列评分列的顺序，只需要修改窗口函数ROW_NUMBER()中ORDER BY后的字段即可。

效果对比 #

• 原多行拆分输出效果：
  
• 调整后单行单记录输出效果：
  

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