sql语句查询前
year salary
2001 10
2002 20
2003 5000
2004 102
2005 1201sql语句查询后
year 总计
2001 10
2002 30
2003 5030
2004 5132
2005 6333
该如何写sql语句
year salary
2001 10
2002 20
2003 5000
2004 102
2005 1201sql语句查询后
year 总计
2001 10
2002 30
2003 5030
2004 5132
2005 6333
该如何写sql语句
select [year],(select sum(salary) from tb where [year] <= t.[year]) as [总计]
from tb t
group by [year]
[year],(select sum(salary) from tb where [year]<t.[year]) as [总计]
from
tb t
group by
[year]
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
year int,
salary int
)
go
--插入测试数据
insert into tb select 2001,10
union all select 2002,20
union all select 2003,5000
union all select 2004,102
union all select 2005,1201
go
--代码实现select year,salary=(select sum(salary) from tb where year<=t.year) from tb t/*测试结果year salary
---------------------
2001 10
2002 30
2003 5030
2004 5132
2005 6333(5 行受影响)
*/
create table #temp
(
year int,
salary int
)
insert into #temp
select 2001,10 union all
select 2002,20 union all
select 2003,5000 union all
select 2004,102 union all
select 2005,1201
go
select * from
(select distinct [YEAR] from #temp)a
cross apply
(select 总计=SUM(salary) from #temp where [year] <= a.[year]) b
/*
YEAR 总计
2001 10
2002 30
2003 5030
2004 5132
2005 6333
*/