多表关联查询订单对话线程总评论数结果不符求助

问题根因 #

你当前查询结果不符合预期有两个核心问题：

1. 没有先过滤出对订单实际提交过响应的供应商，返回了无关的供应商-买家-订单组合
2. 统计评论数时没有限定评论的收发双方和当前行的供应商、买家匹配，导致评论数直接按订单维度全局统计，出现了所有同订单线程评论数一致的错误。

修改方案 #

你可以先从订单响应表取到所有有效对话线程（即提交过响应的供应商和对应订单、买家的唯一组合），再左关联评论表统计匹配双向对话的评论数即可，参考SQL如下：

SELECT 
  order_responses.order_id,
  order_responses.supplier_id,
  order_responses.buyer_id,
  COUNT(comments.id) AS comment_count
FROM order_responses
LEFT JOIN comments 
  ON comments.order_id = order_responses.order_id
  AND (
    -- 覆盖供应商发给买家、买家发给供应商两种情况
    (comments.sender_id = order_responses.supplier_id AND comments.receiver_id = order_responses.buyer_id)
    OR 
    (comments.sender_id = order_responses.buyer_id AND comments.receiver_id = order_responses.supplier_id)
  )
GROUP BY order_responses.order_id, order_responses.supplier_id, order_responses.buyer_id
ORDER BY order_responses.order_id, order_responses.supplier_id;

逻辑说明 #

• 用order_responses作为主表，保证返回的所有线程都是真实提交过响应的供应商配对，不会出现多余的无效组合
• 评论的匹配条件写在LEFT JOIN的关联逻辑里，而非WHERE子句中，没有匹配评论时对应字段返回NULL，COUNT(comments.id)会自动将这类行的统计结果设为0
• 分组字段直接取线程的三个唯一标识，不会出现统计错位的问题，最终结果和你给出的预期完全一致。

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