mysql - 选择项目的订购日期 SQL

Question

我有以下表格:

• 订单(orderID、日期、shipID)

• 运费(shipID、价格)

• PO(prodID、orderID、金额)

• 价格(prodID，fromDate，价格)

我需要检索每个订单 ID 和总价，包括订单商品的成本 + 购买的运费。商品的成本必须是订单日期的商品成本，而不是最近日期。 Price 表存储从特定日期(fromDate 属性)开始的产品的不同价格。因此，第 d 天的产品价格是具有最大 fromDate-value f 且 f ≤ d 的条目中的价格。我的代码不返回所有 orderID，只返回一些。有什么想法吗？

SELECT o.orderId, SUM(pr.price) + SUM(s.price) orderCost
FROM [Order] o 
INNER JOIN Shipping s ON o.shipId = s.shipId
INNER JOIN PO po ON o.orderId = po.orderId 
INNER JOIN 
    (SELECT TOP 1 price, prodID FROM Price pr WHERE 
        fromDate <= (SELECT MAX([date]) FROM [Order])
     ) pr ON pr.prodID  = po.prodID
GROUP BY  o.orderId;

SELECT "Order".orderId
FROM "Order"

Answer 1

我怀疑您的某些商品没有运费，请改用left join。

SELECT o.orderId, SUM(coalesce(pr.price, 0)) + SUM(coalesce(s.price, 0)) orderCost
FROM [Order] o 
LEFT JOIN Shipping s ON o.shipId = s.shipId
INNER JOIN PO po ON o.orderId = po.orderId 
LEFT JOIN 
    (SELECT TOP 1 price, prodID FROM Price pr WHERE 
        fromDate <= (SELECT MAX([date]) FROM [Order])
     ) pr ON pr.prodID  = po.prodID
GROUP BY  o.orderId;