获取 SQL 表列的总和，直到总和达到 5000
我正在对具有两列 Amount 和 Date 的表进行 sql 查询，该表应返回 Amount 列值的总和，直到达到5000 并且它还应该返回 Date 列中 Sum(Amount) 达到 5000 按 <排序的值代码>日期
I am working on an sql query for a table with two columns Amount and Date which should return sum of Amount column values until reaches 5000 and it should also return the value in Date column at which Sum(Amount) reaches 5000 sorted by Date
例如，我的 SQL TABLE
ID Amount Date
1 1000 5/5/2014
2 1000 5/1/2014
3 900 5/3/2014
4 1500 5/4/2014
5 2000 5/4/2014
6 2500 5/5/2014
在上表中，Amount 的总和是按 Date 排序后计算的，当达到 5000 时，返回 Amount 及其关联 Date 的总和.
In the above table the sum of Amount should be calculated after sorting it by Date and should return the sum of Amount and its associated Date once it reaches 5000 mark.
对数据进行排序后，它变成如下所示
after sorting the data it becomes something like following
ID Amount Date
2 1000 5/1/2014
3 900 5/3/2014
4 1500 5/4/2014
5 2000 5/4/2014
1 1000 5/5/2014
6 2500 5/5/2014
查询应该返回以下结果
TotalAmount Date
5400 5/4/2014
以上结果是因为ID=5 Amount=200 and Date=5/4/2014
我可以知道在 SQL Server
推荐答案
对于 SQL Server，你可以使用 SUM() OVER() 并且只得到总和 >= 的第一行5000;
For SQL Server, you can use SUM() OVER() and just get the first row where the total sum is >= 5000;
WITH cte AS (
SELECT id, date, SUM(amount) OVER (ORDER BY date,id) totalamount
FROM mytable
)
SELECT TOP 1 totalamount, date
FROM cte
WHERE totalamount >= 5000
ORDER BY date, id;
用于测试的 SQLfiddle.
相关文章