如何使用递归实现数据的累加
--关于递归累计求和 -->>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 */