php - 按价格和折扣价格排序

Question

我有下表:

product_id | price | discountPrice
    1      |  15   | 12
    2      |  13   | 0
    3      |  20   | 6

我想按折扣价对结果进行排序，但如果某些 product_id 的 discountPrice 等于 0，则此 product_id 应按价格排序。

预期结果:

product_id | price | discountPrice
    3      |  20   | 6
    1      |  15   | 12
    2      |  13   | 0

Answer 1

I want to sort result by discount Price, but if some product_id have a discountPrice equal to 0, this product_id should be sort by price.

SELECT * FROM `table` ORDER BY CASE WHEN `discountPrice` = 0 THEN `price` ELSE `discountprice` END DESC