Postgresql选择直到达到某个总量

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.

我有一个交易表（简化如下），我只想选择交易，直到我的金额达到一定金额。

Transactions table

交易表

 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

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?

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

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 :)

任何帮助将非常感激：）

3 个解决方案

#1

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

#2

Although it may not be the most efficent way (as you're still, in essence, selecting everything first), I'd look at using a running total.

虽然它可能不是最有效的方式（因为你在本质上，首先选择所有内容），但我会考虑使用总计。

Something like:

就像是：

SELECT
  *
FROM
  (
  SELECT
    id
    , date
    , amount
    , (SELECT SUM(amount) FROM Transactions WHERE id <= t.id) AS RunningTotal
  FROM
    Transactions t
) t1
WHERE
  t1.RunningTotal < 6

#3

select T1.*
from Transactions as T1
where 6 - (select sum(T2.amount) 
               from Transactions as T2 where  T2.id <= T1.id
          ) >= 0
order by id asc

This query works on SQL Server if Postgres supports subqueries like SQL Server does it can help you

如果Postgres支持像SQL Server这样的子查询可以帮助您，则此查询适用于SQL Server

#1

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

#2