如何使用递归实现数据的累加

--关于递归累计求和
-->>TravyLee生成测试数据
if OBJECT_ID('test')is not null
drop table test
go
create table test(
id int identity(1,1),
deptid char(3),
empid char(4),
salary int
)
go
insert test(deptid,empid,salary)
select '101','1001',3500 union all
select '101','1002',2200 union all
select '102','1003',1900 union all
select '102','1004',5600 union all
select '102','1005',8000 union all
select '101','1006',2400 union all
select '101','1007',2300 union all
select '103','1008',3200 union all
select '103','1009',7800 union all
select '104','1010',4500 union all
select '101','1011',6500 union all
select '104','1012',3500 union all
select '104','1013',1900 union all
select '103','1014',2700 union all
select '102','1015',3100 union all
select '104','1016',2600 
go
--problem 1：求出所有员工的工资的累计（从工资的最高到最低累计）
;with t
as(
select 
px=ROW_NUMBER()over(order by salary desc),
deptid,empid,salary
from test
),
m as(
select px,deptid,empid,salary,salary as total 
from t where px=1
union all
select 
a.px,a.deptid,a.empid,a.salary,b.total+a.salary
from t a
join m b on a.px=b.px+1 
)
select deptid,empid,salary,total from m
go
/*
deptid empid salary total
------------------------------
1	102	1005	8000	8000
2	103	1009	7800	15800
3	101	1011	6500	22300
4	102	1004	5600	27900
5	104	1010	4500	32400
6	101	1001	3500	35900
7	104	1012	3500	39400
8	103	1008	3200	42600
9	102	1015	3100	45700
10	103	1014	2700	48400
11	104	1016	2600	51000
12	101	1006	2400	53400
13	101	1007	2300	55700
14	101	1002	2200	57900
15	102	1003	1900	59800
16	104	1013	1900	61700
*/

--problem 2:分部门统计，并求出各部门在总工资中所占的百分比

;with t
as(
select 
px=ROW_NUMBER()over(partition by deptid order by salary desc),
deptid,empid,salary
from test
),
m as(
select px,deptid,empid,salary,salary as total 
from t where px=1
union all
select 
a.px,a.deptid,a.empid,a.salary,b.total+a.salary
from t a
join m b on a.px=b.px+1 and a.deptid=b.deptid
)
select 
    deptid,empid,salary,total 
from 
    m 
order by 
    deptid,px

/*
deptid empid salary total
------------------------------
101	1011	6500	6500
101	1001	3500	10000
101	1006	2400	12400
101	1007	2300	14700
101	1002	2200	16900
102	1005	8000	8000
102	1004	5600	13600
102	1015	3100	16700
102	1003	1900	18600
103	1009	7800	7800
103	1008	3200	11000
103	1014	2700	13700
104	1010	4500	4500
104	1012	3500	8000
104	1016	2600	10600
104	1013	1900	12500
*/