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

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.

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 WORKING DEMO

If you are using PHP, you can use the following loop to get all results from query. Query will get result and through while loop we can display all records.

   <?php
 $query =   mysqli_query($db, "SELECT * FROM table WHERE amount >= 1.2 ORDER BY expirydate");
while($array = mysqli_fetch_array($query)){
        echo $row['name'].' '.$row['amount'].' '.$row['expirydate'].'<br>';


      }
?>