php - MySQL从一个查询中的多行中减去

Question

我有下表，其中 parent_id、price、quantity 和 exp_date 是主要字段.

+-----------+-------+----------+------------+---------------------+------------------+
| parent_id | price | quantity | exp_date   | last_modified       | last_modified_by |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 410   | 3.00     | 2016-07-30 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 300   | 10.00    | 0000-00-00 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 540   | 17.00    | 2016-07-22 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+

我想从最后修改的行开始从总数量中减去一个不同的值。现在我有这个查询:

SET @remain = -19;
Update Stock_props SET quantity =
(SELECT IF((@remain := quantity+@remain) < 0,'0',@remain) as quantity)
WHERE parent_id = 2
ORDER BY last_modified DESC

这个特别的工作是因为我减去的值比最后一行多。它将输出:

+-----------+-------+----------+------------+---------------------+------------------+
| parent_id | price | quantity | exp_date   | last_modified       | last_modified_by |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 410   | 0.00     | 2016-07-30 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 300   | 0.00     | 0000-00-00 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 540   | 11.00    | 2016-07-22 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+

但是，如果我想减去更小的数，例如 11，结果将是这样的:

+-----------+-------+----------+------------+---------------------+------------------+
| parent_id | price | quantity | exp_date   | last_modified       | last_modified_by |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 410   | 2.00     | 2016-07-30 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 300   | 0.00     | 0000-00-00 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 540   | 19.00    | 2016-07-22 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+

而不是这个:

+-----------+-------+----------+------------+---------------------+------------------+
| parent_id | price | quantity | exp_date   | last_modified       | last_modified_by |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 410   | 0.00     | 2016-07-30 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 300   | 2.00     | 0000-00-00 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 540   | 17.00    | 2016-07-22 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+

我在这里错过了什么？提前致谢!

Answer 1

好吧，如果以后有人偶然发现这个问题，下面的查询非常有效:

ALTER TABLE Stock_props ADD helper numeric;
SET @remain = 11;
Update Stock_props SET quantity =
(SELECT IF(((@remain := quantity+@remain) < 0),0,@remain) as quantity),
helper = (SELECT IF((@remain>0), @remain:=0,@remain)as helper),
ORDER BY last_modified DESC;
ALTER TABLE Stock_props DROP helper;

上面的工作如下:

1. 在名为 helper 的表中添加一个新行
2. 设置数量的值和@remain(后者是必须的，因为如果它是负数我们要继续减法)
3. 根据符号覆盖@remain的值
4. 放下助手行