选择行，直到满足一列的总金额(mysql)

问题描述：

我已经在SF中看到了这个问题，但是我是菜鸟，只是无法让我的头脑变得模糊.因此，如果感觉像是重复，请原谅我.

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.

我的样品表


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

我需要获取"UNTIL"行，对于特定的供应商ID，"QTY"的累计总数按降序等于或大于5.

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.

在此示例中，对于供应商1，它将是ID为5和2的行.

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

查询:

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)

相关推荐