SQL使用递归实现数据的累加.pdfVIP

SQL使⽤递归实现数据的累加

--关于递归累计求和

ifOBJECT_ID(test)isnotnull

droptabletest

go

createtabletest(

idintidentity(1,1),

deptidchar(3),

empidchar(4),

salaryint

)

go

inserttest(deptid,empid,salary)

select101,1001,3500unionall

select101,1002,2200unionall

select102,1003,1900unionall

select102,1004,5600unionall

select102,1005,8000unionall

select101,1006,2400unionall

select101,1007,2300unionall

select103,1008,3200unionall

select103,1009,7800unionall

select104,1010,4500unionall

select101,1011,6500unionall

select104,1012,3500unionall

select104,1013,1900unionall

select103,1014,2700unionall

select102,1015,3100unionall

select104,1016,2600

go

--problem1：求出所有员⼯的⼯资的累计（从⼯资的最⾼到最低累计

;witht

as(

select

px=ROW_NUMBER()over(orderbysalarydesc),

deptid,empid,salary

fromtest

),

mas(

selectpx,deptid,empid,salary,salaryastotal

fromtwherepx=1

unionall

select

a.px,a.deptid,a.empid,a.salary,b.total+a.salary

fromta

joinmbona.px=b.px+1

)

selectdeptid,empid,salary,totalfromm

go

/*

deptidempidsalarytotal

1102100580008000

21031009780015800

31011011650022300

41021004560027900

51041010450032400

61011001350035900

71041012350039400

81031008320042600

91021015310045700

101031014270048400

111041016260051000

111041016260051000

121011006240053400

131011007230055700

141011002220057900

151021003190059800

161041013190061700

*/

--problem2:分部门统计，并求出各部门在总⼯资中所占的百分⽐

;witht

as(

select

px=ROW_NUMBER()over(partitionbydeptidorderbysalarydesc),

deptid,empid,salary

f