SQL Server 中实现累加栏位 Oracle中相当于sum(col1) over(order by col2)

if object_id('tempdb..#A') is not null drop table #A
select 'a' as Name,2007 as Year,100 as M
into #A
insert into #A select 'a'as Name,2008 as Year,400 as M
insert into #A select 'a'as Name,2009 as Year,600 as M
insert into #A select 'a'as Name,2010 as Year,800 as M
insert into #A select 'b'as Name,2007 as Year,150 as M
insert into #A select 'b'as Name,2008 as Year,250 as M
insert into #A select 'b'as Name,2009 as Year,450 as M
insert into #A select 'b'as Name,2010 as Year,650 as M
insert into #A select 'c'as Name,2007 as Year,700 as M
insert into #A select 'c'as Name,2008 as Year,800 as M
insert into #A select 'c'as Name,2009 as Year,850 as M 
insert into #A select 'c'as Name,2010 as Year,950 as M

select * from #A

SELECT Name,Year,M,(
SELECT SUM(M)AS M FROM #A WHERE Name=T.Name AND Year<=T.Year)AS NUM
FROM #A T
ORDER BY Name ,Year

 

转载于:https://www.cnblogs.com/Doitman/archive/2010/06/26/1765637.html