日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

本文介紹了遞歸和SQL Server的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！

問題描述

我需要在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

推薦答案

玩了一會兒后，我想我明白了。我添加了一個頂級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

這篇關于遞歸和SQL Server的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，