数据库基础SQL

Question

显示部分运输的订单，这意味着运输的数量小于订购数量的数量。显示Salesordernumber，Partid，订购的数量，并由Salesordernumber（Partid）按顺序运送数量。

帮助：所有这些信息都来自两个表：SalesorderPart和ShipmentPart。使用Where语句查找SalesordorderPart数量大于（使用'＆gt;'）装运代码数量的行。

我包含了erd

。

Answer 1

希望它对您有用！

SELECT sop.SalesOrderNumber, sop.PartID, sop.Quantity as Quantity_ordered, sp.Quantity as Quantity_shipped
FROM SalesOrder so
LEFT JOIN Shipment s ON s.SalesOrderNumber = so.SalesOrderNumber
LEFT JOIN SalesOrderPart sop ON sop.SalesOrderNumber = so.SalesOrderNumber
LEFT JOIN ShipmentPart sp ON sp.SalesOrderNumber = so.SalesOrderNumber
WHERE sop.Quantity > Quantity_shipped
GROUP BY 1,2