数据库中的一个表中有 year 、salary这两个字段,原表中数据如原表,请用SQL查询出结果显示的数据:
1)原表中数据:
year salary
-----------------------------------------
2000 1000
2001 2000
2002 3000
2003 40002)结果表中数据:
year salary
------------------------------------------
2000 1000
2001 3000
2002 6000
2003 10000如何做又能实现效果,SQL语句又简单、清晰....
1)原表中数据:
year salary
-----------------------------------------
2000 1000
2001 2000
2002 3000
2003 40002)结果表中数据:
year salary
------------------------------------------
2000 1000
2001 3000
2002 6000
2003 10000如何做又能实现效果,SQL语句又简单、清晰....
select [year],
sum(salary ) over(partition by year) as salary
from tb
go
create table tb([year] int,[salary] int)
insert tb select 2000,1000
union all select 2001,2000
union all select 2002,3000
union all select 2003,4000
go
select [year],salary=(select sum(salary) from tb where [year]<=t.[year]) from tb t
/*
year salary
----------- -----------
2000 1000
2001 3000
2002 6000
2003 10000(4 行受影响)
*/
create table tab(year int,salary int)
insert tab values (2000,1000)
insert tab values (2001,2000)
insert tab values (2002,3000)
insert tab values (2003,4000)select a.year,salary =(select sum(salary) from tab where year<=a.year) from tab a
/*
year salary
----------- -----------
2000 1000
2001 3000
2002 6000
2003 10000(所影响的行数为 4 行)
*/
insert #tb select 2000,1000
union all select 2001,2000
union all select 2002,3000
union all select 2003,4000select [year],
salary=(select sum(salary) from #tb where [year]<=t.[year])
from #tb t
insert #tb select 2000,1000
union all select 2001,2000
union all select 2002,3000
union all select 2003,4000select [year],
salary=(select sum(salary) from #tb where [year]<=t.[year])
from #tb tyear salary
----------- -----------
2000 1000
2001 3000
2002 6000
2003 10000(4 行受影响)
insert into @a select '2000','1000'
union all select '2001','2000'
union all select '2002','3000'
union all select '2003','4000'select year
,salary=(select sum(salary) from @a where salary <= a.salary)
from @a a