mysql - 计算特定主题的两个不同表之间的喜欢和评论

Question

我有两个表，likes 和 comments 这两个表都涉及主题帖子 - 例如论坛的主题。

它们都有一个引用特定 topic_id 的列。

现在是这样:我想创建一个最喜欢 + 评论的前 5 个图表，两者的总和我的意思是主题。

例如，我执行此查询是为了从主题表中选择最喜欢的，我想用喜欢 + 评论的总数制作相同的图表。以下是我点赞总数排名前 5 位的主题。

SELECT topics.* ,
COUNT(q_id)
AS post_count 
FROM topics 
LEFT JOIN likes 
ON topics.id = likes.q_id 
WHERE topics.to_user = 'someuser' 
GROUP BY likes.q_id  
ORDER BY post_count DESC 
LIMIT 0, 5

提前发送!

Answer 1

这会很有魅力!!

      SELECT topics.id
            ,count(DISTINCT(comments.id)) + count(DISTINCT(likes.id)) AS score 
      FROM topics
      LEFT JOIN comments ON topics.id = comments.post_id 
      LEFT JOIN likes ON topics.id = likes.post_id 
      WHERE topics.to_user = 'someuser' 
      GROUP BY topics.id 
      ORDER BY score desc 
      LIMIT 0, 5;