计算ms-sql中的累计总和

Question

我有一个tblsumDemo表，具有以下结构

billingid  qty  Percent_of_qty  cumulative 
   1        10      5              5
   2        5       8              13(5+8)
   3        12      6              19(13+6)
   4        1       10             29(19+10)
   5        2       11             40(11+10) 

这就是我尝试过的

declare @s int
 SELECT billingid, qty, Percent_of_qty,
       @s = @s + Percent_of_qty AS cumulative
FROM tblsumDemo
CROSS JOIN (SELECT @s = 0) AS var
ORDER BY billingid

但我无法获得所需的输出，任何帮助都将不胜感激，谢谢

Answer 1

您可以使用CROSS APPLY:

SELECT 
    t1.*,
    x.cumulative
FROM tblSumDemo t1
CROSS APPLY(
    SELECT 
        cumulative = SUM(t2.Percent_of_Qty)
    FROM tblSumDemo t2
    WHERE t2.billingid <= t1.billingid
)x

---

对于SQL Server 2012+，可以使用SUM OVER():

SELECT *,
    cummulative = SUM(Percent_of_Qty) OVER(ORDER BY billingId)
FROM tblSumDemo

Answer 2

您可以使用适用于所有版本的子查询：

  select billingid,qty,percentofqty,
  (select sum(qty) from tblsumdemo t2 where    t1.id<=t2.id) as csum
  from 
  tblsumdemo t1

您也可以使用sql 2012:中的windows函数

   select *,
   sum(qty) over (order by qty rows between unbounded PRECEDING and current row) as csum
    from tblsumdemo

这里我说的是，为每一行（无界的前一行和当前行）从第一行开始的所有行求和。你可以忽略无界的后一行和目前行，这是默认的

Answer 3

使用ROW_NUMBER只是按升序排列billingID，然后使用join。

查询

;with cte as(
    select rn = row_number() over(
        order by billingid
    ), *
    from tblSumDemo
)
select t1.billingid, t1.qty, t1.Percent_of_qty,
sum(t2.Percent_of_qty) as cummulative
from cte t1
join cte t2
on t1.rn >= t2.rn
group by t1.billingid, t1.qty, t1.Percent_of_qty;