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

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子查询”中未知列“ed_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?

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

8 个解决方案

#1

349

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

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

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 HAVING though.

正如评论中所指出的，使用have可能会起到作用。一定要在这篇文章中给大家读一下。

#2

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:

正如Victor指出的，问题在于别名。可以通过将表达式直接放到y中的WHERE x子句中来避免这种情况:

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.

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

#3

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 WHERE clause, 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.

当表达式变得复杂时，这可以避免重复，从而使代码更易于维护。

#4

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是计算的别名列。

#5

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

您可以对SELECT字段和别名中计算的筛选器使用have子句

#6

I am using mysql 5.5.24 and the following code works:

我使用的是mysql 5.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'
 )
)

#7

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;

从tbl_name中选择id, COUNT(*)为cnt，其中cnt >按id分组;

#8

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

您可以将子字符串(location .raw，-6,4)用于where conditon

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

#1

349