MySQL选择行的总和 [英] Mysql Select rows to the sum of

问题描述

我有一张包含以下信息的表:

I have a table with the following info:

id |      Item ID     | stock_package_id | amount | price
0  |        775       |         1        |   16   |  0.22
1  |        758       |         2        |   10   |  0.28
2  |        775       |         3        |   10   |  0.30
3  |        774       |         4        |   10   |  0.25
4  |        775       |         5        |   10   |  0.10
5  |        775       |         6        |   10   |  0.55

我的问题是我正在尝试创建一个查询(尽可能简单，因为该表将与其他一些表保持连接)，这将允许我输入金额:

My issue is i'm trying to create a query (simple as possible as this table will be left joined with a few other tables) that will allow me to enter an amount:

例如

我要从最便宜的价格中首先选择Item_Id 775中的22个.

I want 22 of Item_Id 775 selecting from the cheapest price first.

所以我要返回的是:

id |      Item ID     | stock_package_id | amount | price
4  |        775       |         5        |   10   |  0.10
0  |        775       |         1        |   12   |  0.22 - this is only 12 as we only want 22 total

一个伪装示例:

从stock_table中选择，直到amount的总和等于或大于按价格输入的数字(22)订单

select from stock_table until the sum of amount is equal to or greater than input number (22) order by price

这可能与MySql有关吗?

Is this possible to do with MySql?

推荐答案

模式:

 CREATE TABLE table1
(`id` int, `Item_ID` int, `stock_package_id` int, `amount` int, `price` float);

 INSERT INTO table1
(`id`, `Item_ID`, `stock_package_id`, `amount`, `price`)
 VALUES
(0, 775, 1, 16, 0.22),
(1, 758, 2, 10, 0.28),
(2, 775, 3, 10, 0.30),
(3, 774, 4, 10, 0.25),
(4, 775, 5, 10, 0.10),
(5, 775, 6, 10, 0.55);

要获取的设置金额:

SET @var_amount = 22;

选择金额:

SELECT * FROM (
SELECT table1.*, if ((@var_amount := @var_amount - amount) > 0, amount, amount +  @var_amount) as buy_amount
FROM table1
WHERE Item_ID = 775
ORDER BY price ASC
) AS tmp 
WHERE buy_amount > 0

结果:

id  Item_ID stock_package_id    amount  price   buy_amount
4   775 5                       10      0.1     10
0   775 1                       16      0.22    12

这篇关于MySQL选择行的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！