如下图,已有表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 eclipse运行项目时遇到的问题
- ¥15 关于#c##的问题:最近需要用CAT工具Trados进行一些开发
- ¥15 南大pa1 小游戏没有界面,并且报了如下错误,尝试过换显卡驱动,但是好像不行
- ¥15 没有证书,nginx怎么反向代理到只能接受https的公网网站
- ¥50 成都蓉城足球俱乐部小程序抢票
- ¥15 yolov7训练自己的数据集
- ¥15 esp8266与51单片机连接问题(标签-单片机|关键词-串口)(相关搜索:51单片机|单片机|测试代码)
- ¥15 电力市场出清matlab yalmip kkt 双层优化问题
- ¥30 ros小车路径规划实现不了,如何解决?(操作系统-ubuntu)
- ¥20 matlab yalmip kkt 双层优化问题