mysql - mysql显示状态完成的产品和总数量

Question

我想显示产品列表、库存以及它们的总订购orders数量Completed。

我有这个查询，但它抛出错误并且它不完整。

SELECT o.id, o.name, op.item_id, o.status SUM( op.quantity ) as quantity
FROM  `orders` o JOIN 
      order_items op
      ON op.order_id = o.id
WHERE o.status = 'Completed'
GROUP BY op.item_id, o.id
ORDER BY o.id, op.item_id;

预期输出：

+-------------+--------------------------+-------------+
|  Product    |  Total Ordered Quantity  |    Stock    |
+-------------+--------------------------+-------------+
|  Product A  |            5             |      8      |
|  Product B  |            2             |      9      |
|  Product C  |            1             |      7      |
+-------------+--------------------------+-------------+

订单表

+-----------+-----+-----------+
|  ID |   User ID |  Total    |
+-----------------+-----------+
|  1  |     1     |    500    |
|  2  |     1     |    200    |
|  3  |     2     |    100    |
+-----------------+-----------+

订单项目表

+-----------+-----+-------------+------------+
|  ID |  Order ID |  Product ID |  Quantity  |
+-----------------+-------------+------------+
|  1  |     1     |      1      |     4      |
|  2  |     1     |      2      |     2      |
|  3  |     2     |      3      |     1      |
|  4  |     3     |      1      |     1      |
+-----------------+-------------+------------+

产品表

+-----+-------------+-----------------+
|  ID |     Name    |   Total Stock   |
+-------------------+-----------------+
|  1  |  Product A  |        8        |
|  2  |  Product B  |        9        | 
|  3  |  Product C  |        7        |
+-----+-------------+-----------------+

编辑：

我已经将我的查询更新到这个并且它正在工作，现在我只需要像上面的预期输出一样显示产品名称和库存。

SELECT o.id, o.name, op.item_id, o.status, SUM( op.quantity ) as quantity
FROM  `orders` o JOIN 
      order_items op
      ON op.order_id = o.id
WHERE o.status = 'Completed'
GROUP BY op.item_id, o.id
ORDER BY o.id, op.item_id;

标签： mysql

Answer 1

不确定您的列是如何命名的，但这应该是它的要点。

在订购商品上加入了商品，这样就可以选择商品信息了。

SELECT p.product_name, SUM(op.quantity) AS quantity, p.total_stock 
FROM  `orders` o 
JOIN 
      order_items op
      ON op.order_id = o.id
LEFT JOIN products p ON p.id = op.item_id      
WHERE o.status = 'Completed'
GROUP BY p.id
ORDER BY p.product_name;