mysql - 如果 id 更改，SQL 重置变量值

Question

SELECT * FROM quantity;
+----+-----------+------------+-------------+
| id | productid | inquantity | outquantity |
+----+-----------+------------+-------------+
|  1 |         1 |        100 |           0 |
|  2 |         1 |         10 |           0 |
|  3 |         1 |          0 |          50 |
|  4 |         1 |          0 |         100 |
|  5 |         2 |        200 |           0 |
|  6 |         2 |          0 |         200 |
|  7 |         2 |         15 |           0 |
|  8 |         3 |        100 |           0 |
|  9 |         3 |         50 |           0 |
| 10 |         3 |          0 |           5 |
+----+-----------+------------+-------------+

为了计算特定产品的数量，我这样做

SELECT 
id , productid,
inquantity,
outquantity,
@qty:= (@qty+inquantity)-outquantity as qty 
from quantity,(select @qty:= 0 )r 
WHERE productid=1;
+----+-----------+------------+-------------+-----+
| id | productid | inquantity | outquantity | qty |
+----+-----------+------------+-------------+-----+
|  1 |         1 |        100 |           0 | 100 |
|  2 |         1 |         10 |           0 | 110 |
|  3 |         1 |          0 |          50 |  60 |
|  4 |         1 |          0 |         100 | -40 |
+----+-----------+------------+-------------+-----+

是否可以为所有产品 ID 计算此值？如果productid与之前的不同，如何将变量@qty重置为从0开始？

要得到这个:

+----+-----------+------------+-------------+-----+
| id | productid | inquantity | outquantity | qty |
+----+-----------+------------+-------------+-----+
|  1 |         1 |        100 |           0 | 100 |
|  2 |         1 |         10 |           0 | 110 |
|  3 |         1 |          0 |          50 |  60 |
|  4 |         1 |          0 |         100 | -40 |
|  5 |         2 |        200 |           0 | 200 |
|  6 |         2 |          0 |         200 |   0 |
|  7 |         2 |         15 |           0 |  15 |
|  8 |         3 |        100 |           0 | 100 |
|  9 |         3 |         50 |           0 | 150 |
| 10 |         3 |          0 |           5 | 145 |
+----+-----------+------------+-------------+-----+

Answer 1

您可以使用case语句和另一个变量来检查相同的产品ID

SELECT 
id , productid,
inquantity,
outquantity,
@qty := case when @g = productid then @qty else 0 end,
@qty:= (@qty+inquantity)-outquantity as qty ,
@g := productid
from quantity,
(select @qty:= 0 ,@g=null)r 
order by productid

DEMO