dongtang1966 2016-08-11 05:09
浏览 105
已采纳

返回从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.

  • 写回答

2条回答 默认 最新

  • duanji1482 2016-08-11 05:28
    关注

    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>

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?