mysql - MySql:在复杂的if语句中更改源表

我不确定这是否可行。但是我有以下sql，它根据产品的特殊价格向product_filter表插入一些值。

INSERT INTO product_filter (product_id,filter_id)
SELECT product_id,
  (CASE WHEN price < 100 then 1
  WHEN price >= 100 AND price < 500 then 2
  WHEN price >= 500 AND price < 1000 then 3
  WHEN price >= 1000 AND price < 1500 then 4
  WHEN price >= 1500 AND price < 2000 then 5
  WHEN price >= 2000 AND price < 2500 then 50
  WHEN price >= 2500 AND price < 3000 then 6
  ELSE 51 END) AS filter_id
FROM product_special;

我想修改上面的脚本，因此，如果没有为产品设置特殊价格，它只会从主product表中读取常规价格。由于这将在大型产品数据库上运行，因此我可能希望将两个脚本组合在一起，而不是在某些产品上手动运行其他脚本。

谢谢你的帮助。

最佳答案

INSERT INTO product_filter (product_id,filter_id)

  SELECT product_id,
  (CASE WHEN price < 100 then 1
  WHEN price >= 100 AND price < 500 then 2
  WHEN price >= 500 AND price < 1000 then 3
  WHEN price >= 1000 AND price < 1500 then 4
  WHEN price >= 1500 AND price < 2000 then 5
  WHEN price >= 2000 AND price < 2500 then 50
  WHEN price >= 2500 AND price < 3000 then 6
  ELSE 51 END) AS filter_id
  FROM product_special
  union
  SELECT product_id,
  (CASE WHEN price < 100 then 1
  WHEN price >= 100 AND price < 500 then 2
  WHEN price >= 500 AND price < 1000 then 3
  WHEN price >= 1000 AND price < 1500 then 4
  WHEN price >= 1500 AND price < 2000 then 5
  WHEN price >= 2000 AND price < 2500 then 50
  WHEN price >= 2500 AND price < 3000 then 6
  ELSE 51 END) AS filter_id
  FROM product where product_id not in
  (select product_id from product_special)

上面的查询假定product_id表中有price和product（作为标准/正常价格）列。这里的想法是从product_special表中获取所有现有产品及其特殊价格，并向其中添加product_special表中未包含但product表中存在的产品及其标准。 /常规价格。

关于mysql - MySql:在复杂的if语句中更改源表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22844608/