Select Promotional Price

The goal

Get the lowest price of a product.

The problem

To illustrate my problem:

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)

The query of the next topic ( What I have ) is returning me zero as the best price of the problem illustrated above — but the best price is 299.00 , by BestBuy , because zero at Product_Promotional_Price means that the product isn't on offer.

What I have

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

Details

My query:

    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`;

What the query returns:

What I already have tried:

Considering that Product_State determines whether a product is on offer or not, follow this fragment:

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`
[...]

Can you see the IF/THEN/ELSE ? This is what has been added in relation to the previous query.

The above query doesn't work — syntax isn't correct, I know, but it was just to illustrate.

The solution

Gordon Linoff posted this answer and with it, I made this:

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`,
        [...]

To clarify, I just adapted his [Gordon Linoff] syntax to my scenario — with ROUND to rounding numbers and CAST to set a value as a certain type.

Worked perfectly!! Thanks!!

---

You need to fix your logic for getting the lowest price. A case statement is the best way. Here is an example:

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

---

把where Product_Original_Price!=0 and Product_Promotional_Price!=0放到最后;

链接地址: http://www.djcxy.com/p/71986.html

上一篇: 我完全不理解Angular数据绑定

下一篇: 选择促销价格