如何在SQL Server递归CTE中总和一列以进行优化

Question

我的下表带有分层数据：

FolderId ParentFolderId NumberOfAffectedItems
---------------------------------------------
1           NULL        2
2           1           3
3           2           5
4           2           3
5           1           0

我想在每个文件夹及其所有孩子下找到数量的受影响的物品。我可以写一个递归CTE，可以产生以下结果，然后通过进行小组来找出我想要的东西。

正常递归CTE：

WITH FolderTree AS
(
    SELECT
        fsa.FolderId AS ParentFolderId,
        fsa.FolderId AS ChildFolderId,          
        fsa.NumberOfReportsAffected
    FROM
        FoldersWithNumberOfReportsAffected fsa
    UNION ALL
    SELECT
        ft.ParentFolderId,
        fsa.FolderId AS ChildFolderId,                  
        fsa.NumberOfReportsAffected
    FROM
        FoldersWithNumberOfReportsAffected fsa
    INNER JOIN
        FolderTree ft ON fsa.ParentFolderId = ft.ChildFolderId          
  )

结果：

ParentFolderId ChildFolderId NumberOfAffectedItems
--------------------------------------------------
1               1           2
1               2           3
1               3           5
1               4           3
1               5           0
2               2           3
2               3           5
2               4           3
3               3           5
4               4           3
5               5           0

但我想优化它，我想从叶子孩子开始通过CTE本身，我想计算NumberOfAffectedItems。

预期CTE

WITH FolderTree AS
(
    SELECT
        fsa.FolderId AS LeafChildId,
        fsa.FolderId AS ParentFolderId,         
        fsa.NumberOfReportsAffected
    FROM
        FoldersWithNumberOfReportsAffected fsa
    LEFT JOIN
        FoldersWithNumberOfReportsAffected f ON fsa.folderid = f.ParentfolderId
    WHERE
        f.ParentfolderId is null -- this is finding leaf child
    UNION ALL
    SELECT
        ft.LeafChildId,
        fsa.FolderId AS ParentFolderId,                 
        fsa.NumberOfReportsAffected + ft.NumberOfReportsAffected AS [ComputedResult]
    FROM
        FoldersWithNumberOfReportsAffected fsa
    INNER JOIN 
        FolderTree ft ON fsa.FolderId = ft.ParentFolderId
  )

结果：

LeafChildId ParentFolderId ComputedNumberOfAffectedItems
---------------------------------------------------------
3           3               5
3           2               8
3           1               10
4           4               3
4           2               5
4           1               7
5           5               0
5           1               2

如果我按ParentFolderId进行了分组，我会得到错误的结果，原因是在CTE进行计算时，从多个访问了同一父文件夹儿童，因此结果错误。无论如何，我想知道是否可以在浏览CTE本身时计算结果。

Answer 1

请检查以下解决方案。我将您的CTE用作基础，并将计算（作为X列）添加到其中：

DECLARE @t TABLE(
  FolderID INT
 ,ParentFolderID INT
 ,NumberOfAffectedItems INT
);
INSERT INTO @t VALUES (1           ,NULL        ,2)
                     ,(2           ,1           ,3)
                     ,(3           ,2           ,5)
                     ,(4           ,2           ,3)
                     ,(5           ,1           ,0);

WITH FolderTree AS
(
    SELECT 1lvl,
        fsa.FolderId AS LeafChildId,
        fsa.ParentFolderId AS ParentFolderId,
        fsa.NumberOfAffectedItems
    FROM
        @t fsa
    LEFT JOIN
        @t f ON fsa.folderid = f.ParentfolderId
    WHERE
        f.ParentfolderId is null -- this is finding leaf child
    UNION ALL
    SELECT lvl + 1,
        ft.LeafChildId,
        fsa.ParentFolderId,                 
        fsa.NumberOfAffectedItems
    FROM
        FolderTree ft
    INNER JOIN @t fsa
        ON fsa.FolderId = ft.ParentFolderId
  )
SELECT  LeafChildId,
        ISNULL(ParentFolderId, LeafChildId) ParentFolderId,
        NumberOfAffectedItems,
        SUM(NumberOfAffectedItems) OVER (PARTITION BY LeafChildId ORDER BY ISNULL(ParentFolderId, LeafChildId) DESC) AS x
  FROM FolderTree
  ORDER BY 1, 2 DESC
  OPTION (MAXRECURSION 0)

结果：

LeafChildId ParentFolderId  NumberOfAffectedItems   x
3           3               2                       2
3           2               5                       7
3           1               3                       10
4           4               2                       2
4           2               3                       5
4           1               3                       8
5           5               2                       2
5           1               0                       2