选择促销价格

目标

获得产品的最低价格。

问题

为了说明我的问题:

第1行

  • Product_Id = 1
  • Product_Name =“iPhone 5”
  • Market_Name =“沃尔玛”
  • Product_Original_Price =“359.00”
  • Product_Promotional_Price =“319.00”
  • Product_State = 1(正在提供)
  • 第2行

  • Product_Id = 1
  • Product_Name =“iPhone 5”
  • Market_Name =“Apple”
  • Product_Original_Price =“359.00”
  • Product_Promotional_Price =“0.00”
  • Product_State = 0(不提​​供)
  • 第3行

  • Product_Id = 1
  • Product_Name =“iPhone 5”
  • Market_Name =“BestBuy”
  • Product_Original_Price =“359.00”
  • Product_Promotional_Price =“299.00”
  • Product_State = 1(正在提供)
  • 下一个主题( 我有什么 )的查询将返回零作为上述问题的最佳价格 - 但BestBuy的最佳价格是299.00 ,因为零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`;
    

    查询返回的内容:

    SQL结果

    我已经尝试过了:

    考虑到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发布了这个答案,并且用它做了这个:

    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)
    

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

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

    上一篇: Select Promotional Price

    下一篇: onPrepareOptionsMenu is not called