Use SQL to accumulate data

For example, if you have such a requirement, a table named User_Salary contains the UserName of each user)

Calculate a result set: the sum of the salaries and accumulated salaries of each person per month, as shown in the following table.

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

Result

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

Of course, this result is well implemented in Excel, just a formula is needed:

Note that the G2 formula must remain unchanged in the first column, so it is $ F $2: F2, and then drag it down. However, this is only applicable when the data is fixed, if there are 100 employees, don't we need to drag them down. Of course, there may be other ways, so I don't know.

What can I do if I use SQL? The first thing that comes to mind is the cursor.

The cursor can indeed be implemented, and the write program can also be implemented, because their idea is the same: judge whether the name has been traversed. If it has been traversed, add up, if not, add it from 0. This is easy to understand, but it takes a lot of time to write. In fact, a SQL statement can be implemented, that is, subquery.

 
 

  
  
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 
 
 

As you know, the results of SQL queries are set-oriented, and such nested subqueries are the operations performed on each row before the entire result set is returned. Maybe this writing method is not easy to understand, so the following writing method should be easier to understand.

 
 

  
  
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 
 
 

In this way, the Union method is better to understand. In fact, this is to divide each row into a group of values smaller than its month, and then perform operations. If this is not clear, the following SQL statement makes the query more understandable:

 
 

  
  
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 
 
 

The result is obvious.

 

This is the recursion related to subqueries. Can this be said ?) Algorithm.

Subquery execution plan

Join execution plan

Though the two execution plans mentioned above are different, they are generally consistent, I don't quite understand the difference. (I don't know whether to sort first and then join first or sort first, but I personally think the second one is easier to understand .).

Source: http://www.cnblogs.com/mylhei/archive/2011/03/09/1978184.html

1. MySQL tips: optimize Limit with relevant parameters
2. Six data migration methods for SQL Server databases
3. MySQL database optimization) standalone MySQL database Optimization
4. MySQL database optimization) MySQL database high-availability architecture solution
5. MySQL Database Security Solution