我有两张表INVENTORY和ITEM
库存表
| PID |product| Qty |
| 1 | Bag | 500 |
| 3 | Cloth | 800 |
| 1 | Bag | 200 |
| 5 | Cap | 100 |
| 5 | Cap | 750 |项目表
| PID |product| Quantity |
| 1 | Bag | 5 |
| 3 | Cloth | 20 |
| 1 | Bag | 10 |
| 5 | Cap | 4 |
| 3 | Cloth | 3 |
| 1 | Bag | 2 |
| 5 | Cap | 4 |
| 5 | Cap | 8 |预期结果
| PID |product| SUM_Inv | SUM_item|
| 1 | Bag | 700 | 17 |
| 3 | Cloth | 800 | 23 |
| 5 | Cap | 850 | 15 |问题:我在计算库存数量时得到了错误的答案。请帮助我,我已经试着解决这个问题好几个星期了,请看下面的代码
$query=mysqli_query($con,"select i.*, i.PID, i.ProductName, SUM(Qty) as SQty, SUM(quantity) as quantity, t.status
FROM inventory as i
JOIN items as o ON i.PID=o.PID
JOIN tblorders as t ON o.order_id=t.orderNumber
WHERE t.status ='Completed'
GROUP BY PID");发布于 2020-05-20 21:11:04
SELECT PID, product, SUM(Qty) SUM_Inv, Sum(Quantity) SUM_item
FROM ( SELECT PID, product, Qty, 0 Quantity FROM inventory
UNION ALL
SELECT PID, product, 0, Quantity FROM item ) total
GROUP BY PID, producthttps://stackoverflow.com/questions/61913742
复制相似问题