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

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

where条件是在计算select列别名之前计算的，因此在计算where条件时，查询引擎不知道select列别名

我们知道，我们不能在单个sql中使用where子句中的inline列，您可以在subquery中尝试下面的方法。

SELECT * 
FROM
    (SELECT 
         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,
        product.*
    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)
    GROUP BY 
        product.id) s
WHERE
    promotion_price >= 49.50 
    AND promotion_price <= 108.89
ORDER BY  
    promotion_price ASC