计算树中值的总和(递归查询) [英] Calculate sum of values in tree(recursive query)
问题描述
我在employees (id,name,parentid) 表中有树结构,这个表可以嵌套.employees 是与另一个表Sales 的一对多关系,列是(id,employidid,quantity).每个员工都有销售数量.我想与儿童员工一起计算每个员工的数量总和.为了更清楚,我写了一些代码.
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:
这是我的查询
;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 this is not correct, I want to get data 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
推荐答案
您尝试使用 LEAD
是行不通的,因为它不会对所有先前级别求和,而且 ID 必须是连续的.
Your attempt using LEAD
will not work as it's not summing all previous levels and it IDs must be sequential.
首先分解每个员工的完整层级,以便每个员工在每个层级中包含一次:
Explode the full hierachy for each employee first such that each employe is included once per hierarchy level:
;WITH cte
AS
(
SELECT e.ID, e.Name, e.ID as sub_ID
FROM @Employees e
-- no WHERE-condition to get all employees
UNION ALL
SELECT
c.ID, c.Name -- keep the initial employee
,e.ID as sub_ID
FROM @Employees e
INNER JOIN cte c ON c.sub_ID = e.ParentID
)
SELECT
c.ID
,c.Name
-- parent level
,sum(case when c.id = s.EmployeeID then s.Quantity else 0 end) AS ParentSumSales
-- child level
,sum(case when c.id <> s.EmployeeID then s.Quantity else 0 end) AS ChildSumSales
FROM cte c
LEFT JOIN @Sales as s
ON s.EmployeeID = c.sub_ID
group by c.Name, c.id
这篇关于计算树中值的总和(递归查询)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!