从MySQL查询返回达到某个字段数量总和的行 [英] Return rows where sum on a field amount is reached from MySQL query

问题描述

我对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

• 给定值:1.20
• 总和字段:金额
• 按到期日排序

表:

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

我需要ID 3的结果，到到期日2顺序.

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

推荐答案

您可以使用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

查看 工作演示

这篇关于从MySQL查询返回达到某个字段数量总和的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！