mysql - 添加订单数量以给出每个产品的订购数量

Question

我有以下 SQL，其中列出了我的所有订单行以及该订单行中的产品数量。

SELECT p.externalReference as KitNumber, 
       p.description as Kitname,
      (SELECT quantity 
         FROM order_line 
        WHERE id = ol.id) as KitQtyShipped
  FROM order_line ol 
  JOIN shipment s ON ol.shipmentId = s.id
  JOIN product p ON ol.productId = p.id
  JOIN order_item oi ON s.orderItemId = oi.id
 WHERE s.state = 'despatched' 
   AND ol.quantity != 0
 ORDER BY KitNumber DESC;

它给出的输出如下

+-----------+--------------------------+---------------+
| KitNumber | Kitname                  | KitQtyShipped |
+-----------+--------------------------+---------------+
| 269588    | product2                 |            30 |
| 269291    | product1                 |             3 |
| 269291    | product1                 |             1 |
| 269291    | product1                 |             2 |
| 269291    | product1                 |             3 |
| 269291    | product1                 |             3 |
| 269291    | product1                 |             2 |
| 269291    | product1                 |             1 |
| 269291    | product1                 |             4 |
| 269291    | product1                 |             1 |
| 269291    | product1                 |             2 |
| 269291    | product1                 |             1 |
| 269291    | product1                 |             2 |
| 269291    | product1                 |             1 |
| 269291    | product1                 |             4 |
| 269291    | product1                 |             1 |
| 269291    | product1                 |             2 |
| 269291    | product1                 |             2 |
| 269291    | product1                 |             1 |
| 269291    | product1                 |             2 |
| 269291    | product1                 |             1 |
| 269291    | product1                 |             2 |
| 269291    | product1                 |             1 |
| 269290    | product3                 |             2 |
| 269290    | product3                 |             3 |
| 269290    | product3                 |             2 |
| 269290    | product3                 |            10 |
| 269290    | product3                 |             3 |
| 269290    | product3                 |            26 |
| 269290    | product3                 |             1 |
| 269290    | product3                 |            11 |
| 269290    | product3                 |             5 |

我需要的是按 KitNumber 对其进行分组，以便为​​每个套件提供一行，并包含 kitsQtyShipped 的总数。即上面为每个套件添加的所有行

我已经尝试过:

SELECT p.externalReference as KitNumber, 
       p.description as Kitname,
       COUNT(*) * (SELECT quantity 
                     FROM order_line 
                    WHERE id = ol.id)  as KitQtyShipped
  FROM order_line ol 
  JOIN shipment s ON ol.shipmentId = s.id
  JOIN product p ON ol.productId = p.id
  JOIN order_item oi ON s.orderItemId = oi.id
 WHERE s.state = 'despatched' 
   AND ol.quantity != 0
 GROUP BY ol.productId
 ORDER BY KitNumber DESC;

但这并没有给出正确的值

我希望得到如下输出:

+-----------+--------------------------+--------------------------+
| KitNumber | Kitname                  | KitQtyShipped            |
+-----------+--------------------------+--------------------------+
| 269588    | product2                 |(Sum of all orderlines) 30|
| 269291    | product1                 |                       45 |
| 269290    | product3                 |                       63 |

Answer 1

我认为它可能比你期望的更容易(请检查它，因为我无法测试它，因为我还没有创建表脚本):我刚刚在您的第一个查询中添加了 SUM、GROUP BY:

SELECT p.externalReference as KitNumber, 
       p.description as Kitname,
       SUM(ol.quantity) as KitQtyShipped
  FROM order_line ol 
  JOIN shipment s ON ol.shipmentId = s.id
  JOIN product p ON ol.productId = p.id
  JOIN order_item oi ON s.orderItemId = oi.id      
 WHERE s.state = 'despatched' 
   AND ol.quantity != 0
 GROUP BY p.externalReference, p.description
 ORDER BY KitNumber DESC;