SQLServer複雜SQL邏輯實現，sqlserversql邏輯

SQLServer複雜SQL邏輯實現，sqlserversql邏輯

一、問題

如，已有表a與表b的資料，如何通過SQL語句變成表c？表a是UserName相同時，根據Month降序匯總Salary資料；表b是UserName對應SumSalary的上限值；表c是從表a中取出表b上限值以下的分錄，若分錄的SumSalary值大於b表的Salary，則拆分出剛好匯總等於b表的Salary數。

表A的SQL語句：

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)

二、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

三、總結

主要還是對上一行下一行記錄的擷取上，也就是以前寫的SQLServer的lead,lag的實現。如果SQLServer2010（好像是這版本），就有內建的lead,lag了，sql就簡單了。

個人覺得這類SQL當做練手是不錯的，但用著應用中複雜度就有點高了，應對對應用的業務和表結構做重新處理。

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。