用CTE如何实现下面的汇总

Question

现有一张人员销售额统计表 Sales雇员ID　　姓名　　层次　　上级ID　　销售额
1　　　　 王伟　　1 　　　0　　　　 900
2　　　　 张三　　2 　　　1　　　　 175
3　　　　 李四　　2 　　　1　　　　 400
4　　　　 王五　　2 　　　1　　　　 185
5　　　　 赵六　　3 　　　2　　　　 190
6　　　　 小七　　3 　　　2　　　　 110
7　　　　 小八　　3 　　　3　　　　 550怎么用CTE递归实现每个人总的销售额，要包含此人所有下级人员的销售额？下面为表以及数据：
create table Sales
(
雇员ID int NULL,
姓名 nchar(10) NULL,
层次 int NULL,
上级ID int NULL,
销售额 money NULL
)insert into Sales values(1,'王伟',1,0,900)
insert into Sales values(2,'张三',2,1,175) 
insert into Sales values(3,'李四',2,1,400) 
insert into Sales values(4,'王五',2,1,185) 
insert into Sales values(5,'赵六',3,2,190) 
insert into Sales values(6,'小七',3,2,110) 
insert into Sales values(7,'小八',3,3,550) 
CTE递归

Answer 1

--create table Sales
--(
--雇员ID int NULL,
--姓名 nchar(10) NULL,
--层次 int NULL,
--上级ID int NULL,
--销售额 money NULL
--)--insert into Sales values(1,'王伟',1,0,900)
--insert into Sales values(2,'张三',2,1,175) 
--insert into Sales values(3,'李四',2,1,400) 
--insert into Sales values(4,'王五',2,1,185) 
--insert into Sales values(5,'赵六',3,2,190) 
--insert into Sales values(6,'小七',3,2,110) 
--insert into Sales values(7,'小八',3,3,550) ;WITH cte AS (
SELECT * 
FROM sales
WHERE 层次=(SELECT MAX(上级id) FROM sales)
UNION ALL 
SELECT b.雇员ID,b.姓名,a.层次,b.上级ID,a.销售额+b.销售额 AS 销售额
FROM sales b INNER JOIN cte a ON a.上级ID=b.雇员ID) 
SELECT 雇员ID,姓名,层次,上级ID,SUM(销售额)销售额
 FROM cte
 GROUP BY 雇员ID,姓名,层次,上级ID
 /*
 雇员ID        姓名         层次          上级ID        销售额
----------- ---------- ----------- ----------- ---------------------
1           王伟         3           0           4300.00
2           张三         3           1           650.00
3           李四         3           1           950.00
5           赵六         3           2           190.00
6           小七         3           2           110.00
7           小八         3           3           550.00
 */

Answer 2

with CTE
as
(
select 雇员ID,销售额,关系=cast(雇员ID as nvarchar)
from Sales
--where 上级ID=0
union all
select a.雇员ID,a.销售额,关系=cast(关系+'>'+cast(a.雇员ID as nvarchar) as nvarchar)
from Sales a join CTE b on a.上级ID=b.雇员ID
)
select 雇员ID=substring(a.关系,1,1),姓名=min(b.姓名),层次=min(b.层次),上级ID=min(b.上级ID),销售额=min(b.销售额),总销售额=sum(a.销售额) 
from CTE a join Sales b on substring(a.关系,1,1)=b.雇员ID
group by substring(a.关系,1,1)
/*
雇员ID 姓名 层次 上级ID 销售额 总销售额
1 王伟         1 0 900.00 2510.00
2 张三         2 1 175.00 475.00
3 李四         2 1 400.00 950.00
4 王五         2 1 185.00 185.00
5 赵六         3 2 190.00 190.00
6 小七         3 2 110.00 110.00
7 小八         3 3 550.00 550.00
*/

Answer 3

回家路上我又想想这样应该比自己刚才那样好吧，就是家里没有SQL
with CTE as(
     select 雇员ID,销售额,关系=雇员ID
     from Sales
     union all
     select a.雇员ID,a.销售额,b.关系
     from Sales a join CTE b on a.上级ID=b.雇员ID
) 
select 雇员ID=a.关系,姓名=min(b.姓名),层次=min(b.层次),上级ID=min(b.上级ID),销售额=min(b.销售额),总销售额=sum(a.销售额)  
from CTE a join Sales b on a.关系=b.雇员ID 
group by a.关系

Answer 4

非常给力，这个结果是正确的，正研究中，CTE递归真心不是很会用，总是感觉一知半解。