MySQL Where 子句中的未知列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/153598/
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
Unknown Column In Where Clause
提问by
I have a simple query:
我有一个简单的查询：
SELECT u_name AS user_name FROM users WHERE user_name = "john";
I get Unknown Column 'user_name' in where clause. Can I not refer to 'user_name'in other parts of the statement even after select 'u_name as user_name'?
我明白了Unknown Column 'user_name' in where clause。'user_name'即使在 之后我也不能在声明的其他部分引用select 'u_name as user_name'吗？
回答by dacracot
SQL is evaluated backwards, from right to left. So the where clause is parsed and evaluate prior to the select clause. Because of this the aliasing of u_name to user_name has not yet occurred.
SQL 从右到左向后求值。所以 where 子句在 select 子句之前被解析和评估。因此，尚未发生 u_name 到 user_name 的别名。
回答by Septimus
What about:
关于什么：
SELECT u_name AS user_name FROM users HAVING user_name = "john";
回答by Paul Dixon
See the following MySQL manual page: http://dev.mysql.com/doc/refman/5.0/en/select.html
请参阅以下 MySQL 手册页：http: //dev.mysql.com/doc/refman/5.0/en/select.html
"A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses."
“可以使用 AS alias_name 为 select_expr 指定一个别名。别名用作表达式的列名，并且可以在 GROUP BY、ORDER BY 或 HAVING 子句中使用。”
(...)
（……）
It is not permissible 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.5.4.4, “Problems with Column Aliases”.
不允许在 WHERE 子句中引用列别名，因为在执行 WHERE 子句时可能尚未确定列值。请参见第 B.5.4.4 节“列别名问题”。
回答by Mark S.
select u_name as user_name from users where u_name = "john";
Think of it like this, your where clause evaluates first, to determine which rows (or joined rows) need to be returned. Once the where clause is executed, the select clause runs for it.
可以这样想，您的 where 子句首先评估，以确定需要返回哪些行（或连接的行）。一旦 where 子句被执行，select 子句就会为它运行。
To put it a better way, imagine this:
换一种更好的方式，想象一下：
select distinct(u_name) as user_name from users where u_name = "john";
You can't reference the first half without the second. Where always gets evaluated first, then the select clause.
如果没有第二部分，您就无法参考前半部分。哪里总是先求值，然后是 select 子句。
回答by Jon
If you're trying to perform a query like the following (find all the nodes with at least one attachment) where you've used a SELECT statement to create a new field which doesn't actually exist in the database, and try to use the alias for that result you'll run into the same problem:
如果您尝试执行如下查询（找到所有节点，至少有一个附件），您使用 SELECT 语句创建了一个实际上不存在于数据库中的新字段，并尝试使用该结果的别名你会遇到同样的问题：
SELECT nodes.*, (SELECT (COUNT(*) FROM attachments
WHERE attachments.nodeid = nodes.id) AS attachmentcount
FROM nodes
WHERE attachmentcount > 0;
You'll get an error "Unknown column 'attachmentcount' in WHERE clause".
您将收到错误“WHERE 子句中的未知列‘attachmentcount’”。
Solution is actually fairly simple - just replace the alias with the statement which produces the alias, eg:
解决方案实际上相当简单——只需用产生别名的语句替换别名，例如：
SELECT nodes.*, (SELECT (COUNT(*) FROM attachments
WHERE attachments.nodeid = nodes.id) AS attachmentcount
FROM nodes
WHERE (SELECT (COUNT(*) FROM attachments WHERE attachments.nodeid = nodes.id) > 0;
You'll still get the alias returned, but now SQL shouldn't bork at the unknown alias.
您仍然会返回别名，但现在 SQL 不应该在未知别名处出错。
回答by M Khalid Junaid
Your defined aliasare not welcomed by the WHEREclause you have to use the HAVINGclause for this
您的定义alias不受WHERE条款的欢迎，您必须为此使用该HAVING条款
SELECT u_name AS user_name FROM users HAVING user_name = "john";
OR you can directly use the original column name with the WHERE
或者您可以直接使用原始列名 WHERE
SELECT u_name AS user_name FROM users WHERE u_name = "john";
Same as you have the result in user defined alias as a result of subquery or any calculation it will be accessed by the HAVINGclause not by the WHERE
与作为子查询或任何计算的结果在用户定义别名中的结果相同，它将由HAVING子句访问，而不是由子句访问WHERE
SELECT u_name AS user_name ,
(SELECT last_name FROM users2 WHERE id=users.id) as user_last_name
FROM users WHERE u_name = "john" HAVING user_last_name ='smith'
回答by David Aldridge
Either:
任何一个：
SELECT u_name AS user_name
FROM users
WHERE u_name = "john";
or:
或者：
SELECT user_name
from
(
SELECT u_name AS user_name
FROM users
)
WHERE u_name = "john";
The latter ought to be the same as the former if the RDBMS supports predicate pushing into the in-line view.
如果 RDBMS 支持将谓词推入内联视图，则后者应该与前者相同。
回答by Steven A. Lowe
corrected:
更正：
SELECT u_name AS user_name FROM users WHERE u_name = 'john';
回答by Per Hornsh?j-Schierbeck
No you need to select it with correct name. If you gave the table you select from an alias you can use that though.
不，您需要使用正确的名称选择它。如果您提供了从别名中选择的表格，则可以使用它。
回答by Jarrett Meyer
No you cannot. user_name is doesn't exist until return time.
你不能。user_name 直到返回时间才存在。
