SQL Server列的层次求和

Question

根据图表，我已经设计好了我的数据库。

• Category表是自关联的父子关系。
• Budget表将定义每个类别和金额的所有类别。
• Expense表将为已支出金额的类别输入条目（考虑此表中的Total列）。

我想编写一个SELECT语句来检索以下列的数据集:

ID   
CategoryID   
CategoryName   
TotalAmount (Sum of Amount Column of all children hierarchy From BudgetTable  )   
SumOfExpense (Sum of Total Column of Expense  all children hierarchy from expense table)

我尝试使用CTE，但是无法产生有用的结果。提前感谢您的帮助。:)

更新

我刚刚创建了一个包含以下查询的视图，以组合和简化数据。

SELECT        
    dbo.Budget.Id, dbo.Budget.ProjectId, dbo.Budget.CategoryId, 
    dbo.Budget.Amount, 
    dbo.Category.ParentID, dbo.Category.Name, 
    ISNULL(dbo.Expense.Total, 0) AS CostToDate
FROM
    dbo.Budget 
INNER JOIN
    dbo.Category ON dbo.Budget.CategoryId = dbo.Category.Id 
LEFT OUTER JOIN
    dbo.Expense ON dbo.Category.Id = dbo.Expense.CategoryId

基本上，这应该会产生像这样的结果。

- Sachin Trivedi

---

请提供一些样本数据和期望结果。 - Felix Pamittan

@SachinTrivedi，尝试一下我在答案中提供的递归查询。由于我无法访问某些数据进行测试运行，您可能需要对其进行一些调整。 - Sunil

@SachinTrivedi，我稍后回来看看，因为我处于不同的时区。 - Sunil

@SachinTrivedi，我使用了一些测试数据来得出最终解决方案，应该能够满足您的要求。请查看我的答案。我运行了查询，它按照层次结构从底部到顶部进行了总计。 - Sunil

@Sunil，非常感谢你在这方面的帮助。Ben提出的方法为我的情况提供了更清晰的解决方案。 - Sachin Trivedi

Answer 1

这是一个有趣的问题。我将使用hierarchyid来解决它。首先，让我们来看一下设置：

USE tempdb;
IF OBJECT_ID('dbo.Hierarchy') IS NOT NULL
    DROP TABLE dbo.[Hierarchy];

CREATE TABLE dbo.Hierarchy 
(
    ID INT NOT NULL PRIMARY KEY,
    ParentID INT NULL,
        CONSTRAINT [FK_parent] FOREIGN KEY ([ParentID]) REFERENCES dbo.Hierarchy([ID]),
    hid HIERARCHYID,
    Amount INT NOT null
);

INSERT INTO [dbo].[Hierarchy]
        ( [ID], [ParentID], [Amount] )
VALUES  
    (1, NULL, 100 ),
    (2, 1, 50),
    (3, 1, 50),
    (4, 2, 58),
    (5, 2, 7),
    (6, 3, 10),
    (7, 3, 20)
SELECT * FROM dbo.[Hierarchy] AS [h];

接下来，为了更新hierarchyid的hid列，我将使用一个标准的递归cte。

WITH cte AS (
    SELECT  [h].[ID] ,
            [h].[ParentID] ,
            CAST('/' + CAST(h.[ID] AS VARCHAR(10)) + '/' AS VARCHAR(MAX)) AS [h],
            [h].[hid]
    FROM    [dbo].[Hierarchy] AS [h]
    WHERE   [h].[ParentID] IS NULL

    UNION ALL

    SELECT  [h].[ID] ,
            [h].[ParentID] ,
            CAST([c].[h] + CAST(h.[ID] AS VARCHAR(10)) + '/' AS VARCHAR(MAX)) AS [h],
            [h].[hid]
    FROM    [dbo].[Hierarchy] AS [h]
    JOIN    [cte] AS [c]
            ON [h].[ParentID] = [c].[ID]
)
UPDATE [h]
SET hid = [cte].[h]
FROM cte
JOIN dbo.[Hierarchy] AS [h]
    ON [h].[ID] = [cte].[ID];

现在重要的工作已经完成，你想要的结果几乎可以轻而易举地获得：

SELECT p.id, SUM([c].[Amount])
FROM dbo.[Hierarchy] AS [p]
JOIN [dbo].[Hierarchy] AS [c]
    ON c.[hid].IsDescendantOf(p.[hid]) = 1
GROUP BY [p].[ID];

- Ben Thul

---

谢谢，这种方法似乎与我迄今为止遇到的不同。一定会尝试。 - Sachin Trivedi

有一件事，HIERARCHYID 在 EF 中支持吗？我正在使用 EF 6，我认为它不支持。 - Sachin Trivedi

看起来似乎不能直接实现，但是你可以将上述任何/所有内容封装在存储过程中，并仅暴露ID和总和。而且EF可以很好地与存储过程一起使用。 - Ben Thul

好的，我已经完成了。非常感谢。我刚刚将这一列标记为[NotMapped]。 - Sachin Trivedi

有一件事我忘了提的是，你可以为层次结构数据类型建立索引。这很可能会在处理大型数据集时提高性能。 - Ben Thul

Answer 2

经过大量的研究和使用测试数据，我成功获得了从层次结构底部开始的累计总数。

该解决方案由两个步骤组成。

1. 创建一个标量值函数，用于确定一个categoryId是否是另一个categoryId的直接或间接子级。第一段代码展示了这一点。请注意，由于在SQL Server中处理层次结构时递归查询是最佳方法，因此使用了递归查询。

2. 编写按照您的要求为所有类别提供总计的运行总计查询。如果需要，可以按类别进行筛选。第二段代码提供了这个查询。

标量值函数，用于告知一个子类别是否是另一个类别的直接或间接子类别：

CREATE FUNCTION dbo.IsADirectOrIndirectChild(
               @childId int, @parentId int)
RETURNS int
AS
BEGIN

    DECLARE @isAChild int;
    WITH h(ParentId, ChildId)
    -- CTE name and columns
         AS (
         SELECT TOP 1 @parentId, @parentId
         FROM dbo.Category AS b
         UNION ALL
         SELECT b.ParentId, b.Id AS ChildId
         FROM h AS cte
              INNER JOIN
              Category AS b
              ON b.ParentId = cte.ChildId AND
                 cte.ChildId IS NOT NULL)
         SELECT @isAChild = ISNULL(ChildId, 0)
         FROM h
         WHERE ChildId = @childId AND
               ParentId <> ChildId
         OPTION(MAXRECURSION 32000);
    IF @isAChild > 0
    BEGIN
        SET @isAChild = 1;
    END;
    ELSE
    BEGIN
        SET @isAChild = 0;
    END;
    RETURN @isAChild;
END;
GO

从层次结构底部开始运行总计的查询。

SELECT c.Id AS CategoryId, c.Name AS CategoryName,
(
    SELECT SUM(ISNULL(b.amount, 0))
    FROM dbo.Budget AS b
    WHERE dbo.IsADirectOrIndirectChild( b.CategoryId, c.Id ) = 1 OR
          b.CategoryId = c.Id
) AS totalAmount,
(
    SELECT SUM(ISNULL(e.total, 0))
    FROM dbo.Expense AS e
    WHERE dbo.IsADirectOrIndirectChild( e.CategoryId, c.Id ) = 1 OR
          e.CategoryId = c.Id
) AS totalCost
FROM dbo.Category AS c;

- Sunil