mysql - 计算引用 parent 和 child 的行数

Question

我有一个看起来像这样的表:

Categories:
cId | Name                    | Parent
----+-------------------------+-------
1   | Parent One              | NULL
2   | Child of 1st Parent     | 1
3   | Parent Two              | NULL
4   | Child of 1st Parent     | 1
5   | Child of 2nd Parent     | 2

该表不代表层次结构:每个项目要么是子项要么是父项，但不能两者都是。

还有一张这样的 table :

Posts:
pId | Name                    | cID
----+-------------------------+-------
1   | Post 1                  | 1
2   | Post 2                  | 2
3   | Post 3                  | 2
4   | Post 4                  | 3

我想对它运行一个返回这个的查询:

cId | Count  
---+---------
1   | 3
2   | 2
3   | 1
4   | 0
5   | 0

Count 是与该类别相关的帖子数。

应返回所有类别。

父类别应该有类别 + 子类别总和的计数。(这是我遇到的问题之一)

子类别应具有类别总和。

我应该怎么做？

Answer 1

从您的预期结果来看，您似乎并不关心孙辈和更小的 child ，在这种情况下，这应该可行。为了获得正确的 parent 数量，我正在检查 Parent IS NULL 或 Count(children) > 0，在这种情况下，我要添加 1:

SELECT c.cId, CASE WHEN C.Parent IS NULL OR COUNT(C2.cId) > 0 THEN 1 ELSE 0 END + 
    COUNT(C2.cId) TotalCount
FROM Categories C
    LEFT JOIN Categories C2 on c.cId = c2.Parent
GROUP BY c.cId

这是一些示例 fiddle :http://www.sqlfiddle.com/#!2/b899f/1

结果:

CID  TOTALCOUNT
1    3
2    2
3    1
4    0
5    0

---编辑---

通过阅读您的评论，您似乎想要这样的东西:

SELECT c.cId, 
    COUNT(DISTINCT P.pId) + COUNT(DISTINCT P2.pId) TotalCount
FROM Categories C
   LEFT JOIN Posts P ON C.CId = P.CId
    LEFT JOIN Categories C2 on c.cId = c2.Parent
   LEFT JOIN Posts P2 ON C2.CId = P2.CId
GROUP BY c.cId

http://www.sqlfiddle.com/#!2/eb0d2/3