可以在mysql的WHERE子句中使用别名吗? [英] Can you use an alias in the WHERE clause in mysql?
问题描述
我需要在WHERE子句中使用别名,但是它一直告诉我它是一个未知列.有什么办法可以解决这个问题?我需要选择评级高于x的记录.评分是根据以下别名计算的:
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
推荐答案
您可以使用HAVING子句,该子句可以看到别名,例如
You could use a HAVING clause, which can see the aliases, e.g.
HAVING avg_rating>5
,但是在where子句中,您需要重复您的表达式,例如
but in a where clause you'll need to repeat your expression, e.g.
WHERE (sum(reviews.rev_rating)/count(reviews.rev_id))>5
但是!并非所有的表达式都被允许-使用像SUM这样的聚合函数将不起作用,在这种情况下,您将需要使用HAVING子句.
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.
从 MySQL手册:
不允许引用 WHERE子句中的列别名, 因为列值可能还没有 在WHERE子句时确定 被执行.参见 B.1.5.4节, 列别名问题" .
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".
这篇关于可以在mysql的WHERE子句中使用别名吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!