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:

e.g.

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

So what I would want returned is:

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

a pesudo example:

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

Is this possible to do with MySql?

回答1:

Schema:

 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);

Setting amount to get:

SET @var_amount = 22;

Selecting amount:

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

Result:

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

回答2:

you need to use ORDER BY clause to sort the records based on price column:

SELECT *
FROM table_name
WHERE Item_ID = 775
ORDER BY price ASC
LIMIT 22;

if you want to fetch records until sum(price) becomes value(22) then try this:

SELECT *
FROM table_name, (SELECT @var_price := 0) a
WHERE Item_ID = 775 AND
      (@var_price := @var_price + price) <= 22
ORDER BY price ASC;