SQL Server层次列总和 [英] SQL Server Hierarchical Sum of column

问题描述

我按照图表进行数据库设计.

I have my database design as per the diagram.

• Category表是自引用父子关系
• Budget将具有所有类别和为每个类别定义的金额
• Expense表将包含已花费金额的类别的条目(请考虑此表中的Total列).

• Category table is self referencing parent child relationship
• Budget will have all the categories and amount define for each category
• Expense table will have entries for categories for which the amount has been spend (consider Total column from this table).

我想编写select语句，该语句将检索具有以下给定列的数据集:

I want to write select statement that will retrieve dataset with columns given below :

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，但无法产生任何有用的信息.感谢您的帮助. :)

I tried to use a CTE but was unable to produce anything useful. Thanks for your help in advance. :)

更新

我只是为了合并和简化数据，我使用下面的查询创建了一个视图.

I just to combine and simplify data I have created one view with the query below.

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

基本上应该会产生这样的结果.

Basically that should produce results like this.

推荐答案

这是一个有趣的问题.我将使用hierarchyid来解决它.首先，设置:

This is an interesting problem. And I'm going to solve it with a hierarchyid. First, the setup:

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];

接下来，用hiearchyid的适当值更新hid列.我将为此使用沼泽标准递归cte

Next, to update the hid column with a proper value for the hiearchyid. I'll use a bog standard recursive cte for that

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];

现在完成了繁重的工作，几乎可以轻松获得所需的结果:

Now that the heavy lifting is done, the results you want are almost trivially obtained:

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];

这篇关于SQL Server层次列总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！