连接多个表的计数

连接多个表的计数

我正在建立一个音乐博客,当列出所有博客帖子时,我想计算每个博客帖子的喜欢和评论数。我有需要的桌子。这是我的查询

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/

10-11 12:06