SQL查询:如何进行多次统计？(返回每个论坛的帖子数量和主题数量)

Question

我在获取数据库中每个论坛的帖子和主题的数量时遇到了一些问题。我可以通过两次查询得到这些值，但我想知道是否有可能只用一次查询。

此查询获取每个论坛的主题数量：

select forums.forumId, forums.forumName, count(*) as Topics FROM Topics 
INNER JOIN forums ON forums.forumId = topics.forumID 
GROUP BY forums.forumId;

此查询获取每个论坛的帖子数量：

select forums.forumId, forums.forumName, count(*) as Posts FROM posts 
INNER JOIN topics ON topics.topicID = posts.topicId 
INNER JOIN forums ON forums.forumId = topics.forumID 
GROUP BY forums.forumId;

如何在一个查询中同时获得帖子和主题计数？

Answer 1

SELECT  forums.forumId, forums.forumName,
        COUNT(DISTINCT topics.TopicID) AS Topics,
        COUNT(*) as Posts
FROM    forums
INNER JOIN
        topics
ON      topics.forumID = forums.forumId
INNER JOIN
        posts 
ON      posts.topicId  = topics.topicID
GROUP BY
        forums.forumId

Answer 2

SELECT  forums.forumId, forums.forumName,
        COUNT(DISTINCT topics.TopicID) AS Topics,
        COUNT(posts.topicId) as Posts
FROM    forums
LEFT OUTER JOIN topics
ON      topics.forumID = forums.forumId
LEFT OUTER JOIN posts 
ON      posts.topicId  = topics.topicID
GROUP BY
        forums.forumId

如果您想要计算没有主题或帖子的论坛，则需要使用左外部连接