巧用sql进行数据累加

比如有这样一个需求,一张表格(User_Salary)包含每个人(UserName)每个月份(Month)发的薪水(Salary)

求这样一个结果集:每个人每月所发薪水及累计所得薪水和,如下表

UserName Month Salary
AAA 2010/12 1000
AAA 2011/01 2000
AAA 2011/02 3000
BBB 2010/12 2000
BBB 2011/01 2500
BBB 2011/02 2500

结果

UserName Month Salary Cumulation
AAA 2010/12 1000 1000
AAA 2011/01 2000 3000
AAA 2011/02 3000 6000
BBB 2010/12 2000 2000
BBB 2011/01 2500 4500
BBB 2011/02 2500 7000

当然这个结果在Excel中十分好实现,只需要一个公式就好:

image

注意G2的公式一定要保持第一个列不动所以就是$F$2:F2,然后向下拖一下就可以,但是,这只适用于数据固定的情况下,试想,如果有100个员工的数据,岂不是要拖100下。当然也可能有其他办法,这个我就不知道了。

下面,如果用sql实现能有什么办法呢?首先想到的是游标。

对游标的确可以实现,写程序也可以实现,因为他们的思想是一样的:判断一下名字是不是已经遍历过了,如果遍历过了,就累加一下,如果没有就从0加起。这样很好理解,但是写的很费时,其实一条sql语句就可以实现的,那就是子查询。

   1: create table User_Salary (UserName nvarchar(200), Month nvarchar(20), Salary int)
   2:  
   3: go
   4:  
   5: insert into User_Salary (UserName,Month,Salary ) values('AAA','2010/12',1000)
   6: insert into User_Salary (UserName,Month,Salary ) values('AAA','2011/01',2000)
   7: insert into User_Salary (UserName,Month,Salary ) values('AAA','2011/02',3000)
   8: insert into User_Salary (UserName,Month,Salary ) values('BBB','2010/12',2000)
   9: insert into User_Salary (UserName,Month,Salary ) values('BBB','2011/01',2500)
  10: insert into User_Salary (UserName,Month,Salary ) values('BBB','2011/02',2500)
  11:  
  12: go
  13:  
  14: select UserName,Month,Salary,
  15:     Cummulation=(
  16:         select SUM(Salary) 
  17:         from 
  18:             User_Salary i
  19:         where 
  20:             i.UserName=o.UserName and i.Month<=o.Month
  21:         )
  22: from User_Salary o
  23: order by 1,2
  24:  
  25: go
  26:  
  27: drop table User_Salary

大家知道SQL查询的结果是面向集合,而这种嵌套的子查询恰恰就是在整个结果集返回之前做的对于每一行的运算。也许这样的写法不是很容易理解,那么下面这个写法应该容易理解多了。

   1: create table User_Salary (UserName nvarchar(200), Month nvarchar(20), Salary int)
   2:  
   3: go
   4:  
   5: insert into User_Salary (UserName,Month,Salary ) values('AAA','2010/12',1000)
   6: insert into User_Salary (UserName,Month,Salary ) values('AAA','2011/01',2000)
   7: insert into User_Salary (UserName,Month,Salary ) values('AAA','2011/02',3000)
   8: insert into User_Salary (UserName,Month,Salary ) values('BBB','2010/12',2000)
   9: insert into User_Salary (UserName,Month,Salary ) values('BBB','2011/01',2500)
  10: insert into User_Salary (UserName,Month,Salary ) values('BBB','2011/02',2500)
  11:  
  12: go
  13:  
  14: select 
  15:     A.UserName,A.Month,MAX(A.Salary) as Salary,SUM (B.Salary) as Accumulation 
  16: from 
  17:     User_Salary A inner join User_Salary B
  18: ON 
  19:     A.UserName = B.UserName 
  20: where 
  21:     B.Month <= A.Month 
  22: group by
  23:     A.UserName,A.Month
  24: order by    
  25:     A.UserName,A.Month
  26:  
  27: go
  28:  
  29: drop table User_Salary

这样用联合的方式就好理解一些,其实这样就是把每一行对应比他月份小的值分成一组,然后进行运算,如果这样不明白,那么下面的sql会使这个查询更加好理解:

   1: select 
   2:     A.*,B.*
   3: from 
   4:     User_Salary A inner join User_Salary B
   5: ON 
   6:     A.UserName = B.UserName 
   7: where 
   8:     B.Month <= A.Month 
   9: order by 1,2

这样的结果就显而易见了

image

这就是子查询相关的递归(可以这么说?)算法。

image

子查询执行计划

image

join执行计划

通过上述2个执行计划,虽然执行计划不同,但大体一致,这其中的区别我就不太明白了(不知道是先排序再join好 还是先join在排序好,但是我个人觉得第二种比较容易理解.).

源代码
posted @ 2011-03-09 13:03  mylhei  阅读(11078)  评论(9编辑  收藏