在引用父级和子级时计算行数

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

该表不表示层次结构：每个项目或为子项或为父项，但不能同时具备二者。

还有一个像这样的表格：

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是连接到该类别的帖子数量。

应返回所有类别。

父类别应具有类别计数+子类别总和。（这是我遇到问题的其中一件事）

子类别应具有类别总和。

我该如何做到这一点？

- Rasmus Bengtsson

---

你能发布你的代码吗？ - Hituptony

Answer 1

从你期望的结果来看，似乎你不关心子孙级别以下的内容，那么这个方案应该可行。为了得到正确的父节点数量，我检查是否 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

- sgeddes

---

谢谢，但我认为这不正确，因为它没有计算每个类别中的帖子数量？ - Rasmus Bengtsson

@RasmusBengtsson -- 抱歉，我不理解你的问题。请参见上面的编辑 :) - sgeddes

Answer 2

这是一般提示。我不确定MySQL中是否有分析函数和分区功能，但是您可以通过类别对输出进行分区，然后在类别内进行计数和求和。请查找关于分析函数和分区的内容。我简单举个例子：输出按deptno进行分区并排序。在分区内，max hiredate被替换为count、sum等等...根据您的情况而定。

SELECT * FROM 
(  
SELECT deptno
     , empno
     , ename
     , sal
     , RANK() OVER (PARTITION BY deptno ORDER BY sal desc) rnk 
     , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal desc) rno 
     , MAX(hiredate) OVER (PARTITION BY deptno ORDER BY deptno) max_hire_date
 FROM emp_test
ORDER BY deptno
)
--WHERE rnk = 1
ORDER BY deptno, sal desc
/

DEPTNO    EMPNO    ENAME    SAL    RNK    RNO    MAX_HIRE_DATE
--------------------------------------------------------------------
10        7839    KING      5000    1      1    1/23/1982
10        7782    CLARK     2450    2      2    1/23/1982
10        7934    MILLER    1300    3      3    1/23/1982
20        7788    SCOTT     3000    1      1    1/28/2013
20        7902    FORD      3000    1      2    1/28/2013
20        7566    JONES     2975    3      3    1/28/2013

- Art