在树结构中获得多个总和 [英] Get multiple sums in tree structure
本文介绍了在树结构中获得多个总和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在表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 < span class =code-digit> 12
2 Employee2 3 < span class =code-digit> 9
3 Employee3 9 < span class =code-digit> 0
4 Employee4 0 < span class =code-digit> 13
5 Employee5 11 < span class =code-digit> 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 < span class =code-keyword> 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 Tables
What I have tried:
Here is my query
;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.ParentID
query returns this result, but it is not correct. Returned result
The return result should be like this:
ID Name 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
How 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 ChildSumSales FROM cteEmployees As E ORDER BY E.NodePath ;
Output:
ID Name 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
这篇关于在树结构中获得多个总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
![返回结果](\"http://i.stack.imgur.com/wWyHC.jpg\"){kind=link}
