PostgreSQL行转列及多表关联查询问题求助

表结构说明 #

Table1（行存储键值对表） #

ticket_id  Key        Value      Created_at
-------------------------------------------
   1        123        Free       2023-06-01
   1        type       task       2023-06-01
   1        status     open       2023-06-01
   1        channel    email      2023-06-01
   1        456        fruit      2023-06-01
   2        123        paid       2023-06-01
   2        type       incident   2023-06-01
   2        status     closed     2023-06-01
   2        channel    voice      2023-06-01
   2        456        vegetable  2023-06-01

注：原表中tickedt_ID应为笔误，统一修正为ticket_id

Table2（关联表） #

ID  Created_at  type        subject
-----------------------------------
1   2023-06-01  task        XXX
2   2023-06-01  incident    abc
3   2023-06-01  task        def

原查询的问题 #

你提交的SQL存在几个关键问题：

• SELECT列表包含未聚合的t.value，会导致每个不同Value生成一行，直接破坏分组效果
• GROUP BY中使用的t.id是错误字段，Table1对应字段为ticket_id
• 未对status、type等需要的字段做行转列聚合
• 分组前执行关联操作，因Table1多行记录导致Table2单条记录被重复关联，最终出现ID重复

正确的SQL查询 #

SELECT
    t.ticket_id AS ID,
    MIN(CASE WHEN t.`Key` = '123' THEN t.Value END) AS plan,
    MIN(CASE WHEN t.`Key` = 'status' THEN t.Value END) AS status,
    MIN(CASE WHEN t.`Key` = '456' THEN t.Value END) AS category,
    -- 优先取Table1的type值，为空则用Table2的type
    COALESCE(MIN(CASE WHEN t.`Key` = 'type' THEN t.Value END), te.type) AS type,
    t.created_at AS `created at`,
    te.subject -- 不需要该字段可直接删除
FROM Table1 t
JOIN Table2 te ON t.ticket_id = te.ID
WHERE t.created_at >= '2023-06-01' AND t.created_at <= '2023-06-21'
GROUP BY t.ticket_id, t.created_at, te.ID, te.type, te.subject
ORDER BY t.ticket_id;

说明 #

1. 用MIN()配合CASE WHEN完成行转列，确保每个ticket_id只保留唯一字段值
2. 分组时包含Table2中与ticket_id一一对应的字段，避免关联导致的重复行
3. COALESCE()兼容Table1和Table2的type字段，保证取值优先级
4. 修正日期条件边界，确保包含目标日期的记录

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