SQL Server complex SQL logic implementation, SQL Server SQL Logic

SQL Server complex SQL logic implementation, SQL Server SQL Logic

I. Problems

For example, if you already have data in tables a and B, how can you convert it to Table c through SQL statements? Table a is a table that has the same UserName and summarizes Salary data in descending order of Month. Table B is the upper limit of SumSalary corresponding to UserName. Table c is the entry that extracts the upper limit of Table B from Table, if the SumSalary value of the entry is greater than the Salary value of Table B, the sum of the values is equal to the Salary number of Table B.

SQL statement of Table:

create table b(  UserName nvarchar(200),Salary int)create table User_Salary (UserName nvarchar(200), Month nvarchar(20), Salary int)goinsert 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)insert into User_Salary (UserName,Month,Salary ) values('CCC','2013/12',5000)insert into User_Salary (UserName,Month,Salary ) values('CCC','2013/04',4000)insert into User_Salary (UserName,Month,Salary ) values('CCC','2013/02',3000)

Ii. SQL

SQLServer:

SELECT username,MONTH,Salary2 salary,SumSalary2 sumsalary FROM (SELECT k.*,case when b.flag1=0 then -1 else b.flag1 end flag2,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(order by UserName,month desc) r  FROM (SELECT a.*,case when maxSalary>SumSalary then 1 when 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 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>SumSalary then 1 when 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 maxSalaryfrom User_Salary u ) a )A) B   on k.R = B.R+1  ) a WHERE FLAG1=1 OR FLAG1 = 0 OR FLAG2 = 1

Iii. Summary

It mainly refers to the acquisition of the record of the last row, that is, the lead and lag Implementation of SQL Server previously written. If SQLServer2010 (like this version) has a built-in lead and lag, the SQL is simple.

I personally think this kind of SQL is good as a trainer, but it is a little complicated to use in the application, and it should be re-processed on the application's business and table structure.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.