SQL 列值分组累计求和
测试数据：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | create table Salary ( UserId int, [Month] int, Salary decimal(13,2) ) insert into Salary select 10010,1,3500.40 union all select 10010,2,3000.60 union all select 10010,3,4000.00 union all select 10010,4,3800.50 union all select 10020,1,3000.50 union all select 10020,2,3000.50 union all select 10020,3,3500.20 union all select 10020,4,3800.80 union all select 10020,5,4000.00 union all select 10018,2,4000.50 union all select 10018,3,4500.20 union all select 10018,4,4800.80 |
查看数据：
| 1 | select * from Salary |
要得到的结果：
方法一：
| 1 2 3 4 | select *, (select sum(Salary) from Salary where UserId=s.UserId and Month<=s.Month) as Balance from Salary as s order by GETDATE() |
方法二：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | ;with cte as ( select *,ROW_NUMBER() over(partition by UserId order by [Month] asc) as Rn from Salary ), cte1 as ( select *,Salary as Balance from cte where RN=1 union all select a.UserId,a.Month,a.Salary,a.Rn, cast((a.Salary+b.Balance) as decimal(13,2)) from cte as a,cte1 as b where a.UserId=b.UserId and a.RN=b.RN+1 ) select UserId,[MONTH],Salary,Balance from cte1 order by UserId,[month] |
方法三（很新颖）：
| 1 2 3 4 5 6 7 8 | select *,CAST(0 as decimal(13,2)) as Balance into #Sal from Salary declare @userid int,@amount decimal(13,2) update s set @amount=case when s.UserId=@userid then s.Salary+@amount else s.Salary end, @UserId=s.UserId, s.Balance=@amount from #Sal as s select * from #Sal drop table #Sal |
方法四（sql server 2012以上适用）：
| 1 2 3 | select *, Sum(Salary) over(partition by UserId order by [Month] asc) as Balance from Salary |
列值分组求和的方法同样也是求差的情况，只不过将分组后的非第一条数据的求差字段（如：Salary）求相反数作为一个新的字段，这样就转换成列值分组球和了。
睿联技术公司福利 31人发布
