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

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

<块引用>

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

SELECT `users`.`first_name`, `users`.`last_name`, `users`.`email`,SUBSTRING(`locations`.`raw`,-6,4) AS `guaranteed_postcode`从 `users` LEFT OUTER JOIN `locations`ON `users`.`id` = `locations`.`user_id`WHERE `guaranteed_postcode` NOT IN #这是使用假冒号的地方(SELECT `postcode` FROM `postcodes` WHERE `region` IN('澳大利亚'))

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

解决方案

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

<块引用>

标准 SQL 不允许您引用 WHERE 中的列别名条款.强加了这个限制因为当 WHERE 代码是已执行，列值可能尚未确定.

复制自 MySQL 文档

正如评论中所指出的，改用 HAVING 可能会完成这项工作.请务必阅读此问题:WHERE vs HAVING.

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

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

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?

解决方案

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

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.

Copied from MySQL documentation

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