mySQL 按参数排序

Question

这个问题已经有答案了:

MySQL - ORDER BY values within IN() (6 个回答)

sql ORDER BY multiple values in specific order? (12 个回答)

已关闭 3 年前。

有一个sql请求:

SELECT
`e` .*,
`cat_index`.`position` AS `cat_index_position`,
`price_index`.`price`,
`price_index`.`tax_class_id`,
`price_index`.`final_price`,
IF(price_index.tier_price IS NOT NULL,
LEAST(price_index.min_price, price_index.tier_price),
price_index.min_price) AS `minimal_price`,
`price_index`.`min_price`,
`price_index`.`max_price`,
`price_index`.`tier_price`
FROM
`catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index_store2` AS `cat_index` ON
cat_index.product_id = e.entity_id
AND cat_index.store_id = '2'
AND cat_index.visibility IN(2,4)
AND cat_index.category_id = '153'
INNER JOIN `catalog_product_index_price` AS `price_index` ON
price_index.entity_id = e.entity_id
AND price_index.website_id = '3'
AND price_index.customer_group_id = '1'
WHERE
((`e`.`sku` IN('96005', '5806', '96004','PROD91','PROD187')))
    AND (e.created_in <= '1548056609')
    AND (e.updated_in > '1548056609')

我得到这个结果:

但我需要像 IN 内那样按顺序排列:96005','5806','96004','PROD91','PROD187'

Answer 1

使用“case”语句例如:

SELECT * FROM 
...
ORDER BY case when <your_value> = '96005' then 1
          when <your_value> = '5806' then 2
          when <your_value> = '96004' then 3
          when <your_value> = 'PROD91' then 4
          when <your_value> = 'PROD187' then 5