如下图,已有表a与表b的数据,如何通过SQL语句变成表c?表a是UserName相同时,根据Month降序汇总Salary数据;表b是UserName对应SumSalary的上限值;表c是从表a中取出表b上限值以下的分录,若分录的SumSalary值大于b表的Salary,则拆分出刚好汇总等于b表的Salary数。
表A的SQL语句:
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,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求取部分数据,如何实现
- 写回答
- 好问题 0 提建议
- 追加酬金
- 关注问题
- 邀请回答
-
4条回答
- danielinbiti 2015-07-18 16:22关注
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.*,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 ) B on k.R = B.R+1 ) a WHERE FLAG1=1 OR FLAG1 = 0 OR FLAG2 = 1
本回答被题主选为最佳回答 , 对您是否有帮助呢?解决 无用评论 打赏 举报
悬赏问题
- ¥15 求差集那个函数有问题,有无佬可以解决
- ¥15 【提问】基于Invest的水源涵养
- ¥20 微信网友居然可以通过vx号找到我绑的手机号
- ¥15 寻一个支付宝扫码远程授权登录的软件助手app
- ¥15 解riccati方程组
- ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
- ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
- ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
- ¥50 树莓派安卓APK系统签名
- ¥65 汇编语言除法溢出问题