mysql - 正确使用计数

Question

我只想计算所有子评论以及属于画廊的所有图像的所有点赞。

SELECT Count(Message.id) AS subCount, 
       Count(likes.id), 
       Image.id, 
       Image.date_created, 
       Image.likes, 
       Image.path 
FROM   images AS Image 
       LEFT JOIN messages AS Message 
              ON ( Message.object_id = Image.id 
                   AND Message.msg_type = 16 ) 
       LEFT JOIN likes AS Likes 
              ON ( likes.object_id = Image.id 
                   AND likes.object_type = 3 ) 
WHERE  Image.gallery_id = 31 

当我使用 count 时，它只返回 1 行。有什么想法吗？

Answer 1

您需要一个GROUP BY:

SELECT Count(Message.id) AS subCount, 
       Count(likes.id), 
       Image.id, 
       Image.date_created, 
       Image.likes, 
       Image.path 
FROM   images AS Image 
       LEFT JOIN messages AS Message 
              ON ( Message.object_id = Image.id 
                   AND Message.msg_type = 16 ) 
       LEFT JOIN likes AS Likes 
              ON ( likes.object_id = Image.id 
                   AND likes.object_type = 3 ) 
WHERE  Image.gallery_id = 31 
GROUP  BY Image.id, 
          Image.date_created, 
          Image.likes, 
          Image.path