SQLServer复杂SQL逻辑实现

一、问题

如下图，已有表a与表b的数据，如何通过SQL语句变成表c？表a是UserName相同时，根据Month降序汇总Salary数据；表b是UserName对应SumSalary的上限值；表c是从表a中取出表b上限值以下的分录，若分录的SumSalary值大于b表的Salary，则拆分出刚好汇总等于b表的Salary数。

create table b(
  UserName nvarchar(200),Salary int
)
create table User_Salary (UserName nvarchar(200),Month nvarchar(20),Salary int)
go
insert 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)

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 maxSalary
from 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 maxSalary
from 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当做练手是不错的，但用着应用中复杂度就有点高了，应对对应用的业务和表结构做重新处理。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!