选择值达到和超过(但只有一个)另一个值的记录? [英] Select records with values up to and over (but only one over) another value?

问题描述

我有数据，例如...

 ID    | Amount
-----------------
 1     | 50.00
 2     | 40.00
 3     | 15.35
 4     | 70.50

等等.我有一个我正在努力的值，在这种情况下，假设是 100.00.我想按照 ID 的顺序获取最多 100.00 的所有记录.而且我还想再买一个，因为我想把它一直填满到我的目标价值.

etc. And I have a value I'm working up to, in this case let's say 100.00. I want to get all records up to 100.00 in order of the ID. And I want to grab one more than that, because I want to fill it up all the way to the value I'm aiming for.

也就是说，我想得到，在这个例子中，记录1、2和3.前两个总和达到90.00，3推动总和超过100.00.所以我想要一个查询来为我做这件事.MySQL中是否存在这样的事情，还是我将不得不求助于PHP数组循环?

That is to say, I want to get, in this example, records 1, 2, and 3. The first two total up to 90.00, and 3 pushes the total over 100.00. So I want a query to do that for me. Does such a thing exist in MySQL, or am I going to have to resort to PHP array looping?

编辑:用英语来说:假设他们的帐户中有 100 美元.我想知道他们的哪些要求可以全部或部分支付.所以我可以还清 50 美元和 40 美元，以及 15.35 美元的一部分.在程序的这一点上，我不在乎偏袒；我只想以任何方式找出哪种质量.

Edit: To put it in English terms: Let's say they have $100 in their account. I want to know which of their requests can be paid, either in toto or partially. So I can pay off the $50 and the $40, and part of the $15.35. I don't care, at this point in the program, about the partialness; I only want to find out which quality in any way.

推荐答案

是的，有可能

 set @total:=0; 
 select * from
 (
   select *, if(@total>100, 0, 1) as included, @total:=@total+Amount 
   from your_table 
   order by id
 ) as alls
 where included=1
 order by id;

这篇关于选择值达到和超过(但只有一个)另一个值的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！