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 |
+----+-----------+------------+-------------+-----+
您可以使用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
我认为
| 7 | 2 | 15 | 0 | -15 |
数量将15
而不是-15
你可能会得到结果
select
id,
productid,
inquantity,
outquantity,
qty from (
SELECT
id ,
productid,
inquantity,
outquantity,
@qty:= if(@prev_prod = productid, ((@qty+inquantity)-outquantity),(inquantity-outquantity)) as qty,
@prev_prod:= productid
from quantity,(select @qty:= 0,@prev_prod:=0 )r
order by productid,id
)x;