SQL CTE计算子递归 [英] SQL CTE counting childs recursion

问题描述

我想（使用cte）以这种方式对表格中的孩子进行计数，以拥有父级所有孩子的数量，包括他们的孩子。

I'd like (using cte) to count children in table in that way to have at parent level number of all children including theirs children. Is there any sample available?

推荐答案

CREATE TABLE t_parent (id INT NOT NULL PRIMARY KEY, parentID INT NOT NULL)

INSERT
INTO    t_parent
VALUES  (1, 0)
INSERT
INTO    t_parent
VALUES  (2, 1)
INSERT
INTO    t_parent
VALUES  (3, 1)
INSERT
INTO    t_parent
VALUES  (4, 2)
INSERT
INTO    t_parent
VALUES  (5, 1)
INSERT
INTO    t_parent
VALUES  (6, 5)
INSERT
INTO    t_parent
VALUES  (7, 5);

WITH    q AS
        (
        SELECT  id, parentId
        FROM    t_parent
        UNION ALL
        SELECT  p.id, p.parentID
        FROM    q
        JOIN    t_parent p
        ON      p.id = q.parentID
        )
SELECT  id, COUNT(*)
FROM    q
GROUP BY
        id

这篇关于SQL CTE计算子递归的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！