在sql中查找特定id的总和值的行

I have a table like below--

我有一张如下表 -

Id| Amount|DateAdded  |
--|-------|-----------|
1   20     20-Jun-2018
1   10     05-Jun-2018
1   4      21-May-2018
1   5      15-May-2018
1   15     05-May-2018
2   25     15-Jun-2018
2   25     12-Jun-2018
2   65     05-Jun-2018
2   65     20-May-2018

Here If I sum up the Amount of Id = 1 then I will get 54 as the sum result. I want to find those rows of Id = 1 whose sum is not greater then exact 35 or any given value

在这里如果我总结Id = 1的数量,那么我将得到54作为总和结果。我想找到Id = 1的行,其总和不大于35或任何给定值

In case of given value 35 the expected Output for id = 1 should be--

在给定值35的情况下,id = 1的预期输出应为 -

    Id| Amount|DateAdded  |
    --|-------|-----------|
    1   20     20-Jun-2018
    1   10     05-Jun-2018
    1   4      21-May-2018
    1   5      15-May-2018

In case of given value 50 the expected Output for Id = 2 should be--

在给定值50的情况下,Id = 2的预期输出应为 -

  Id| Amount|DateAdded  |
  --|-------|-----------|
   2   25     15-Jun-2018
   2   25     12-Jun-2018

1 个解决方案

#1

You would use a cumulative sum. To get all the rows:

您将使用累计金额。获取所有行:

select t.*
from (select t.*,
             sum(amount) over (partition by id order by dateadded) as running_amount
      from t
     ) t
where t.running_amount - amount < 35;

To get just the row that passes the mark:

要获得通过标记的行:

where t.running_amount - amount < 35 and
      t.running_amount >= 35

#1