mysql查询计数

Question

我正在执行sql查询，以便从db，db结构中获取值，如下所示.

fcat

| id | title  | section |
+----+--------+---------+
|  1 | test   | gd      |
+----+--------+---------+

专题

| id | title  | cat_id  |
+----+--------+---------+
|  1 | test1  | 1       |
+----+--------+---------+

fpost

| id | post  | topic_id |
+----+-------+----------+
|  1 | post  | 1        |
+----+-------+----------+

我希望获得给定类别的主题和帖子的总数，并使用以下查询.

SELECT id, title (SELECT count(id) FROM ftopics WHERE cat_id = id) AS total_topics FROM fcats WHERE section = "gd"

它给出了正确的结果，但我如何才能得到所有类别的帖子总数，我对此感到困惑，请帮我写这个查询。谢谢。

Answer 1

试试这个：

SELECT
    t.id,
    t.title,
    COUNT(f.id) + COUNT(p.id) AS total
FROM fcats t
LEFT OUTER JOIN ftopics f
    ON f.cat_id = t.id
LEFT OUTER JOIN fposts p
    ON p.cat_id = t.id
WHERE t.section = "gd"
GROUP BY t.id, t.title

Answer 2

SELECT
    t.id,
    t.title,
COUNT(f.id) AS total_topics,
(SELECT COUNT(p.id) FROM fposts WHERE topic_id = t.id) total_posts
FROM fcats t
INNER JOIN ftopics f
    ON f.cat_id = t.id

WHERE t.section = "gd"
GROUP BY t.id, t.title

不确定这是否是正确的语法，更新其他答案代码，以尝试回答您对这两个总计的请求。