SQL Server CTE用于在层次结构的每个级别汇总成本

Question

我无法想出如何编写一个CTE，从事务表中滚动成本到自连接表，以便在层次结构的每个级别上给我总成本。我已经准备了一个非常简单的示例来说明这个问题。以下是DDL和插入脚本，以便您能够复制该问题，如果您愿意帮助我：

CREATE TABLE [Items](
    [ItemId] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NULL,
    [ItemName] [varchar](100) NOT NULL,
 CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED 
(
    [ItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Transactions](
    [TransactionId] [int] IDENTITY(1,1) NOT NULL,
    [ItemId] [int] NOT NULL,
    [Amount] [money] NOT NULL,
 CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED 
(
    [TransactionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [Items] ON 
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (1, NULL, N'Warehouse')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (3, 1, N'Bin 1')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (4, 1, N'Bin 2')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (5, 3, N'Item 1.1')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (6, 3, N'Item 1.2')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (7, 4, N'Item 2.1')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (8, 4, N'Item 2.2')
GO
SET IDENTITY_INSERT [Items] OFF
GO
SET IDENTITY_INSERT [Transactions] ON 
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (1, 5, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (2, 5, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (3, 6, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (4, 6, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (5, 4, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (6, 7, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (7, 7, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (8, 8, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (9, 8, 10.0000)
GO
SET IDENTITY_INSERT [Transactions] OFF
GO
ALTER TABLE [Items]  WITH CHECK ADD  CONSTRAINT [FK_Items_Items] FOREIGN KEY([ParentId])
REFERENCES [Items] ([ItemId])
GO
ALTER TABLE [Items] CHECK CONSTRAINT [FK_Items_Items]
GO
ALTER TABLE [Transactions]  WITH CHECK ADD  CONSTRAINT [FK_Transactions_Items] FOREIGN KEY([ItemId])
REFERENCES [Items] ([ItemId])
GO
ALTER TABLE [Transactions] CHECK CONSTRAINT [FK_Transactions_Items]
GO

这是我一直在努力完成的CTE：

With cteAggregateCost
as  
(  
    select i.itemId, i.ParentId, t.Amount
    from Items i join Transactions t on i.ItemId = t.ItemId
union all
    select i.itemId, i.ParentId, t.Amount
    from Items i join cteAggregateCost c on i.ItemId = c.ParentId
    join Transactions t on i.ItemId = t.ItemId
)
select i.ParentId, i.ItemId, i.ItemName, sum(Amount) As AggregateCost
from Items i left join cteAggregateCost c on i.ItemId = c.ItemId
group by i.ParentId, i.ItemId, i.ItemName

这是我得到的结果： 而我希望得到的结果是： 可以看到，所有线都有效，除了前两条线，它们没有容器的成本，只有其包含的物品。

非常感谢您所提供的任何指导！

- Karin

---

这些值从哪里来的？90和40？对于数据组合(itemid =1 and parentid = null)和(itemid=3, parentid = 1)，在您提供的示例中，Transaction表中没有这些记录的匹配值。 - Rigerta

@Rigerta 对 ItemId 的子项进行求和。ItemId 1 是 ItemId 3 和 4 的父项，它们的总数分别为40和50，相加得到90。ItemId 3 的总数为40，来自于每个子项5和6的20的总和。ItemId 4 是50，因为需要包括子项（ItemId 7和8）以及ItemId 4本身的单个交易。 - iamdave

Answer 1

您可以像这样使用递归CTE。

;WITH temp AS 
(
   SELECT i.*, sum(isnull(t.Amount,0)) AS Amount 
   FROM @Items i
   LEFT JOIN @Transactions t ON t.ItemId = i.ItemId
   GROUP BY i.ItemId, i.ParentId, i.ItemName
)
,cteAggregateCost
as  
(  
   select i.ItemId, i.ItemId AS RootId, i.Amount  
   from temp i     
union all
   select i.ItemId, c.RootId, i.Amount 
   from cteAggregateCost c
   INNER JOIN temp i ON i.ParentId = c.ItemId
)
select i.*, ca.TotalAmount
from  @Items i 
CROSS APPLY 
(
   SELECT Sum(cac.Amount) AS TotalAmount
   FROM cteAggregateCost cac WHERE i.ItemId = cac.RootId
) ca
OPTION (MAXRECURSION 0)

演示链接：http://rextester.com/XMK96314

- TriV

Answer 2

谢谢您，TriV。您的回答非常棒！不过，在SQL Server论坛上，我找到了一个更简单的解决方案，由Xi Jin发布。以下是他的解决方案：

    With cteAggregateCost
    as  
    (  
        select  i.itemId as rootid,i.itemid, i.ParentId
        from Items i 
        union all
        select rootid, i.itemId, i.ParentId
        from Items i join cteAggregateCost c on i.ParentId = c.ItemId
    )

    select a.parentid, a.ItemId , a.ItemName , sum(t.Amount) As AggregateCost
    from items a
    left join cteAggregateCost i on a.itemid = i.rootid 
    left join Transactions t on i.ItemId = t.ItemId
    group by a.parentid, a.ItemId, a.ItemName

两种解决方案都能够在使用了多层次关系的巨大数据集测试时得出我所期望的正确结果。对于我来说，林曦的回答更易于理解。我无法想出如何添加rootID技术以保留那些没有自己成本但只有来自子项成本的项目的值。请注意保留HTML标签。

- Karin

---

使用RootId的目的是获取您表中每个节点的所有子节点。这是通过递归CTE完成的，仅剩的一件事是将“左连接”到“transactions”以确保左表中的所有项目都存在。您的第一个查询不正确，因为内部连接到“transactions”表。而“递归CTE”不允许使用“左连接”，因此必须将其移动到外部。 - TriV

谢谢 TriV。现在我看到最终答案，一切都变得非常清晰明了。我非常感谢你的解释。 - Karin