MySQL JOIN查询中结合GROUP_CONCAT实现反应计数统计的方法

基础实现（带计数的拼接结果） #

你需要先对chat_reactions按消息ID和反应类型分组统计次数，再与chats表关联，最后用GROUP_CONCAT将反应类型和计数拼接成指定格式：

SELECT 
    c.id, 
    c.msg, 
    COALESCE(GROUP_CONCAT(CONCAT(cr.reaction, ':', cr.count) SEPARATOR ', '), '') AS reaction_counts
FROM chats AS c 
LEFT JOIN (
    -- 子查询先统计每个消息的各反应次数
    SELECT chat_id, reaction, COUNT(*) AS count 
    FROM chat_reactions 
    GROUP BY chat_id, reaction
) AS cr ON c.id = cr.chat_id 
GROUP BY c.id;

这个查询会返回类似这样的结果：

更高效的实现思路 #

原查询是先关联所有反应记录再分组拼接，当chat_reactions数据量较大时，会产生大量中间数据。优化后的写法通过子查询提前聚合反应统计，只将每个消息的各反应统计结果与chats关联，减少了连接和分组的计算量，性能更优。

可选：返回JSON格式结果（适合程序处理） #

如果你的MySQL版本支持JSON函数（5.7及以上），可以用JSON_OBJECTAGG直接生成键值对格式的JSON结果，更方便后续程序解析：

SELECT 
    c.id, 
    c.msg, 
    COALESCE(JSON_OBJECTAGG(cr.reaction, cr.count), '{}') AS reaction_counts
FROM chats AS c 
LEFT JOIN (
    SELECT chat_id, reaction, COUNT(*) AS count 
    FROM chat_reactions 
    GROUP BY chat_id, reaction
) AS cr ON c.id = cr.chat_id 
GROUP BY c.id;

返回结果示例：

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