一、问题
如下图,已有表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,'2011/01',2000)insert into User_Salary (Username,'2011/02',3000)insert into User_Salary (Username,Salary ) values('BBB',2500)insert into User_Salary (Username,Salary ) values('CCC','2013/12',5000)insert into User_Salary (Username,'2013/04',4000)insert into User_Salary (Username,'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.*,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 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当做练手是不错的,但用着应用中复杂度就有点高了,应对对应用的业务和表结构做重新处理。
总结以上是内存溢出为你收集整理的SQLServer复杂SQL逻辑实现全部内容,希望文章能够帮你解决SQLServer复杂SQL逻辑实现所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)