mysql是否支持别名,可以在mysql的WHERE子句中使用别名吗？

I need to use an alias in the WHERE clause, but It keeps telling me that its an unknown column. Is there any way to get around this issue? I need to select records that have a rating higher than x. Rating is calculated as the following alias:

sum(reviews.rev_rating)/count(reviews.rev_id) as avg_rating

解决方案

You could use a HAVING clause, which can see the aliases, e.g.

HAVING avg_rating>5

but in a where clause you'll need to repeat your expression, e.g.

WHERE (sum(reviews.rev_rating)/count(reviews.rev_id))>5

BUT! Not all expressions will be allowed - using an aggregating function like SUM will not work, in which case you'll need to use a HAVING clause.

It is not allowable to refer to a

column alias in a WHERE clause,

because the column value might not yet

be determined when the WHERE clause

is executed. See Section B.1.5.4,

“Problems with Column Aliases”.