使用CTE MS SQL聚合所有的后代

Question

我有一个非常简单的表在microsoft SQL 2008 R2数据库中称为"祖先"的形式:

parentSearch childSearch
1195784 1185596
1195787 1185596
1195787 1195785
1195787 1195786
1195799 1185596
1185596 1195785
1195785 1195786
1195786 1195776
1195786 1195788
1195786 1195790
1195786 1195796
1195786 1195798
1195785
1195786 1195776
1195786 1195788
1195786 1195790
1195786 1195796
1195786 1195798
1195786 1195776
1195786 1195788
1195786 1195790
1195786
1195786 1195798

这是一个祖先表，其后代至少有四层深。我已经把自己打蓝了脸，试图写一个CTE来反对这一点，这将汇总每个父级的后代总数，而不是他们所处的级别。此数据中的单个子节点可以有多个父节点。

Answer 1

试试这个:

with temp as
(
SELECT 1195784 as Parent, 1185596 as Child
UNION
SELECT 1195787, 1185596
UNION
SELECT 1195787, 1195785
UNION
SELECT 1195787, 1195786
UNION
SELECT 1195799, 1185596
UNION
SELECT 1185596, 1195785
UNION
SELECT 1195785, 1195786
UNION
SELECT 1195786, 1195776
UNION
SELECT 1195786, 1195788
UNION
SELECT 1195786, 1195790
UNION
SELECT 1195786, 1195796
UNION
SELECT 1195786, 1195798
UNION
SELECT 1195785, 1195786
UNION
SELECT 1195786, 1195776
UNION
SELECT 1195786, 1195788
UNION
SELECT 1195786, 1195790
UNION
SELECT 1195786, 1195796
UNION
SELECT 1195786, 1195798
UNION
SELECT 1195786, 1195776
UNION
SELECT 1195786, 1195788
UNION
SELECT 1195786, 1195790
UNION
SELECT 1195786, 1195796
UNION
SELECT 1195786, 1195798
),
agg as
(
        SELECT  parent, child
        FROM    temp
        UNION ALL
        SELECT  t.parent, t.Child
        FROM    agg
        JOIN    temp t
        ON      agg.child = t.parent
)
SELECT parent, COUNT(*)
FROM agg
GROUP BY parent

Answer 2

试试这个:

WITH CTE AS
(
    SELECT *
    FROM Ancestry
    UNION ALL
    SELECT A.ParentSearch, B.ChildSearch
    FROM CTE A
    INNER JOIN Ancestry B
    ON A.ChildSearch = B.ParentSearch
)
SELECT ParentSearch, COUNT(*) Quant
FROM CTE
GROUP BY ParentSearch
ORDER BY ParentSearch
OPTION(MAXRECURSION 0)