添加订单产品订购数量已订购每件商品

问题描述：

我下面的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 |

输出I所需要的是该基团通过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 |

如果您不告诉我们什么是正确的价值，很难帮助您。向我们展示数据库架构，示例数据，当前和预期输出。 \t请阅读[**如何提问**]（http://stackoverflow.com/help/how-to-ask） \t \t这里是一个伟大的地方[** START **] （http://spaghettidba.com/2015/04/24/how-to-post-at-sql-question-on-a-public-forum/）来了解如何提高您的问题质量并获得更好的答案。 \t [**如何创建一个最小，完整和可验证的示例**]（http://stackoverflow.com/help/mcve） –

@Zac根据您发布的样本数据发布所需的输出 – etsa

@etsa已添加想要的 –

答

我认为这可能是比预期更容易（请检查一下，因为我无法测试它，因为我还没有创建表脚本）：我刚添加总之，关于你的第一个查询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;

查看修订后的答案 – etsa

您可能不需要'AND ol.quantity！= 0' –

答

我想你需要：

SELECT p.externalReference as KitNumber, 
     p.description as Kitname, 
     SUM(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 -- no need this to SUM 
GROUP BY p.externalReference, p.description 
ORDER BY KitNumber DESC

但是再次没有源数据和预期的输出只是一个猜测。我看你不用order_item。这也应该工作：

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

检查编辑删除未使用的表 –

答

更容易设置为临时表，然后总结和计数那里。如果某些MYSQL语法不正确，请原谅我。

编辑：如果您正在查看KitShipped的总计，您希望使用SUM GROUP BY KitNumber而不是COUNT。

--Create a temp table for your SQL query 
CREATE TEMPORARY TABLE IF NOT EXISTS SumTable AS 
(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) 

--Do your sum here. 

Select Sum(KitQtyShipped) as TotalShipped, KitNumber from Sumtable 
Group by KitNumber

添加订单产品订购数量已订购每件商品

相关推荐