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，即前两行，我该怎么做？

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屋！