SQL COUNT返回单行问题：如何统计评论赞踩数并按行展示

你的SQL存在两个核心问题：

• 缺少GROUP BY子句：聚合函数COUNT会把所有匹配行合并成一条结果，无法按单条评论分组统计点赞、点踩数。
• 冗余关联条件：CASE里的r.CommentID = c.ID完全多余，因为LEFT JOIN已经通过c.ID = r.CommentID建立了关联，这部分条件不会改变结果，只会增加代码冗余。

修改后的SQL语句 #

SELECT 
    c.ID as CommentID, 
    c.message,
    COUNT(CASE WHEN r.Liked = 1 THEN 1 END) as likes, 
    COUNT(CASE WHEN r.Liked = 0 THEN 1 END) as dislikes
FROM tblcomments as c 
LEFT JOIN tblcommentreactions as r ON c.ID = r.CommentID
WHERE c.userID = 1
GROUP BY c.ID, c.message;

也可以用SUM替代COUNT，逻辑更直观（CASE满足条件返回1，否则返回null，SUM会自动忽略null）：

SELECT 
    c.ID as CommentID, 
    c.message,
    SUM(CASE WHEN r.Liked = 1 THEN 1 ELSE 0 END) as likes, 
    SUM(CASE WHEN r.Liked = 0 THEN 1 ELSE 0 END) as dislikes
FROM tblcomments as c 
LEFT JOIN tblcommentreactions as r ON c.ID = r.CommentID
WHERE c.userID = 1
GROUP BY c.ID, c.message;

关键修改点说明 #

1. 添加GROUP BY：必须按评论的唯一标识（c.ID）和需要展示的字段（c.message）分组，这样聚合函数才会针对每条评论单独统计。
2. 简化CASE条件：去掉r.CommentID = c.ID，JOIN已经保证了关联关系，无需重复判断。

执行修改后的SQL就能得到预期结果：

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