递归SUM Sql服务器

Question

我需要在SQL Server中进行递归求和。我想要一个存储过程，我可以传入一个父ID，然后返回所有与该父ID相链接的子（和子的孩子）的总数。

这里是我迄今为止

IF object_id('tempdb..#Averages') IS NOT NULL 
BEGIN 
    DROP TABLE #Averages 
END 


CREATE TABLE #Averages 
(
ID INT PRIMARY KEY CLUSTERED IDENTITY(1,1), 
Name VARCHAR(255), 
ParentID int, 
Value INT 
) 

INSERT INTO #Averages(Name,ParentID,Value)VALUES('Fred',NULL,1) 
INSERT INTO #Averages(Name,ParentID,Value)VALUES('Bets',NULL,1) 

INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Wynand',ID,21 FROM #Averages WHERE  Name = 'Fred' ) 

INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Dewald',ID,27 FROM #Averages WHERE  Name = 'Fred' ) 
INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Katelynn',ID,1 FROM #Averages WHERE Name = 'Dewald' ) 

INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Jacques',ID,28 FROM #Averages WHERE Name = 'Bets' ) 
INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Luan',ID,4 FROM #Averages WHERE Name = 'Jacques' ) 
INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Ruben',ID,2 FROM #Averages WHERE Name = 'Jacques' ) 


;WITH Personal AS 
(
SELECT N=1, ID,Name,ParentID,Value 
FROM #Averages 
WHERE ParentID IS NULL 
UNION ALL 
SELECT N+1, Av.ID,Av.Name,Av.ParentID,Av.Value 
FROM #Averages Av 
INNER JOIN Personal P ON P.ID = Av.ParentID 
) 

SELECT Name, 
    SUM(Value) as Total 
FROM Personal 
WHERE N<=3 
GROUP BY Name 

Answer 1

玩了一下后，我想我明白了。我添加了一个顶级ID，这是我在CTE的Root中设置的。然后，只需为所有递归添加顶级ID。

最后我只求和，基本上用TopLevelId加入顶层表。

IF object_id('tempdb..#Averages') IS NOT NULL 
BEGIN 
    DROP TABLE #Averages 
END 

CREATE TABLE #Averages 
(
    ID INT PRIMARY KEY CLUSTERED IDENTITY(1,1), 
    Name VARCHAR(255), 
    ParentID int, 
    Value INT 
) 

INSERT INTO #Averages(Name,ParentID,Value)VALUES('Fred',NULL,1) 
INSERT INTO #Averages(Name,ParentID,Value)VALUES('Bets',NULL,1) 

INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Wynand',ID,21 FROM #Averages WHERE Name = 'Fred' ) 

INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Dewald',ID,27 FROM #Averages WHERE Name = 'Fred' ) 
INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Katelynn',ID,1 FROM #Averages WHERE Name = 'Dewald' ) 

INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Jacques',ID,28 FROM #Averages WHERE Name = 'Bets' ) 
INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Luan',ID,4 FROM #Averages WHERE Name = 'Jacques' ) 
INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Ruben',ID,2 FROM #Averages WHERE Name = 'Jacques' ) 


;WITH Personal AS 
(
    SELECT N=1, 
     ID, 
     Name, 
     ParentID, 
     Value, 
     TopLevelID =ID 
    FROM #Averages 
    WHERE ParentID IS NULL 

    UNION ALL 

    SELECT N+1, 
     Av.ID, 
     Av.Name, 
     Av.ParentID, 
     Av.Value, 
     TopLevelID =P.TopLevelID 
    FROM #Averages Av 
    INNER JOIN Personal P ON P.ID = Av.ParentID 
) 

SELECT SUM(P.Value) AS Total, 
     A.Name 
FROM Personal P 
INNER JOIN #Averages A on A.ID = P.TopLevelID 
GROUP BY A.Name 

Answer 2

这里实现你想要的东西的一种方式，尽管它的做法略有不同，你有以上：

SQLFiddle

Create Table #Ancestors (
    ID int 
    , Name VARCHAR(255) 
    , ParentID int 
    , AncestryCompleteTF tinyint 
    , Ancestors varchar(max) 
    , TotalValue int  
) 

INSERT INTO #Ancestors 
SELECT 
    ID 
    , Name 
    , ParentID 
    , CASE ISNULL(ParentID, 0) 
    WHEN 0 THEN 1 
    ELSE 0 
    END 
    , CONVERT(VARCHAR, ISNULL(ParentID, '')) 
    , Value 
FROM 
    Averages 

WHILE EXISTS (SELECT * FROM #Ancestors WHERE AncestryCompleteTF = 0) 
BEGIN 
    UPDATE C SET 
    C.Ancestors = P.Ancestors + ',' + CONVERT(VARCHAR, P.ID), 
    C.AncestryCompleteTF = 1, 
    C.TotalValue = P.TotalValue + C.TotalValue 
    FROM #Ancestors C 
    INNER JOIN #Ancestors P ON (C.ParentID = P.ID) 
    AND P.AncestryCompleteTF = 1 
END 

SELECT 
    Name 
, TotalValue 
FROM 
    #Ancestors 

基本上我创建了一个临时表，并使用一个while循环来更新已经计算出父代的行的总数（因为这仅仅是一个广告的例子将当前行的总数改为父行的总数），直到所有行都已计算完毕。 ParentID为空的行被设置为开头，所以他们的直接后代将首先计算，然后这些行的后代等。等等。