SQL 我如何使用 SUM() OVER()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10039431/
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 can I use SUM() OVER()
提问by serkan
I can't understand this code's bug
我无法理解这段代码的错误
ID AccountID Quantity
1 1 10 Sum = 10
2 1 5 = 10 + 5 = 15
3 1 2 = 10 + 5 + 2 = 17
4 2 7 = 7
5 2 3 = 7 + 3 = 10
SELECT ID, AccountID, Quantity,
SUM(Quantity) OVER (PARTITION BY AccountID ) AS TopBorcT,
FROM tCariH
回答by Andriy M
Seems like you expected the query to return running totals, but it must have given you the same values for both partitions of AccountID.
似乎您希望查询返回运行总计，但它必须为AccountID.
To obtain running totals with SUM() OVER (), you need to add an ORDER BYsub-clause after PARTITION BY …, like this:
要使用 获得运行总计SUM() OVER ()，您需要在 之后添加一个ORDER BY子句PARTITION BY …，如下所示：
SUM(Quantity) OVER (PARTITION BY AccountID ORDER BY ID)
But remember, not all database systems support ORDER BYin the OVERclause of a window aggregate function. (For instance, SQL Server didn't support it until the latest version, SQL Server 2012.)
但请记住，并非所有数据库系统都支持窗口聚合函数ORDER BY的OVER子句。（例如，SQL Server 直到最新版本 SQL Server 2012 才支持它。）
回答by UV.
if you are using SQL 2012 you should try
如果您使用的是 SQL 2012，则应该尝试
SELECT ID,
AccountID,
Quantity,
SUM(Quantity) OVER (PARTITION BY AccountID ORDER BY AccountID rows between unbounded preceding and current row ) AS TopBorcT,
FROM tCariH
if available, better order by date column.
如果可用，最好按日期列排序。
回答by AjaySharma2061
Query would be like this:
查询将是这样的：
SELECT ID, AccountID, Quantity,
SUM(Quantity) OVER (PARTITION BY AccountID ) AS TopBorcT
FROM #Empl ORDER BY AccountID
Partition by works like group by. Here we are grouping by AccountID so sum would be corresponding to AccountID.
Partition by 与 group by 类似。这里我们按 AccountID 分组，因此 sum 将对应于 AccountID。
First first case, AccountID = 1 , then sum(quantity) = 10 + 5 + 2 => 17 & For AccountID = 2, then sum(Quantity) = 7+3 => 10
第一种情况， AccountID = 1 ，然后 sum(quantity) = 10 + 5 + 2 => 17 & For AccountID = 2，然后 sum(Quantity) = 7+3 => 10
so result would appear like attached snapshot.
所以结果会像附加快照一样出现。
