SQL 如何在sql server中获取一列的运行总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14953294/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get running sum of a column in sql server
提问by user1448783
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 :
嗨，我在 Bills 表中有一个名为 Qty 的列，我想要一列显示 Qty 列的运行总和，如下所示：
Qty Run_Sum
1 1
2 3
3 6
4 10
5 15
Suggest me some appropriate method to make running some thankx
建议我一些合适的方法来运行一些谢谢
回答by John Woo
if you RDBMS supports window function,
如果你的 RDBMS 支持窗口函数，
for SQL Server 2012
SELECT Qty,
SUM(Qty) OVER (ORDER BY Qty) AS CumulativeTOTAL
FROM tableName
为了 SQL Server 2012
SELECT Qty,
SUM(Qty) OVER (ORDER BY Qty) AS CumulativeTOTAL
FROM tableName
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;
回答by valex
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
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
回答by Lloyd Santos
Here's a sample using Oracle/analytical functions:
这是使用 Oracle/分析函数的示例：
select id, qty, sum(qty) over(order by id asc) run_sum
from test;
回答by vikas
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
回答by muhmud
;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
回答by Varun
@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
