我在查询中有问题。我有表product
,stockRecord
和priceDetail
。我想展示所有产品。如果未在priceDetail
中为该产品定义价格,则价格为0.00;同样,如果未在stockRecord
表中定义数量,则数量应为0。
但是,如果在priceDetail
表中定义了价格,那么我们应该从表中获取最新价格
WM产品
BusinessUnit ProductCode Description SalableFlag
MASS 0001 Pen 1
MASS 0002 Computer 1
MASS 0003 Book 1
MASS 0004 Bottle 1
WMStockRecord
ProductCode AvailableQuantity
0001 10
0003 15
WMPriceDetail
ProductCode DateFrom DateTo Price
0001 10-10-2009 10-10-2011 100
0001 10-12-2009 10-10-2010 80
0001 12-12-2010 01-12-2011 120
0002 12-01-2010 '' 200
0004 12-12-2010 12-05-2011 100
我需要这样的产品清单:
BusinessUnit ProductCode Description SalableFlag Price AvailableQuantity
MASS 0001 Pen 1 120 10
MASS 0002 Computer 1 200 0
MASS 0003 Book 1 0.00 15
MASS 0004 Bottle 1 0.00 0
最佳答案
尝试使用子查询和左联接,如下所示:
SELECT P.ProductCode AS ProductCode,
P.Description AS ProductName,
P.SalableFlag AS Salable,
ISNULL(STK.AvailableQuantity, 0) AS Qty,
ISNULL((SELECT TOP 1 Price FROM WMPriceDetail
WHERE ProductCode = P.ProductCode ORDER BY DateTo DESC), 0) AS Price
FROM WMProduct P
LEFT JOIN WMStockRecord STK ON P.ProductCode = STK.ProductCode