qq_20560653 于 2015.07.18 23:05 提问

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.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
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
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 谢谢

devmiao      2015.07.18 23:23

qq_22886617   2015.07.18 23:24

strutce      2015.07.19 11:39

qq_20560653 C结果就是要把Sumsalary与B的salary的值一致

[SQL] 只更新表中某一部分数据的实现方法
（1）只更新表中前10条数据（更新为固定值） （2）用一个表的字段值更新另一个表的某字段值 （3）更新表前10条数据（更新为另一个表的数据）

sql 如何提取某一字段记录的部分数据

SQL查询部分数据表中部分数据

很简单，使用opendatasource就可以实现了insert into tablenameselect * from opendatasource(Microsoft.Jet.OLEDB.4.0,Data Source="excel文件的具体位置";Extended properties=Excel 5.0)...Sheet名\$

SQL更新某字段中的部分值语句
SQL更新某字段中的部分值语句： Update tableName SET columeName = REPLACE(columeName, 'a', 'b')

1. Jacobi迭代法