mysql - 选择促销价

目标
获得产品的最低价格。
问题
为了说明我的问题:

对下一个主题(我所拥有的)的查询返回的是我作为上述问题的最佳价格的零-但最佳价格是299.00的BestBuy，因为Product_Promotional_Price的零意味着该产品不提供。
是)我有的

SELECT
  MIN(LEAST(`Product_Original_Price`, `Product_Promotional_Price`)) as `minProductPrice`
[...]

细节
我的查询:

    SELECT  `pr`.`Product_Id` as `productId`,
    `pr`.`Product_Name` as `productName`,
    ROUND(CAST(MIN(`map`.`Product_Original_Price`) AS DECIMAL)/100,2)
      as `minProductPrice`,
    `prm`.`Product_Measure_Name` as `measureName`,
    `prm`.`Product_Measure_Shortname` as `measureShortName`,
    `pri`.`Product_Thumbnail_Image_Url` as `thumbnailUrl`,
    `pr`.`Product_Markets_Quantity` as `numberOfMarketsThatHaveThisProduct`
FROM `bm_market_products` as `map`
JOIN `bm_products` as `pr` ON `map`.`Product_Id` = `pr`.`Product_Id`
JOIN `bm_products_category_relationship` as `car` ON `pr`.`Product_Id` =
      `car`.`Product_Id`
JOIN `bm_product_categories` as `ca` ON `car`.`Category_Id` = `ca`.`Category_Id`
JOIN `bm_products_measure_relationship` as `prmr` ON `pr`.`Product_Id` =
      `prmr`.`Product_Id`
JOIN `bm_product_measures` as `prm` ON `prmr`.`Measure_Id` =
      `prm`.`Product_Measure_Id`
JOIN `bm_products_images` as `pri` ON `pr`.`Product_Id` = `pri`.`Product_Id`
WHERE ("" IS NULL OR `map`.`Product_State` = 0)
AND ("" IS NULL OR `ca`.`Category_Id` = 14)
GROUP BY `map`.`Product_Id`;

查询返回什么:

我已经尝试过的东西:
考虑到Product_State决定了是否提供某种产品，请遵循以下片段:

SELECT  `pr`.`Product_Id` as `productId`,
    `pr`.`Product_Name` as `productName`,
    (IF(`map`.`Product_State` <> 0) THEN
      MIN(LEAST(`Product_Original_Price`, `Product_Promotional_Price`))
    ELSE (`map`.Product_Original_Price) as `minProductPrice`,
    `prm`.`Product_Measure_Name` as `measureName`,
    `prm`.`Product_Measure_Shortname` as `measureShortName`,
    `pri`.`Product_Thumbnail_Image_Url` as `thumbnailUrl`,
    `pr`.`Product_Markets_Quantity` as `numberOfMarketsThatHaveThisProduct`
[...]

您能看到 IF/THEN/ELSE 吗？这是与上一个查询相关的内容。
上面的查询不起作用-语法不正确，我知道，但这只是为了说明。
解决方案
Gordon Linoff发布了this answer，并使用它来完成此任务:

SELECT  [...]
    ROUND(CAST(MIN(CASE WHEN `map`.`Product_Promotional_Price` = 0 THEN `map`.`Product_Original_Price`
            ELSE LEAST(`map`.`Product_Promotional_Price`, `map`.`Product_Original_Price`)
       end) AS DECIMAL)/100,2) as `minProductPrice`,
        [...]

为了明确起见，我只是根据他的情况调整了他的[Gordon Linoff]语法- ROUND 舍入了数字，而 CAST 则将值设置为某种类型。
完美工作! 谢谢!

最佳答案

您需要修正获得最低价格的逻辑。案例陈述是最好的方法。这是一个例子:

select MIN(case when `Product_Promotional_Price` = 0 then `Product_Original_Price`
                else least(`Product_Promotional_Price`, `Product_Original_Price`)
           end)

关于mysql - 选择促销价，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17176917/