如何在sql server中获取列的运行总和

Hi I have a column with name Qty from table Bills i want a column that show the running sum of Qty column like this :

嗨我有一个名为Qty的列来自表Bills我想要一个列显示Qty列的运行总和，如下所示：

Qty   Run_Sum
1      1
2      3
3      6
4      10
5      15

Suggest me some appropriate method to make running some thankx

建议我一些适当的方法来运行一些thankx

6 个解决方案

#1

if you RDBMS supports window function,

如果您的RDBMS支持窗口功能，

for SQL Server 2012

对于SQL Server 2012

SELECT  Qty,
        SUM(Qty) OVER (ORDER BY Qty) AS CumulativeTOTAL
FROM    tableName

• SQLFiddle Demo
• SQLFiddle演示

for SQL Server 2008

对于SQL Server 2008

SELECT a.Qty, (SELECT SUM(b.Qty)
               FROM   TableName b
               WHERE  b.Qty <= a.Qty)
FROM   TableName a
ORDER  BY a.Qty;

• SQLFiddle Demo
• SQLFiddle演示

#2

SQLFiddle demo

SQLFiddle演示

SELECT Qty,
SUM(Qty) OVER (ORDER BY Qty) Run_Sum
FROM t ORDER BY Qty

For SQLServer prior to 2012:

对于2012之前的SQLServer：

select Qty,
(select sum(Qty) from t where Qty<=t1.Qty)
from t t1 order by Qty

SQLFiddle demo

SQLFiddle演示

Or also you can do it without subquery:

或者您也可以在没有子查询的情况下执行此操作：

select t1.Qty, sum(t2.Qty)
from t t1 
join t t2 on (t1.Qty>=t2.Qty)
group by t1.Qty
order by t1.Qty

SQLFiddle demo

SQLFiddle演示

#3

Here's a sample using Oracle/analytical functions:

以下是使用Oracle /分析函数的示例：

select id, qty, sum(qty) over(order by id asc) run_sum
from test;

http://www.sqlfiddle.com/#!4/3d149/1

http://www.sqlfiddle.com/#!4/3d149/1

#4

Check this

检查一下

DECLARE @TEMP table
(
    ID int IDENTITY(1,1),
    QUANTITY int
)

INSERT INTO @TEMP
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 8 UNION ALL
SELECT 7 UNION ALL
SELECT 5 UNION ALL
SELECT 1

SELECT t.QUANTITY AS Qty, SUM(t1.QUANTITY) AS Run_Sum
FROM @TEMP t
INNER JOIN @TEMP t1
ON t1.ID <= t.ID
GROUP BY t.ID, t.QUANTITY
ORDER BY t.ID

#5

;with cte as (
  select top 1 Qty, Qty as RunningSum
  from Bills
  order by Qty

  union all

  select t.Qty, cte.RunningSum + t.Qty
  from cte
  inner join Bills t on cte.Qty + 1 = t.Qty
)
select * from cte

#6

@mahmud: See what this gives

@mahmud：看看这给了什么

DECLARE @Bills table
(
    QUANTITY int
)

INSERT INTO @Bills
SELECT 2 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT -5 UNION ALL
SELECT 5 UNION ALL
select 1

;with cte as (
  select top 1 QUANTITY, QUANTITY as RunningSum
  from @Bills
  order by QUANTITY

  union all

  select t.QUANTITY, cte.RunningSum + t.QUANTITY
  from cte
  inner join @Bills t on cte.QUANTITY + 1 = t.QUANTITY
)
select * from cte