用CTE如何实现下面的汇总
现有一张人员销售额统计表 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递归
[解决办法]
--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
*/
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.关系
