qq_20560653 2015-07-18 15:05 采纳率: 100%
浏览 1651
已采纳

SQL求取部分数据,如何实现

如下图,已有表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)

  • 写回答

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
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题