php - 如何用多表的SUM和MINUS按COUNT排序

Question

我正在尝试通过评论和喜欢的总和来显示帖子。此查询中使用了三个表post、comment 和like
对于像它具有保持值喜欢或不喜欢的列type。

SQL

SELECT (SELECT COUNT(id) AS count_comment 
FROM comment WHERE comment.post_id = post.post_id),
       (SELECT COUNT(id) AS count_like 
FROM like WHERE like.post_id = post.post_id AND like.type = 'like'),
       (SELECT COUNT(id) AS count_unlike 
FROM like WHERE like.post_id = post.post_id AND like.type = 'unlike'),
       post.* FROM post
       ORDER BY (count_comment + count_like - count_unlike) DESC;

所以，这是一个显示在页面上的例子

post_id | comment | like | unlike | (comment+like-unlike)
4       | 5       | 3    | 1      |  7
1       | 2       | 3    | 0      |  5 
2       | 1       | 1    | 4      | -2 
...     | ...     | ...  | ...    | ...

我的问题是我的 SQL 很慢，如果可以的话请建议另一种方法。我试过使用 JOIN 但我无法弄清楚它的 SQL 应该如何，请帮助谢谢。

Answer 1

对每个计数使用派生表，下面的查询对每个帖子的评论、喜欢、不喜欢进行计数，然后通过 post_id 将计数连接到 post 表.

SELECT 
    p.post_id,
    COALESCE(c.comment_count,0) comment_count,
    COALESCE(l.like_count,0) like_count,
    COALESCE(ul.unlike_count,0) unlike_count,
    (COALESCE(c.comment_count,0) 
       + COALESCE(l.like_count,0) 
       - COALESCE(ul.unlike_count,0)) total
FROM post p
LEFT JOIN (
    SELECT c.post_id,
    COUNT(*) comment_count
    FROM comment c
    GROUP BY c.post_id
) c ON c.post_id = p.post_id
LEFT JOIN (
    SELECT l.post_id,
    COUNT(*) like_count
    FROM like l
    WHERE l.type = 'like'
    GROUP BY l.post_id
) l ON l.post_id = p.post_id
LEFT JOIN (
    SELECT ul.post_id,
    COUNT(*) unlike_count
    FROM like ul
    WHERE ul.type = 'unlike'
    GROUP BY ul.post_id
) ul ON ul.post_id = p.post_id
ORDER BY total DESC