使用获取下行记录的总数
使用获取下行记录的总数
提问于 2017-03-14 02:14:36
如何使用SQL将所有下线求和到它们的即时上线。
- 所有的员工销售都是主管的销售。
- 所有主管销售都是经理的销售。
- 所有的经理销售都是负责人的销售。
当前数据：
ID Name Role Level Reportingto Sales
---------------------------------------------------------------
1 Joe Head 1 NULL 0
2 Smith Manager 2 1 0
3 Mike Supervisor 3 2 0
4 Mitch Staff 4 3 10
5 Jen Staff 4 3 20
6 Ian Manager 2 1 0
7 Jess Supervisor 3 6 0
8 Rocky Staff 4 7 5
9 Jessica Supervisor 3 6 0
10 Rolly Staff 4 9 3理想输出
ID Name Role Level Reportingto Sales
---------------------------------------------------------------
1 Joe Head 1 NULL 38
2 Smith Manager 2 1 30
3 Mike Supervisor 3 2 30
4 Mitch Staff 4 3 10
5 Jen Staff 4 3 20
6 Ian Manager 2 1 8
7 Jess Supervisor 3 6 5
8 Rocky Staff 4 7 5
9 Jessica Supervisor 3 6 3
10 Rolly Staff 4 9 3我在使用Server 2016
回答 4
Stack Overflow用户
回答已采纳
发布于 2017-03-14 03:32:43
Declare @YourTable table (ID int,Name varchar(25),Role varchar(25),Level int,Reportingto int,Sales int)
Insert into @YourTable values
(1 ,'Joe' ,'Head' ,1 ,NULL ,0),
(2 ,'Smith' ,'Manager' ,2 ,1 ,0),
(3 ,'Mike' ,'Supervisor' ,3 ,2 ,0),
(4 ,'Mitch' ,'Staff' ,4 ,3 ,10),
(5 ,'Jen' ,'Staff' ,4 ,3 ,20),
(6 ,'Ian' ,'Manager' ,2 ,1 ,0),
(7 ,'Jess' ,'Supervisor' ,3 ,6 ,0),
(8 ,'Rocky' ,'Staff' ,4 ,7 ,5),
(9 ,'Jessica' ,'Supervisor' ,3 ,6 ,0),
(10 ,'Rolly' ,'Staff' ,4 ,9 ,3)
Declare @Top int = null --<< Sets top of Hier Try 6
;with cteP as (
Select Seq = cast(10000+Row_Number() over (Order by Name) as varchar(500))
,ID
,Reportingto
,Lvl=1
,Name
,Role
,Sales
From @YourTable
Where IsNull(@Top,-1) = case when @Top is null then isnull(Reportingto ,-1) else ID end
Union All
Select Seq = cast(concat(p.Seq,'.',10000+Row_Number() over (Order by r.Name)) as varchar(500))
,r.ID
,r.Reportingto
,p.Lvl+1
,r.Name
,r.Role
,r.Sales
From @YourTable r
Join cteP p on r.Reportingto = p.ID)
Select A.ID
,A.Name
,A.Role
,A.Lvl
,A.Reportingto
,Sales = (Select sum(Sales) from cteP where Seq Like A.Seq+'%')
From cteP A
Order By A.Seq返回
Stack Overflow用户
发布于 2017-03-14 02:51:35
您可以使用outer apply。
select t1.id,t1.name,t1.role,t1.level,t1.reportingto,coalesce(t2.sales,t1.sales) as val
from t t1
outer apply (select sum(sales) as sales
from t t2
where t1.id<=t2.id and t1.level<t2.level) t2Stack Overflow用户
发布于 2017-03-14 02:53:40
也许有一种更简单的方法可以做到这一点，但在我看来，你似乎需要从“员工”开始，然后重新开始：
;with cte as (SELECT [ID], [Name], [Role], [Level], [Reportingto], [Sales], Sales as Sum_Sales
FROM #Table1
WHERE [Role] = 'Staff'
UNION ALL
SELECT a.[ID], a.[Name], a.[Role], a.[Level], a.[Reportingto], a.[Sales], a.Sales + b.Sum_Sales AS Sum_Sales
FROM #Table1 a
JOIN cte b
ON b.Reportingto = a.ID
)
SELECT [ID], [Name], [Role], [Level], [Reportingto],SUM(Sum_Sales) as Sales
FROM cte
GROUP BY [ID], [Name], [Role], [Level], [Reportingto]页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42776652
复制相关文章
相似问题
腾讯云开发者
