mysql - 如何从 if 语句中的多个选择中将值返回到 View ？

Question

我是 SQL 的新手，我想弄清楚如何创建一个 View ，在该 View 中，产品价格会相应地发生变化——是否有折扣。我该怎么做？我想我走错了方向。可能与 JOINS 有关？欢迎提出任何建议!

CREATE VIEW AvailableProductsAndTheirPrices AS
SELECT Product.Name,

Product.Price,

InStock.Quantity, Store.Name, Store.Adress
FROM Product, InStock, Store
WHERE
InStock.Quantity > 0 AND
Product.PID = InStock.PID AND -- PID as in Product ID 
InStock.FID = Store.FID -- FID as in Store ID

GO

Product.Price 行将替换为如下内容:

(SELECT IIF( (SELECT Product.PID NOT IN (SELECT Product.PID FROM Discount)),
             (SELECT Product.Price FROM .. how does this work?[1]), -- if there is no discount
             (SELECT Product.Price * Discount.Discount -- otherwise apply discount to price
              FROM Product, Discount
              WHERE Product.PID = Discount.PID AND GETDATE() <= Discount.To)
           ) 
),

[1] 如果我没记错的话，它会返回第一行的值。

Answer 1

首先，我建议使用标准的 JOIN 语法。然后您可以对 discount 表使用 OUTER JOIN。

CREATE VIEW AvailableProductsAndTheirPrices AS
SELECT P.Name,
       P.Price * COALESCE(D.Discount,1),
       I.Quantity, 
       S.Name, 
       S.Adress
FROM Product P
       INNER JOIN InStock I ON P.PID = I.PID
       INNER JOIN Store S ON P.FID = I.FID
       LEFT JOIN Discount D ON P.PID = D.PID AND D.To > Now() 
WHERE I.Quantity > 0  
GOm