数据库基础SQL

Question

显示分批装运的订单，这意味着发运的数量少于所订购的数量。显示SalesOrderNumber、PartID、订单数量和SalesOrderNumber、PartID按顺序发送的数量。

帮助:所有这些信息来自两个表: SalesOrderPart和ShipmentPart。使用WHERE语句查找SalesOrderPart数量大于( where‘>’>) ShipmentPart数量的行。

我已经附上了一张ERD的图片

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