mysql - 如何通过CASE语句中的变量对结果进行排序

如何通过promotion_price语句中的变量CASE对结果进行排序。我得到的错误是：

Unknown column 'promotion_price' in 'where clause'

这是我的查询：

SELECT product.*,
    CASE
        WHEN promotion.type=1 THEN product.price - (product.price * promotion.value/100)
        WHEN promotion.type=2 THEN product.price - promotion.value
        ELSE product.price
    END promotion_price
FROM product LEFT JOIN page ON product.category_id=page.id
LEFT JOIN promotion_product ON product.id=promotion_product.main_product_id
LEFT JOIN promotion ON promotion_product.promo_id=promotion.id
WHERE (page.id = 12 OR page.id_in = 12)
AND promotion_price >= 49.50
AND promotion_price <= 108.89
GROUP BY product.id
ORDER BY  promotion_price ASC

谢谢！

最佳答案

您不能在必须重复代码的情况下使用列别名

SELECT product.*,
    CASE
        WHEN promotion.type=1 THEN product.price - (product.price * promotion.value/100)
        WHEN promotion.type=2 THEN product.price - promotion.value
        ELSE product.price
    END promotion_price
FROM product LEFT JOIN page ON product.category_id=page.id
LEFT JOIN promotion_product ON product.id=promotion_product.main_product_id
LEFT JOIN promotion ON promotion_product.promo_id=promotion.id
WHERE (page.id = 12 OR page.id_in = 12)
AND (CASE
        WHEN promotion.type=1 THEN product.price - (product.price * promotion.value/100)
        WHEN promotion.type=2 THEN product.price - promotion.value
        ELSE product.price
    END ) >= 49.50
AND (CASE
        WHEN promotion.type=1 THEN product.price - (product.price * promotion.value/100)
        WHEN promotion.type=2 THEN product.price - promotion.value
        ELSE product.price
    END) <= 108.89
GROUP BY product.id
ORDER BY  promotion_price ASC

在评估select列别名之前先评估where条件，因此在查询where条件时查询引擎不知道select列别名