SQLServer复杂SQL逻辑实现

SQLServer复杂SQL逻辑实现,第1张

概述一、问题 如下图,已有表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

一、问题

如下图,已有表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逻辑实现所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/1165281.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-06-01
下一篇 2022-06-01

发表评论

登录后才能评论

评论列表(0条)

保存