本文介绍了选择行直到在列中达到总金额(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)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！