返回从MySQL查询到达字段数量的总和的行

Question

My knowledge of MySQL is basic. I want to build a query to return all rows when is reached that sum a given amount value

• given value: 1.20
• field to sum: amount
• order by expirydate

table:

id name     expirydate  amount
1  name1    11-08-16     0.20     
2  name2    10-08-16     1.50
3  name3    08-08-16     1.00

I need the result from id 3, 2 order by expiry date.

Answer 1

You can accomplish this using MySQL user defined variables

SET @n := 1.2;

SELECT 
 t.id,
 t.name,
 t.expirydate,
 t.amount
FROM 
(
    SELECT 
    *,
    @bal := @bal + amount AS runningAmount,
    IF(@bal >= @n, @doneHere := @doneHere + 1 , @doneHere) AS whereToStop
    FROM table_amount
    CROSS JOIN (SELECT @bal := 0.0 , @doneHere := 0) var
    ORDER BY expirydate
) AS t
WHERE t.whereToStop <= 1

See a <kbd>WORKING DEMO</kbd>