2 qq 20560653 qq_20560653 于 2015.07.18 23:05 提问

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
danielinbiti   Ds   Rxr 2015.07.19 00: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
qq_20560653
qq_20560653 谢谢
2 年多之前 回复
devmiao
devmiao   Ds   Rxr 2015.07.18 23:23

先group by分组,再join b表去比较判断。

qq_22886617
qq_22886617   2015.07.18 23:24

使用视图吧,如果不会,那就用楼上所说的做

strutce
strutce   Ds   Rxr 2015.07.19 11:39

insert into c select a.usernaem,a.mouth,b.salary,a.sumSalary from a,b where a.username=b.username and a.sumSalary<b.salary
你给出的c结果有问题

qq_20560653
qq_20560653 C结果就是要把Sumsalary与B的salary的值一致
2 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片