如何返回 2 COUNT()

Question

我有这个表：

forum_categories

• id
• title

forum_topics

• id
• Category_id (n-1 with forum_categories.id)

forum_messages

• id
• topic_id (n-1 with forum_topics.id)
• 首先

我想返回 forum_categories.title ， count(forum_topics.id) （我的意思是该类别的主题数量） ， count(forum_messages.id) （我的意思是该主题的消息数，其中字段 first 为 0）。

这是一个例子：

forum_categories            forum_topics            forum_messages
1   title1                  1   1                   1       1       0
2   title2                  2   2                   2       2       1
3   title3                  3   1                   3       3       1
                            4   1                   4       6       1
                            5   3                   5       7       0
                            6   3                   6       2       0
                            7   2                   7       1       1
                                                    8       3       0
                                                    9       5       0
                                                    10      7       1
                                                    11      5       1
                                                    12      1       0

输出必须是：

title1      3       3 (topic 1=2 + topic 3=1 topic4=0
title2      2       2 (topic2=1 + topic7=1)
title3      2       1  (topic5=1 + topic6=0)

尝试过类似的东西：

SELECT forum_categories.description, 
       COUNT(forum_topics.id), 
       COUNT(forum_messages.id)
  FROM forum_categories 
  JOIN forum_topics 
  JOIN forum_messages ON forum_categories.id = forum_topics.category_id 
                     AND forum_topics.id = forum_messages.topic_id
GROUP BY forum_categories.id, forum_messages.id
ORDER BY forum_categories.date

但它绝对远离我的目标（而且我不知道如何检查 *forum_messages.first* 字段！有帮助吗？

编辑

感谢吉姆鲁宾斯坦，这是一个部分解决方案：

SELECT fc.description, COUNT(DISTINCT ft.id) topics, COUNT(fm.id) 
FROM forum_categories fc 
JOIN forum_topics ft ON ft.category_id = fc.id 
JOIN forum_messages fm ON fm.topic_id = ft.id
GROUP BY fc.id ORDER BY fc.date 

这个查询的问题是计算来自forum_messages的每条消息，但我只需要在以下情况下计算每个主题的这些消息： field=0 。

编辑2

我想我已经找到了解决方案：

SELECT fc.title, COUNT(DISTINCT ft.id) topics, COUNT(fm.id) 
FROM forum_categories fc 
JOIN forum_topics ft ON ft.category_id = fc.id 
LEFT OUTER JOIN (SELECT id, topic_id, first FROM forum_messages WHERE first=0) fm ON fm.topic_id = ft.id
GROUP BY fc.id ORDER BY fc.date 

您认为我可以改进吗？

Answer 1

您应该能够使用DISTINCT关键字来完成此任务。您还需要对 category_id 进行GROUP BY，以便获得每个类别的主题/消息的计数。

SELECT fc.description, COUNT(DISTINCT ft.id) topics, COUNT(fm.id) messages
FROM forum_categories fc
JOIN forum_topics ft ON ft.category_id = fc.id
JOIN form_messages fm ON fm.topic_id = ft.id
WHERE fm.first = 0
GROUP BY fc.id
ORDER BY fc.date

另一个选项：

SELECT fc.description, COUNT(DISTINCT ft.id) topics, SUM(CASE WHEn fm.first = 0 THEN 1 ELSE 0) messages
FROM forum_categories fc
JOIN forum_topics ft ON ft.category_id = fc.id
JOIN form_messages fm ON fm.topic_id = ft.id
WHERE fm.first = 0
GROUP BY fc.id
ORDER BY fc.date

注意：我将您的表别名为较短的名称...因为我懒得一遍又一遍地输入全名。哈哈。

Answer 2

您可以使用子查询，例如：

 select (select count(*) from ...., select count(*) from ....)