获取 SQL 表列的总和，直到总和达到 5000 [英] Getting Sum of an SQL table column until the sum reaches 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.

这篇关于获取 SQL 表列的总和，直到总和达到 5000的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！