SQL如何将Conversation多层嵌套关联数据转为成对Code列输出

前置修正 #

你现有SQL存在几处语法&逻辑问题先调整：

• CONCAT(senders.first_name, ' ', senders.last_name) as 'Sender Name' 后缺失逗号
• 关联senders表的条件里笔误写了sender.id，应改为senders.id
• GROUP BY字段使用conversations.name不合理，会话IDconversations.id才是唯一主键，改为按conversations.id分组

核心实现逻辑 #

要把多行Code数据转为成对的列输出，用窗口函数编号+条件聚合的方案即可：

1. 按关联链补全messages/highlights/codes表的关联
2. 用ROW_NUMBER()给每个会话下的Code按顺序生成编号
3. 通过MAX(CASE WHEN)按编号提取对应Code的名称和分数

完整SQL示例 #

SELECT
    c.id AS 'ID',
    CONCAT(s.first_name, ' ', s.last_name) as 'Sender',
    MAX(CASE WHEN code_rn = 1 THEN code.name END) AS 'Code 1',
    MAX(CASE WHEN code_rn = 1 THEN code.score END) AS 'Code 1 Score',
    MAX(CASE WHEN code_rn = 2 THEN code.name END) AS 'Code 2',
    MAX(CASE WHEN code_rn = 2 THEN code.score END) AS 'Code 2 Score',
    c.created_at AS 'Start Date'
FROM
    conversations c
    LEFT JOIN users AS s ON s.id = c.participant_1
    LEFT JOIN users AS recipient ON recipient.id = c.participant_2
    -- 补全关联链
    LEFT JOIN messages m ON m.conversation_id = c.id
    LEFT JOIN highlights h ON h.message_id = m.id
    LEFT JOIN codes code ON code.highlight_id = h.id
    -- 给每个会话的Code按顺序编号，排序规则可按需求调整（比如按code创建时间/分数倒序等）
    LEFT JOIN (
        SELECT 
            c2.id AS conversation_id,
            code2.id AS code_id,
            ROW_NUMBER() OVER (PARTITION BY c2.id ORDER BY code2.id ASC) AS code_rn
        FROM conversations c2
        LEFT JOIN messages m2 ON m2.conversation_id = c2.id
        LEFT JOIN highlights h2 ON h2.message_id = m2.id
        LEFT JOIN codes code2 ON code2.highlight_id = h2.id
        WHERE code2.id IS NOT NULL
    ) rn ON rn.conversation_id = c.id AND rn.code_id = code.id
GROUP BY
    c.id, s.first_name, s.last_name, c.created_at

补充说明 #

• 如果你需要输出更多对的Code列，只需新增对应CASE WHEN code_rn = N的提取语句即可
• 窗口函数里的ORDER BY规则可根据你需要的Code排序逻辑调整，比如按分数从高到低、按Code创建时间排序等
• 如果单条会话关联的Code不足2个，对应位置会自动返回NULL，符合LEFT JOIN的逻辑

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