mysql - 如何在mysql中用新插入的值计算最后一行的值

Question

全部

如何在mysql中用新插入的值计算最后一行的值

final_stock (2nd) row = final_stock(1st) row - out(2nd) row

我有一张 table

no   date         product_code   first_stock   in  out  final_stock
1    2018/01/18      A001          50          0   0      50 (last inserted)
2    2018/01/18      A001          0           0   35     15 <==== i want to achieve this

当我插入新数据(数据编号 2)并用 35 填充“out”列时，第 2 行的“final_stock”列将有 15。我怎样才能实现这一点

这是查询

mysql_query("insert into flow_stock (date, product_code, first_stock, in, out, final_stock )
values('$date', '$code','','','$out',(select final_stock from flow_stock order by no desc limit 1) - ".$out.")");

需要你的帮助，查询不计算

Answer 1

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(no INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,date DATE NOT NULL
,product_code CHAR(4) NOT NULL
,first_stock INT NOT NULL
,`in` INT NOT NULL
,`out` INT NOT NULL
,final_stock INT NOT NULL
);


INSERT INTO my_table VALUES (1,'2018-01-18','A001',50,0,0,50);

SELECT * FROM my_table;
+----+------------+--------------+-------------+----+-----+-------------+
| no | date       | product_code | first_stock | in | out | final_stock |
+----+------------+--------------+-------------+----+-----+-------------+
|  1 | 2018-01-18 | A001         |          50 |  0 |   0 |          50 |
+----+------------+--------------+-------------+----+-----+-------------+

INSERT INTO my_table (date,product_code,first_stock,`in`,`out`,final_stock) 
SELECT '2018-01-21'
     , 'A001'
     , 0
     , 0
     , 35
     , final_stock - 35 
  FROM my_table 
 WHERE product_code = 'A001' 
 ORDER 
    BY no DESC 
 LIMIT 1;

Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM my_table;
+----+------------+--------------+-------------+----+-----+-------------+
| no | date       | product_code | first_stock | in | out | final_stock |
+----+------------+--------------+-------------+----+-----+-------------+
|  1 | 2018-01-18 | A001         |          50 |  0 |   0 |          50 |
|  2 | 2018-01-21 | A001         |           0 |  0 |  35 |          15 |
+----+------------+--------------+-------------+----+-----+-------------+