mysql - 选择促销价

Question

目标

获取产品的最低价格。

问题

为了说明我的问题:

Row 1

• Product_Id = 1
• Product_Name = "iPhone 5"
• Market_Name = "Walmart"
• Product_Original_Price = "359.00"
• Product_Promotional_Price = "319.00"
• Product_State = 1 (is on offer)

Row 2

• Product_Id = 1
• Product_Name = "iPhone 5"
• Market_Name = "Apple"
• Product_Original_Price = "359.00"
• Product_Promotional_Price = "0.00"
• Product_State = 0 (isn't on offer)

Row 3

• Product_Id = 1
• Product_Name = "iPhone 5"
• Market_Name = "BestBuy"
• Product_Original_Price = "359.00"
• Product_Promotional_Price = "299.00"
• Product_State = 1 (is on offer)

下一个主题(What I have)的查询返回零作为上述问题的最佳价格 — 但最佳价格是 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将值设置为特定类型。

完美地工作!!谢谢!!

Answer 1

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

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