巧用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中十分好實現，只需要一個公式就好：

注意G2的公式一定要保持第一個列不動所以就是$F$2:F2，然後向下拖一下就可以，但是，這隻適用於資料固定的情況下，試想，如果有100個員工的資料，豈不是要拖100下。當然也可能有其他辦法，這個我就不知道了。

下面，如果用sql實現能有什麼辦法呢？首先想到的是遊標。

對遊標的確可以實現，寫程式也可以實現，因為他們的思想是一樣的：判斷一下名字是不是已經遍曆過了，如果遍曆過了，就累加一下，如果沒有就從0加起。這樣很好理解，但是寫的很費時，其實一條sql語句就可以實現的，那就是子查詢。

 

  
create table User_Salary (UserName nvarchar(200), Month nvarchar(20), Salary int)     
  
      
  
go     
  
      
  
insert into User_Salary (UserName,Month,Salary ) values('AAA','2010/12',1000)     
  
insert into User_Salary (UserName,Month,Salary ) values('AAA','2011/01',2000)     
  
insert into User_Salary (UserName,Month,Salary ) values('AAA','2011/02',3000)     
  
insert into User_Salary (UserName,Month,Salary ) values('BBB','2010/12',2000)     
  
insert into User_Salary (UserName,Month,Salary ) values('BBB','2011/01',2500)    
  
insert into User_Salary (UserName,Month,Salary ) values('BBB','2011/02',2500)    
  
     
  
go    
  
     
  
select UserName,Month,Salary,    
  
    Cummulation=(    
  
        select SUM(Salary)     
  
        from     
  
            User_Salary i    
  
        where     
  
            i.UserName=o.UserName and i.Month<=o.Month    
  
        )    
  
from User_Salary o    
  
order by 1,2    
  
     
  
go    
  
     
  
drop table User_Salary 
 

大家知道SQL查詢的結果是面向集合，而這種嵌套的子查詢恰恰就是在整個結果集返回之前做的對於每一行的運算。也許這樣的寫法不是很容易理解，那麼下面這個寫法應該容易理解多了。

 

  
create table User_Salary (UserName nvarchar(200), Month nvarchar(20), Salary int)    
  
      
  
go    
  
   
  
insert into User_Salary (UserName,Month,Salary ) values('AAA','2010/12',1000)    
  
insert into User_Salary (UserName,Month,Salary ) values('AAA','2011/01',2000)    
  
insert into User_Salary (UserName,Month,Salary ) values('AAA','2011/02',3000)    
  
insert into User_Salary (UserName,Month,Salary ) values('BBB','2010/12',2000)    
  
insert into User_Salary (UserName,Month,Salary ) values('BBB','2011/01',2500)   
  
insert into User_Salary (UserName,Month,Salary ) values('BBB','2011/02',2500)   
  
    
  
go    
  
     
  
select    
  
     A.UserName,A.Month,MAX(A.Salary) as Salary,SUM (B.Salary) as Accumulation    
  
from   
  
     User_Salary A inner join User_Salary B   
  
 ON    
  
     A.UserName = B.UserName   
  
 where    
  
     B.Month <= A.Month   
  
 group by   
  
     A.UserName,A.Month   
  
order by       
  
     A.UserName,A.Month   
  
     
  
go   
  
     
  
drop table User_Salary 
 

這樣用聯合的方式就好理解一些，其實這樣就是把每一行對應比他月份小的值分成一組，然後進行運算，如果這樣不明白，那麼下面的sql會使這個查詢更加好理解：

 

  
select     
  
   A.*,B.*    
  
from     
  
   User_Salary A inner join User_Salary B    
  
ON     
  
   A.UserName = B.UserName     
  
where     
  
   B.Month <= A.Month     
  
order by 1,2 
 

這樣的結果就顯而易見了

 

這就是子查詢相關的遞迴可以這麼說？）演算法。

子查詢執行計畫

join執行計畫

通過上述2個執行計畫,雖然執行計畫不同,但大體一致,這其中的區別我就不太明白了(不知道是先排序再join好 還是先join在排序好,但是我個人覺得第二種比較容易理解.).

原文出處：http://www.cnblogs.com/mylhei/archive/2011/03/09/1978184.html

1. MySQL技巧:結合相關參數 做好Limit最佳化
2. SQL Server資料庫六種資料移動方法
3. MySQL資料庫的最佳化上）單機MySQL資料庫的最佳化
4. MySQL資料庫的最佳化下）MySQL資料庫的高可用架構方案
5. MySQL資料庫安全解決方案