你可以在mysql的WHERE子句中使用别名吗?

2022-01-30 00:00:00 sql mysql having having-clause

我需要在 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".

相关文章