mysql - 如何使用与 table1 无关的子句正确计算 table2 中与 table1 中的项目相关的行

Question

我有一个简单的杂志，还有包含帖子、评论、类别等的表格。在列出单个类别时，我想在列表中列出每个帖子的评论总和，但这个数字是错误的，这让我发疯.请注意，单个帖子可以属于多个类别。

这是简单的表结构

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;

这个陈述给我错误的结果，有点像帖子所属类别的累积数量并乘以评论的实际数量。如果我删除 SQL 的类别部分(这不好，我需要类别 ID 和名称)，我会收到正确的值:

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 以获得正确的值吗？

Answer 1

您想计算每个帖子的评论数 - 而不是每个类别。因此，实现这一目标的一种方法是首先进行计数(在子选择中，因为 MySQL 目前还没有 CTE)，然后将结果加入类别表中:

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 ;

请参阅此 fiddle :http://sqlfiddle.com/#!9/f9c6f/1