在树结构中获得多个总和

本文介绍了在树结构中获得多个总和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我在表employee（table，name，parentid）中有树结构，这个表可以嵌套.employees是与另一个表Sales（列，id，employeeid，quantity）的一对多关系。每位员工都有销售数量。我想计算每个员工与子员工一起的数量总和。我写了一些代码以便更清楚。 DECLARE @ Employees TABLE （ID INT ，名称 NVARCHAR （ 100 ），ParentID INT ）; DECLARE @ Sales TABLE （ID INT ，EmployeeID INT ，数量 INT ）; INSERT INTO @ Employees （ID，Name，ParentID） VALUES （ 1 ，N ' Employee1'， NULL ），（ 2 ，N ' Employee2' ， 1 ），（ 3 ，N ' Employee3'， 2 ），（ 4 ，N ' Employee4'， NULL ），（ 5 ，N ' Employee5'， 4 ），（ 6 ，N ' Employee6'， 5 ） INSERT INTO @ Sales （ID，EmployeeID，Quantity） VALUES （ 1 ， 1 ， 4 ），（ 2 ， 1 ， 2 ），（ 3 ， 2 ， 3 ） ，（ 4 ， 3 ， 2 ），（ 5 ， 3 ， 7 ），（ 6 ， 5 ， 8 ），（ 7 ， 5 ， 3 ），（ 8 ， 6 ， 2 ） 我加入了这个表，看起来像这样：加入表格 我尝试过： 这是我的查询 ; WITH cte AS （ SELECT e.ID，e.Name ，e.ParentID FROM @Employees e WHERE e.ParentID IS NULL UNION ALL SELECT e.ID，e.Name，e.ParentID FROM @Employees e INNER JOIN cte c ON c.ID = e.ParentID ） SELECT c.ID ，c.Name ，c.ParentID ，ISNULL（ SUM（s.Quantity），0）AS ParentSumSales ，ISNULL（LEAD（SUM（s.Quantity））OVER（ORDER BY c.ID），0）AS ChildSumSales FROM cte c LEFT JOIN @Sales s ON s.EmployeeID = c.ID GROUP BY c.ID，c.Name，c.Par entid 查询返回此结果，但不正确。 返回结果 返回结果应该像这个： ID名称ParentSumSales ChildSumSales --- --------- ------------ - ------------- 1 Employee1 6 12 2 Employee2 3 9 3 Employee3 9 0 4 Employee4 0 13 5 Employee5 11 2 6 Employee6 2 0 如何编写查询来获取此数据？解决方案 可能有更简洁的方法来执行此操作，但假设您使用的是MS SQL Server 2008或更高版本， hierarchyid数据类型 [ ^ ]提供了一个解决方案： WITH cteRawEmployees As （ SELECT E.ID， E.ParentID， E.Name， IsNull（（ SELECT Sum（S.Quantity） FROM @ Sales As S WHERE S.EmployeeID = E.ID ）， 0 ）作为销售额 FROM @ Employees As E ）， cteEmployeeTree 作为 （ SELECT R.ID， R.Name， R.Sales， CAST（' /' + CAST（R.ID 作为 varchar （ 10 ））+ ' /' 作为 varchar （max）） As NodePath FROM cteRawEmployees As R WHERE R.ParentID 空 UNION ALL SELECT R.ID， R.Name， R.Sales， CAST（E.NodePath + CAST（R.ID As varchar （ 10 ））+ ' /' 作为 varchar （max）） FROM cteRawEmployees 作为 R INNER JOIN cteEmployeeTree 作为 E ON E.ID = R.ParentID ）， cteEmployees As （ SELECT ID，名称，销售额， CAST（ NodePath As hierarchyid ） As NodePath FROM cteEmployeeTree ） SELECT E.ID， E.Name， E.Sales As ParentSumSales， IsNull（（ SELECT Sum（Sales） FROM cteEmployees As E2 WHERE E2.NodePath.IsDescendantOf（E.NodePath）= 1 和 E2.ID！= E.ID - NB：IsDescendantOf将节点视为自己的后代。 ）， 0 ） As ChildSumSales FROM cteEmployees 作为 E ORDER BY E.NodePath ; 输出： ID名称ParentSumSales ChildSumSales - --------- -------------- ---------- --- 1员工1 6 12 2员工2 3 9 3员工3 9 0 4员工4 0 13 5员工5 11 2 6员工6 2 0 I have tree structure in table employees (id,name,parentid) and this table can be nested.employees is one-to-many relation to another table Sales with columns(id, employeeid, quantity). Each employee has Sales Quantity. I want to calculate sum of quantity for each employee along side with child employees. I wrote some code to be more clearly.DECLARE @Employees TABLE(ID INT, Name NVARCHAR(100), ParentID INT);DECLARE @Sales TABLE(ID INT, EmployeeID INT, Quantity INT);INSERT INTO @Employees(ID, Name, ParentID)VALUES(1,N'Employee1', NULL),(2,N'Employee2', 1),(3,N'Employee3', 2),(4,N'Employee4', NULL),(5,N'Employee5', 4),(6, N'Employee6', 5)INSERT INTO @Sales(ID, EmployeeID, Quantity)VALUES(1,1,4),(2,1,2),(3,2,3),(4,3,2),(5,3,7),(6,5,8),(7,5,3),(8,6,2)I joined this Tables and looks likes this: Joined TablesWhat I have tried:Here is my query;WITH cteAS( SELECT e.ID, e.Name, e.ParentID FROM @Employees e WHERE e.ParentID IS NULL UNION ALL SELECT e.ID, e.Name, e.ParentID FROM @Employees e INNER JOIN cte c ON c.ID = e.ParentID)SELECT c.ID ,c.Name ,c.ParentID ,ISNULL(SUM(s.Quantity), 0) AS ParentSumSales ,ISNULL(LEAD(SUM(s.Quantity)) OVER(ORDER BY c.ID), 0) AS ChildSumSalesFROM cte c LEFT JOIN @Sales s ON s.EmployeeID = c.IDGROUP BY c.ID, c.Name, c.ParentIDquery returns this result, but it is not correct. Returned resultThe return result should be like this:ID Name ParentSumSales ChildSumSales--- --------- ------------- -------------1 Employee1 6 122 Employee2 3 93 Employee3 9 04 Employee4 0 135 Employee5 11 26 Employee6 2 0How can i write query to get this data? 解决方案 There might be a cleaner way to do this, but assuming you're using MS SQL Server 2008 or later, the hierarchyid data type[^] provides a solution:WITH cteRawEmployees As( SELECT E.ID, E.ParentID, E.Name, IsNull(( SELECT Sum(S.Quantity) FROM @Sales As S WHERE S.EmployeeID = E.ID ), 0) As Sales FROM @Employees As E),cteEmployeeTree As( SELECT R.ID, R.Name, R.Sales, CAST('/' + CAST(R.ID As varchar(10)) + '/' As varchar(max)) As NodePath FROM cteRawEmployees As R WHERE R.ParentID Is Null UNION ALL SELECT R.ID, R.Name, R.Sales, CAST(E.NodePath + CAST(R.ID As varchar(10)) + '/' As varchar(max)) FROM cteRawEmployees As R INNER JOIN cteEmployeeTree As E ON E.ID = R.ParentID),cteEmployees As( SELECT ID, Name, Sales, CAST(NodePath As hierarchyid) As NodePath FROM cteEmployeeTree)SELECT E.ID, E.Name, E.Sales As ParentSumSales, IsNull(( SELECT Sum(Sales) FROM cteEmployees As E2 WHERE E2.NodePath.IsDescendantOf(E.NodePath) = 1 And E2.ID != E.ID -- NB: IsDescendantOf considers a node to be its own descendant. ), 0) As ChildSumSalesFROM cteEmployees As EORDER BY E.NodePath;Output:ID Name ParentSumSales ChildSumSales-- --------- -------------- -------------1 Employee1 6 122 Employee2 3 93 Employee3 9 04 Employee4 0 135 Employee5 11 26 Employee6 2 0 这篇关于在树结构中获得多个总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！