选择行直到在列中达到总金额(mysql) [英] Select rows until a total amount is met in a column (mysql)

问题描述

I have seen this issue in SF, but me being a noob I just can't get my fried brain around them. So please forgive me if this feels like repetition.

My Sample Table

--------------------------
ID   | Supplier   | QTY
--------------------------
1       1          2
2       1          2
3       2          5
4       3          2
5       1          3
6       2          4

I need to get the rows "UNTIL" the cumulative total for "QTY" is equal or greater than 5 in descending order for a particular supplier id.

In this example, for supplier 1, it will be rows with the ids of 5 and 2.

    Id - unique primary key
    Supplier - foreign key, there is another table for supplier info.
    Qty - double

解决方案

How about this? Using two variables.

SQLFIDDLE DEMO

Query:

set @tot:=0;
set @sup:=0;

select x.id, x.supplier, x.ctot
from (
select id, supplier, qty,
@tot:= (case when @sup = supplier then
@tot + qty else qty end) as ctot,
@sup:=supplier
from demo
order by supplier asc, id desc) x
where x.ctot >=5
;

| ID | SUPPLIER | CTOT |
------------------------
|  2 |        1 |    5 |
|  1 |        1 |    7 |
|  3 |        2 |    5 |

这篇关于选择行直到在列中达到总金额(mysql)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！