如何在SQL的LEFT JOIN中正确统计discussions_replies数据？

AI 小北

2026-6-20

解决LEFT JOIN中关联数据的正确统计问题

问题根源

你的查询存在两个核心问题：

统计字段错误：用count(dm.id)统计回复数完全错误——dm是通过INNER JOIN关联的，dm.id永远非空，哪怕对应消息没有回复，这个计数也会返回1，而非0。应该统计discussions_replies表的字段，比如count(dr.id)（LEFT JOIN后无回复时dr.id为NULL，count会自动忽略）。
逻辑顺序错误：你先做了关联和分组，再用窗口函数取每个房间的最新消息，这会导致分组合并了同一discussion_message_id的多条房间消息，窗口函数无法正确识别每个房间的最新记录。

正确解决方案

先筛选出每个房间的最新rooms_messages记录，再关联消息内容和预先统计好的回复数，逻辑更清晰且结果准确：

WITH latest_room_messages AS (
    -- 先获取每个room_id的最新消息记录
    SELECT 
        rm.id, 
        rm.created_at, 
        rm.room_id, 
        rm.discussion_message_id,
        ROW_NUMBER() OVER (PARTITION BY rm.room_id ORDER BY rm.created_at DESC) AS rn
    FROM rooms_messages rm
),
reply_counts AS (
    -- 预先统计每条discussions_message的回复数
    SELECT 
        discussion_message_id, 
        COUNT(id) AS replies
    FROM discussions_replies
    GROUP BY discussion_message_id
)
SELECT 
    lrm.id, 
    lrm.created_at, 
    lrm.room_id, 
    lrm.discussion_message_id, 
    dm.text, 
    -- 无回复时显示0，避免NULL
    COALESCE(rc.replies, 0) AS replies
FROM latest_room_messages lrm
INNER JOIN discussions_messages dm ON lrm.discussion_message_id = dm.id
LEFT JOIN reply_counts rc ON dm.id = rc.discussion_message_id
WHERE lrm.rn = 1;