想要在SQL Server中按原始和列累计总和
问题描述:
以下是我的实际数据。想要在SQL Server中按原始和列累计总和
select Year,fall_unit,summer_unit,spring_unit
from (
select 2010 as Year,4 fall_unit,5 summer_unit,6 spring_unit
union
select 2011 as Year,7 fall_unit,23 summer_unit,90 spring_unit
union
select 2012 as Year,3 fall_unit,2 summer_unit,5 spring_unit) M
想通过原始&栏也做累计总和。
要求按照以下输出。
2010 4 9 15
2011 22 45 135
2012 138 140 145
下面是计算的更多解释。
2010 : 9 = (4+5)
2010 : 15 = (9+6)
2011 : 22 = (15+7)
2011 : 45 = (22+23)
2011 : 135= (45+90)
2012 : 138= (135+3)
2012 : 140= (138+2)
2012 : 145= (140+5)
答
尝试更新后的查询(最后一个查询)。我理解的输出逻辑是:
年| C1:Fall + previous_year_total | C2:秋季+夏季+以前的计数| C3:合计
create table #test(Year int, fall_unit int, summer_unit int, spring_unit int)
insert #test (Year, fall_unit, summer_unit, spring_unit)
select 2010 as Year,4 fall_unit,5 summer_unit,6 spring_unit
union
select 2011 as Year,7 fall_unit,23 summer_unit,90 spring_unit
union
select 2012 as Year,3 fall_unit,2 summer_unit,5 spring_unit
-- select * from #test
select t2.Year,
case when t1.fall_unit IS NULL then t2.fall_unit
else SUM(t1.rowTotal) OVER (ORDER BY t1.YEAR) + t2.fall_unit
end as C1,
case when t1.summer_unit IS NULL then t2.summer_unit + coalesce(t1.rowTotal, 0) + t2.fall_unit
else SUM(t1.rowTotal) OVER (ORDER BY t1.YEAR) + t2.fall_unit + t2.summer_unit
end as C2,
SUM(t2.rowTotal) OVER (ORDER BY t1.YEAR) as C3
from (select t.*, t.fall_unit + t.summer_unit + t.spring_unit as rowTotal from #test t) t1
right join (select t.*, t.fall_unit + t.summer_unit + t.spring_unit as rowTotal from #test t) t2
on t1.YEAR+1 = t2.YEAR
order by t2.Year
它只提供总计,我希望按照在必需输出中描述的累积总计。 – sandip 2014-11-24 13:27:34
@Sandip,我更新了答案(查询)以检索所需的输出。 – 2014-11-25 09:19:25