MySQL 统计返回不同结果的孙子表

Question

我正在开发一个 PHP 论坛。 这个论坛使用了四个数据库表：forum、thread、post、user。

在我的登陆页面上，我列出了所有论坛，以及最新线程（通过联接和内部联接实现）、总线程（简单计数子查询）和帖子总数的列。

我有一个相当大小的查询，它返回上述所有内容，并且一切都运行得很好 - 除了帖子总数。

主要查询是这样的：

select f.id as forum_id,
f.name as forum_name,
f.description,
t.forum_id,
#this subquery counts total threads in each forum
(select count(t.forum_id)
    from thread t
    where t.forum_id = f.id
    ) as total_threads,
#this query counts total posts for each forum
(SELECT COUNT( p.id )
    FROM post p
    WHERE p.thread_id = t.id
    AND t.forum_id = f.id
    GROUP BY f.id) as total_posts,
t.id as thread_id,
t.name as thread_name,
t.forum_id as parent_forum,
t.user_id,
t.date_created,
u.id as user_id,
u.username
from forum f
#    this join finds all latest threads of each forum
join
    (select forum_id, max(date_created) as latest
    from thread
    group by forum_id) as d on d.forum_id = f.id
#and this inner join grabs the rest of the thread table for each latest thread
inner join thread as t
on d.forum_id = t.forum_id
and d.latest = t.date_created
join user as u on t.user_id = u.id

所以，如果您将注意力集中在上面的总帖子子查询上 你会注意到 htat 我正在计算所有帖子，其中它们的线程 id = 每个线程的 id，然后 = 每个论坛的 id，如果我单独使用此查询（并包括主查询中其他地方使用的表别名）它可以工作完美。

但是，当在主查询的上下文中使用时，并且在其他地方提供表别名时，它仅返回第一个线程 p/forum 的计数。

如果我尝试在子查询中声明表别名，它会返回已返回多行的错误。

为什么查询内容存在差异，以及为什么在主查询中用作计算字段时仅计算第一个线程？

Answer 1

由于 t.forum_id 和 f.id 仅在子查询之外相关，因此您的子查询相当于：

IF(t.forum_id = f.id, 
(SELECT COUNT(p.id) 
FROM post p
WHERE p.thread_id = t.id
GROUP BY 1)
, 0) AS total_posts

您可能想要这样的内容：

SELECT f.name AS forum_name, COUNT(p.id) AS total_posts
FROM forum AS f
JOIN thread AS t ON t.forum_id = f.id
JOIN post AS p ON p.thread_id = t.id
GROUP BY f.id

该查询将为每个论坛返回一行，并且应正确包含帖子计数。

请注意，如果论坛中没有帖子，则此查询不会返回该论坛 - 如果您需要注意的话，您可以使用 LEFT JOIN 而不是 JOIN 来更改它。