可以在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:

我需要在WHERE子句中使用别名，但它总是告诉我这是一个未知列。有什么办法解决这个问题吗?我需要选择评级高于x的记录。评级按以下别名计算:

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

4 个解决方案

#1

190

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

你可以用have子句，它可以看到别名。

 HAVING avg_rating>5

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

但是在where子句中，你需要重复你的表达。

 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.

但是!并不是所有的表达式都被允许——使用像SUM这样的聚合函数是行不通的，在这种情况下，您需要使用一个have子句。

From the MySQL Manual:

从MySQL手册:

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”.

不允许引用WHERE子句中的列别名，因为在执行WHERE子句时可能还不能确定列值。参见B.1.5.4节“列别名的问题”。

#2

Dunno if this works in mysql but using sqlserver you can also just wrap it like:

不知道，如果这在mysql中有效，但是使用sqlserver，你也可以这样包装:

select * from (
  -- your original query
  select .. sum(reviews.rev_rating)/count(reviews.rev_id) as avg_rating 
  from ...) Foo
where Foo.avg_rating ...

#3

This question is quite old and one answer already gained 160 votes...

这个问题由来已久，一个答案已经获得了160票。

Still I would make this clear: The question is actually not about whether alias names can be used in the WHERE clause.

但我还是要说明这一点:问题实际上不是关于是否可以在WHERE子句中使用别名。

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

is an aggregation. In the WHERE clause we restrict records we want from the tables by looking at their values. sum(reviews.rev_rating) and count(reviews.rev_id), however, are not values we find in a record; they are values we only get after aggregating the records.

是一个聚合。在WHERE子句中，我们通过查看表的值来限制我们想从表中获取的记录。sum(review .rev_rating)和count(review .rev_id)不是我们在记录中找到的值;它们是我们聚合记录后才获得的值。

So WHERE is inappropriate. We need HAVING, as we want to restrict result rows after aggregation. It can't be

那是不合适的。我们需要，因为我们想在聚合之后限制结果行。它不能

WHERE avg_rating > 10

nor

也不

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

hence.

因此。

HAVING sum(reviews.rev_rating) / count(reviews.rev_id) > 10

on the other hand is possible and complies with the SQL standard. Whereas

另一方面是可能的，并符合SQL标准。而

HAVING avg_rating > 10

is only possible in MySQL. It is not valid SQL according to the standard, as the SELECT clause is supposed to get executed after HAVING. From the MySQL docs:

只能在MySQL中使用。根据标准，它不是有效的SQL，因为SELECT子句应该在拥有之后执行。从MySQL文档:

Another MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list.

标准SQL的另一个MySQL扩展允许在“有”子句中引用在选择列表中别名的表达式。

The MySQL extension permits the use of an alias in the HAVING clause for the aggregated column

MySQL扩展允许在聚合列的have子句中使用别名

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

#4

It your query is static, you can define it as a view then you can use that alias in the where clause while querying the view.

如果查询是静态的，那么可以将其定义为视图，然后可以在查询视图时使用where子句中的别名。

#1

190