我正在建立一个音乐博客,当列出所有博客帖子时,我想计算每个博客帖子的喜欢和评论数。我有需要的桌子。这是我的查询
SELECT tbl_music.id,
tbl_music.name,
tbl_music.img,
tbl_music.date,
tbl_music.post,
COUNT(*) AS comms,
COUNT(*) AS liky
FROM tbl_music
LEFT JOIN tbl_mlikes
ON tbl_music.id = tbl_mlikes.mid
LEFT JOIN tbl_mus_coms
ON tbl_music.id = tbl_mus_coms.mid
WHERE tbl_music.status = 'Publish'
GROUP BY tbl_music.id
ORDER BY tbl_music.id
DESC
我注意到的是,它将评论数乘以“喜欢”数,并提供答案作为“喜欢”数,即(如果帖子中有4条评论和1条“赞”,我的查询将显示4条评论和4个“赞”),这是真令人沮丧
最佳答案
问题是您有两个不同的维度,因此查询为每个音乐ID生成笛卡尔乘积。
最好的解决方案是在加入之前进行汇总:
SELECT m.*, l.likes, c.comments
FROM tbl_music m LEFT JOIN
(SELECT mid, COUNT(*) as likes
FROM tbl_mlikes
GROUP BY mid
) l
ON m.id = l.mid LEFT JOIN
(SELECT mc.mid, COUNT(*) as comments
FROM tbl_mus_coms mc
GROUP BY mc.mid
) c
ON m.id = c.mid
WHERE m.status = 'Publish'
ORDER BY m.id DESC ;
关于mysql - 连接多个表的计数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/42731294/