First, the question
For example, how does the data in table A and Table B become table C through SQL statements? Table A is the username phase, summarizing salary data in descending order of month, table B is the upper value of username corresponding sumsalary, and table C is the entry with the upper value of table B removed from table A, if the sumsalary value of the ledger entry is greater than the salary of table B, The salary is split to just summarize the number of the numbers equal to the B table.
SQL statement for Table A:
CREATE TABLE B ( UserName nvarchar ($), Salary int) CREATE TABLE user_salary (UserName nvarchar ($), Month nvarchar ( ), Salary int) Goinsert into user_salary (username,month,salary) VALUES (' AAA ', ' 2010/12 ', +) insert INTO user_salary ( Username,month,salary) VALUES (' AAA ', ' 2011/01 ', ') insert into user_salary (username,month,salary) VALUES (' AAA ', ' 2011/02 ', +) insert into user_salary (username,month,salary) VALUES (' BBB ', ' 2010/12 ', ' + ') insert INTO user_salary ( Username,month,salary) VALUES (' BBB ', ' 2011/01 ', 2500) insert into user_salary (username,month,salary) VALUES (' BBB ', ' 2011/02 ', 2500) insert into user_salary (username,month,salary) VALUES (' CCC ', ' 2013/12 ', ' and ') insert into user_salary ( Username,month,salary) VALUES (' CCC ', ' 2013/04 ', 4000) insert into user_salary (username,month,salary) VALUES (' CCC ', ' 2013/02 ', 3000)
Second, SQL
Sql server:
Select Username,month,salary2 salary,sumsalary2 sumsalary from (select K.*,case when b.flag1=0 then-1 else B.flag1 end FL Ag2,case when B.flag1=1 and k.flag1<>1 then k.salary-(k.sumsalary-k.maxsalary) Else K.salary end salary2,case when B . flag1=1 and K.flag1<>1 then k.maxsalary else K.sumsalary end SumSalary2 from (SELECT a.*,row_number () over (Orde R by username,month Desc) r from (SELECT a.*,case if Maxsalary>sumsalary then 1 while maxsalary=sumsalary then 0 else -1 End Flag1 from (select U.*, (select SUM (Salary) from User_salary where User_salary.month>=u.month and User_salary.use Rname = u.username) as Sumsalary, (select Salary from b where b.username = U.username) as Maxsalaryfrom user_salary u) a) A) K left JOIN (select A.*,row_number () over (order by username,month Desc) r from (select A.*,case when Maxsalary>sumsa Lary then 1 while maxsalary=sumsalary then 0 else-1 end Flag1 from (select U.*, (select SUM (Salary) from User_salary where User_salary.month>=u.Month and user_salary.username = u.username) as Sumsalary, (select Salary from b where b.username = U.username) as Maxsalar Yfrom user_salary u) a) B on K.R = b.r+1) A WHERE flag1=1 or FLAG1 = 0 or FLAG2 = 1
Iii. Summary
The main or the last row of the next row of records on the acquisition, that is, the previous written SQL Server Lead,lag implementation. If SQLServer2010 (like this version), there is a built-in Lead,lag, SQL is simple.
Personally think this kind of SQL as practiced hand is good, but with the application of complexity is a bit high, should be the application of business and table structure to be re-processed.
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
SQL Server complex Logic implementation