mysql - 无法使用别名作为字段

Question

考虑像这样开始查询:

SELECT

(SELECT ps2.price FROM product_special ps2 
WHERE p.product_id = ps2.product_id 
AND ((ps2.date_start = '0000-00-00' OR ps2.date_start < NOW()) 
AND (ps2.date_end = '0000-00-00' OR ps2.date_end > NOW()))
ORDER BY ps2.priority ASC, ps2.price ASC LIMIT 1) AS special,

-- here is the problem, i can't use subquery alias special as field that i can
-- calculate with

IF(tr.rate ,CEILING(special + (special * tr.rate / 100 )),special) AS final_price

-- ending not important

有没有办法可以在此查询中使用别名作为字段？我已经在这里遇到了一些类似的问题，但没有一个对我有帮助。

Answer 1

这是 MySQL 中 Sub Select 查询中的一个众所周知的错误，无法用于计算!您有一个解决方法:

SELECT

(SELECT ps2.price FROM product_special ps2 
WHERE p.product_id = ps2.product_id 
AND ((ps2.date_start = '0000-00-00' OR ps2.date_start < NOW()) 
AND (ps2.date_end = '0000-00-00' OR ps2.date_end > NOW()))
ORDER BY ps2.priority ASC, ps2.price ASC LIMIT 1) AS special,
IF(tr.rate ,CEILING((SELECT ps2.price FROM product_special ps2 
WHERE p.product_id = ps2.product_id 
AND ((ps2.date_start = '0000-00-00' OR ps2.date_start < NOW()) 
AND (ps2.date_end = '0000-00-00' OR ps2.date_end > NOW()))
ORDER BY ps2.priority ASC, ps2.price ASC LIMIT 1)+ ((SELECT ps2.price FROM product_special ps2 
WHERE p.product_id = ps2.product_id 
AND ((ps2.date_start = '0000-00-00' OR ps2.date_start < NOW()) 
AND (ps2.date_end = '0000-00-00' OR ps2.date_end > NOW()))
ORDER BY ps2.priority ASC, ps2.price ASC LIMIT 1)* tr.rate / 100 )),(SELECT ps2.price FROM product_special ps2 
WHERE p.product_id = ps2.product_id 
AND ((ps2.date_start = '0000-00-00' OR ps2.date_start < NOW()) 
AND (ps2.date_end = '0000-00-00' OR ps2.date_end > NOW()))
ORDER BY ps2.priority ASC, ps2.price ASC LIMIT 1)) AS final_price

我知道这可能很疯狂，但请检查它是否有效!