問題描述

我正在對具有兩列 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的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！