mysql - 如何正确计数表2中与表1中的项目相关的行，并使用与表1不相关的子句

我有一个简单的杂志，有一个表格，上面有帖子，评论，分类等等。当我列出一个分类的时候，我想在一个列表中列出每个帖子的评论的总和，但是这个数字是错误的，它让我疯狂。请注意，单个帖子可以分为多个类别。
下面是简单的表结构

posts
id | title | categoryid | content | published
---------------------------------------------

comments
id | postid | comment
---------------------

category_rel
postid | categoryid
-------------------

categories
id | category
-------------

我使用以下sql（简化为本例）：

SELECT posts.*, categories.id AS categoryid,
  categories.category AS categorytitle,
  COUNT(comments.postid) AS CNT
FROM posts
LEFT JOIN comments ON posts.id = comments.postid
INNER JOIN category_rel ON posts.id = category_rel.postid
INNER JOIN categories ON category_rel.categoryid = categories.id
WHERE posts.published=1
GROUP BY posts.id;

这句话给了我一个错误的结果，有点像它的累计类别数post是成员和乘以实际的评论数。如果我删除SQL的category部分（这不好，我需要category Id和name），我将收到正确的值：

SELECT posts.*, COUNT(comments.postid) AS CNT
FROM posts
LEFT JOIN comments ON posts.id = comments.postid
WHERE posts.published=1
GROUP BY posts.id;

具体来说：
其中一个帖子有1条评论，它是7个类别的成员。CNT的值是7，而不是1。
知道如何更改第一个SQL以获得正确的值吗？

最佳答案

你想计算每篇文章的评论数，而不是按类别。因此，实现这一点的一种方法是首先进行计数（在子选择中，因为MySQL到目前为止还没有CTE），然后将结果加入到category表中：

SELECT countpost.*, categories.id AS categoryid,
   categories.category AS categorytitle
  FROM
  -- subselect post and comment count
  (
    SELECT posts.*, count(comments.postid) as CNT FROM posts
     LEFT JOIN comments ON posts.id = comments.postid
       WHERE posts.published = 1
       GROUP BY posts.id
  ) as countpost
  -- join category table
INNER JOIN category_rel ON countpost.id = category_rel.postid
INNER JOIN categories ON category_rel.categoryid = categories.id ;

看这个小提琴：http://sqlfiddle.com/#!9/f9c6f/1

关于mysql - 如何正确计数表2中与表1中的项目相关的行，并使用与表1不相关的子句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37942247/