在 MySQL 查询的 WHERE 子句中使用列别名会产生错误

Using column alias in WHERE clause of MySQL query produces an error

提问by James

The query I'm running is as follows, however I'm getting this error:

我正在运行的查询如下，但是我收到此错误：

#1054 - Unknown column 'guaranteed_postcode' in 'IN/ALL/ANY subquery'

#1054-“IN/ALL/ANY 子查询”中的未知列“guaranteed_postcode”

SELECT `users`.`first_name`, `users`.`last_name`, `users`.`email`,
SUBSTRING(`locations`.`raw`,-6,4) AS `guaranteed_postcode`
FROM `users` LEFT OUTER JOIN `locations`
ON `users`.`id` = `locations`.`user_id`
WHERE `guaranteed_postcode` NOT IN #this is where the fake col is being used
(
 SELECT `postcode` FROM `postcodes` WHERE `region` IN
 (
  'australia'
 )
)

My question is: why am I unable to use a fake column in the where clause of the same DB query?

我的问题是：为什么我不能在同一个数据库查询的 where 子句中使用假列？

回答by victor hugo

You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.

您只能在 GROUP BY、ORDER BY 或 HAVING 子句中使用列别名。

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

标准 SQL 不允许您在 WHERE 子句中引用列别名。强加此限制是因为在执行 WHERE 代码时，可能尚未确定列值。

Copied from MySQL documentation

复制自MySQL 文档

As pointed in the comments, using HAVING instead may do the work. Make sure to give a read at this WHERE vs HAVINGthough.

正如评论中所指出的，使用 HAVING 可以完成这项工作。请务必阅读此WHERE vs HAVING。

回答by rodion

As Victor pointed out, the problem is with the alias. This can be avoided though, by putting the expression directly into the WHERE x IN y clause:

正如维克多指出的，问题出在别名上。但是，可以通过将表达式直接放入 WHERE x IN y 子句来避免这种情况：

SELECT `users`.`first_name`,`users`.`last_name`,`users`.`email`,SUBSTRING(`locations`.`raw`,-6,4) AS `guaranteed_postcode`
FROM `users` LEFT OUTER JOIN `locations`
ON `users`.`id` = `locations`.`user_id`
WHERE SUBSTRING(`locations`.`raw`,-6,4) NOT IN #this is where the fake col is being used
(
 SELECT `postcode` FROM `postcodes` WHERE `region` IN
 (
  'australia'
 )
)

However, I guess this is very inefficient, since the subquery has to be executed for every row of the outer query.

但是，我想这是非常低效的，因为必须为外部查询的每一行执行子查询。

回答by Joni

Standard SQL (or MySQL) does not permit the use of column aliases in a WHERE clause because

标准 SQL（或 MySQL）不允许在 WHERE 子句中使用列别名，因为

when the WHERE clause is evaluated, the column value may not yet have been determined.

评估 WHERE 子句时，可能尚未确定列值。

(from MySQL documentation). What you can do is calculate the column value in the WHEREclause, save the value in a variable, and use it in the field list. For example you could do this:

（来自MySQL 文档）。您可以做的是计算WHERE子句中的列值，将值保存在变量中，并在字段列表中使用它。例如你可以这样做：

SELECT `users`.`first_name`, `users`.`last_name`, `users`.`email`,
@postcode AS `guaranteed_postcode`
FROM `users` LEFT OUTER JOIN `locations`
ON `users`.`id` = `locations`.`user_id`
WHERE (@postcode := SUBSTRING(`locations`.`raw`,-6,4)) NOT IN
(
 SELECT `postcode` FROM `postcodes` WHERE `region` IN
 (
  'australia'
 )
)

This avoids repeating the expression when it grows complicated, making the code easier to maintain.

这避免了表达式变得复杂时的重复，使代码更易于维护。

回答by George Khouri

Maybe my answer is too late but this can help others.

也许我的回答为时已晚，但这可以帮助其他人。

You can enclose it with another select statement and use where clause to it.

您可以用另一个 select 语句将其括起来，并对其使用 where 子句。

SELECT * FROM (Select col1, col2,...) as t WHERE t.calcAlias > 0

calcAlias is the alias column that was calculated.

calcAlias 是计算的别名列。

回答by Hett

You can use HAVING clause for filter calculated in SELECT fields and aliases

您可以将 HAVING 子句用于在 SELECT 字段和别名中计算的过滤器

回答by themhz

I am using mysql 5.5.24 and the following code works:

我正在使用 mysql 5.5.24 并且以下代码有效：

select * from (
SELECT `users`.`first_name`, `users`.`last_name`, `users`.`email`,
SUBSTRING(`locations`.`raw`,-6,4) AS `guaranteed_postcode`
FROM `users` LEFT OUTER JOIN `locations`
ON `users`.`id` = `locations`.`user_id`
) as a
WHERE guaranteed_postcode NOT IN --this is where the fake col is being used
(
 SELECT `postcode` FROM `postcodes` WHERE `region` IN
 (
  'australia'
 )
)

回答by Pavan Rajput

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:

标准 SQL 不允许在 WHERE 子句中引用列别名。强加此限制是因为在评估 WHERE 子句时，可能尚未确定列值。例如，以下查询是非法的：

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

回答by Sameera Prasad Jayasinghe

You can use SUBSTRING(locations.raw,-6,4) for where conditon

您可以将 SUBSTRING( locations. raw,-6,4) 用于 where 条件

SELECT `users`.`first_name`, `users`.`last_name`, `users`.`email`,
SUBSTRING(`locations`.`raw`,-6,4) AS `guaranteed_postcode`
FROM `users` LEFT OUTER JOIN `locations`
ON `users`.`id` = `locations`.`user_id`
WHERE SUBSTRING(`locations`.`raw`,-6,4) NOT IN #this is where the fake col is being used
(
SELECT `postcode` FROM `postcodes` WHERE `region` IN
(
 'australia'
)
)