Postgresql 选择直到达到一定的总量 [英] Postgresql select until certain total amount is reached

问题描述

我想知道我是否可以获得有关以下问题的任何帮助.

I was wondering if I could get any help with the following problem.

我有一个交易表(简化如下)，我只想选择交易，直到我的总金额达到一定金额.

I have a table of transactions (simplified below) and I only want to select transactions until my amount total reaches a certain amount.

交易表

 id |   date   | amount 
----|----------|--------
 1  | 1/1/2012 |   2 
 2  | 2/1/2012 |   3 
 3  | 3/1/2012 |   4
 4  | 4/1/2012 |   20 
 5  | 5/1/2012 |   1 
 6  | 6/1/2012 |   2

现在说我想在桌子上做一个选择，直到总数为 6，即前 2 行，我该怎么做?

Now say I want to do a select on the table until the amount total is 6 i.e just the first 2 rows, how would I do this?

我正在考虑可能会与自身和一些总和进行连接，但实际上并没有取得任何进展.如果可能，我宁愿不使用任何函数.

I was thinking of maybe doing a join with itself and some sum but not really getting anywhere. I'd prefer no to use any functions if possible.

还有任何类似的最低金额.

Also anything similar for minimum amount.

任何帮助将不胜感激:)

Any help would be much appreciated :)

T

推荐答案

select id, 
       date, 
       amount, 
       running_total
from (
    select id,
           date,
           amount,
           sum(amount) over (order by date asc) as running_total
    from transactions
) t
where running_total <= 6

这篇关于Postgresql 选择直到达到一定的总量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！