MYSQL如何通过LEFT JOIN合并数据查询与COUNT统计查询？

错误原因分析 #

• 引用了不存在的表别名：你左连接关联的表别名是c，但聚合函数里写了COUNT(b.id)，没有别名为b的表，因此触发字段不存在报错。
• 使用了不存在的字段：GROUP BY子句中的A.Date字段不存在，你的message表中时间字段为created。
• 核心逻辑错误：对单条消息id分组会导致查询结果仅返回去重后的id对应的记录，无法拿到全部符合条件的消息列表，不符合你查询全量消息的需求。

实现方案 #

方案1：使用LEFT JOIN实现需求 #

你可以先把统计未读数量的逻辑封装为子查询，再和符合条件的消息数据做左连接，就能同时拿到全量符合条件的消息数据和总未读数量，SQL如下：

SELECT a.*, b.unread_count
FROM message a
LEFT JOIN (
    SELECT COUNT(id) AS unread_count
    FROM message 
    WHERE (sender_id = 1 OR receiver_id = 1) 
      AND (id = 18 OR parent_id = 18) 
      AND readed = 0
) b ON 1=1
WHERE (a.sender_id = 1 OR a.receiver_id = 1) 
  AND (a.id = 18 OR a.parent_id = 18)
ORDER BY a.id;

这里ON 1=1是因为统计结果是全局总未读数，需要关联到每一行查询到的消息记录上。

方案2：使用窗口函数（更简洁，性能更优） #

如果你的MySQL版本支持8.0及以上，可以直接用窗口函数实现，不需要做JOIN关联，逻辑更简单：

SELECT *, 
       SUM(CASE WHEN readed = 0 THEN 1 ELSE 0 END) OVER() AS unread_count
FROM message
WHERE (sender_id = 1 OR receiver_id = 1) 
  AND (id = 18 OR parent_id = 18)
ORDER BY id;

两种方案都可以实现同时查询符合条件的消息列表、以及符合条件的消息中未读总数量的需求。

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