在MySQL查询的WHERE子句中使用列别名会产生错误 [英] Using column alias in WHERE clause of MySQL query produces an error

问题描述

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

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

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

#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'
 )
)

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

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

推荐答案

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

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

标准SQL不允许您执行以下操作 引用WHERE中的列别名 条款.施加此限制 因为当WHERE代码是 执行后，列值可能还没有 被确定.

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.

从 MySQL文档复制

正如评论中指出的那样，改用HAVING可以完成这项工作.不过，请确保在此在哪里与生活进行阅读.

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

这篇关于在MySQL查询的WHERE子句中使用列别名会产生错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！