你可以在 mysql 的 WHERE 子句中使用别名吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/200200/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can you use an alias in the WHERE clause in mysql?
提问by Paul Dixon
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
回答by Paul Dixon
You could use a HAVING clause, which cansee the aliases, e.g.
您可以使用 HAVING 子句，它可以看到别名，例如
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 这样的聚合函数将不起作用，在这种情况下，您需要使用 HAVING 子句。
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 节，“列别名问题”。
回答by Torbj?rn Gyllebring
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 ...
回答by Thorsten Kettner
This question is quite old and one answer already gained 160 votes...
这个问题很老了，一个答案已经获得了160票......
Still I would make this clear: The question is actually notabout whether alias names can be used in the WHEREclause.
不过我还是要说清楚：问题实际上不在于是否可以在WHERE子句中使用别名。
sum(reviews.rev_rating) / count(reviews.rev_id) as avg_rating
is an aggregation. In the WHEREclause 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(reviews.rev_rating)并且count(reviews.rev_id)，然而，不重视我们的记录找到; 它们是我们只有在汇总记录后才能获得的值。
So WHEREis inappropriate. We need HAVING, as we want to restrict result rows after aggregation. It can't be
所以WHERE是不合适的。我们需要HAVING，因为我们想在聚合后限制结果行。不可能
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 SELECTclause is supposed to get executed after HAVING. From the MySQL docs:
仅在 MySQL 中可能。根据标准，它不是有效的 SQL，因为该SELECT子句应该在HAVING. 来自 MySQL 文档：
Another MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list.
The MySQL extension permits the use of an alias in the HAVING clause for the aggregated column
标准 SQL 的另一个 MySQL 扩展允许在 HAVING 子句中引用选择列表中的别名表达式。
MySQL 扩展允许在聚合列的 HAVING 子句中使用别名
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
回答by anson
SELECT * FROM (SELECT customer_Id AS 'custId', gender, age FROM customer
WHERE gender = 'F') AS c
WHERE c.custId = 100;
回答by alpere
If 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 子句中使用该别名。
