具有 2 个计数和连接的 php 查询

Question

我正在运行一个使用 3 个表格“发布、点赞和评论”的查询，我需要获取帖子获得的点赞和评论数量，同时从帖子表中获取基本信息，因此我使用下面的查询，但问题是如果喜欢更大，它会将 likeAmount 的值复制到 commentAmount，除非 comments 为 0。

SELECT post.*, COUNT(likes.id) as 'LikeAmount', COUNT(comment.id) as 'commentAmount' FROM post 
  LEFT JOIN likes ON post.id = likes.post
  LEFT JOIN comment ON post.id = comment.post
  GROUP BY post.id 
  ORDER BY LikeAmount DESC"

所以这不起作用，但是当我添加 distinct 时它确实起作用，所以当它像这样时:。

SELECT post.*, COUNT(distinct likes.id) as 'LikeAmount', COUNT(distinct comment.id) as 'commentAmount' FROM post 
LEFT JOIN likes ON post.id = likes.post
LEFT JOIN comment ON post.id = comment.post
GROUP BY post.id 
ORDER BY LikeAmount DESC";

我不明白为什么它与 distinct 一起工作而不能与 out 一起工作，并且在 distinct mather performance wise 明智的情况下还是没有差异，因为它将用于具有大量流量的网站..

Answer 1

试试这个，不短，但可读:

SELECT
    p.*,
    pl.like_count,
    pc.comment_count
FROM post p

#join likes
LEFT OUTER JOIN (
    SELECT
        post,
        COUNT(*) AS like_count
    FROM likes
    GROUP BY post
) AS pl
    ON pl.post = p.id

#join comments
LEFT OUTER JOIN (
    SELECT
        post,
        COUNT(*) AS comment_count
    FROM comment
    GROUP BY post
) AS pc
    ON pc.post = p.id