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

发布于 2024-07-23 05:36:19 字数 552 浏览 10 评论 0原文

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

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

我的问题是：为什么我无法在同一数据库查询的 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'

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

锦上情书 2024-07-30 05:36:19

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

标准 SQL 不允许您
引用 WHERE 中的列别名
条款。施加此限制
因为当 WHERE 代码是
执行后，列值可能尚未执行
下定决心。

复制自 MySQL 文档

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

回复收藏 0 原文

守望孤独 2024-07-30 05:36:19

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

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

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

calcAlias 是计算的别名列。

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 * FROM (Select col1, col2,...) as t WHERE t.calcAlias > 0

calcAlias is the alias column that was calculated.

回复收藏 0 原文

如痴如狂 2024-07-30 05:36:19

正如维克多指出的，问题出在别名上。不过，通过将表达式直接放入 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'
 )
)

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

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:

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.

回复收藏 0 原文

云朵有点甜 2024-07-30 05:36:19

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

当计算 WHERE 子句时，列值可能尚未确定。

（来自 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'
 )
)

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

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

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

(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:

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.

回复收藏 0 原文

万劫不复 2024-07-30 05:36:19

您可以使用 HAVING 子句进行 SELECT 字段和别名中计算的过滤器

回复收藏 0 原文

女皇必胜 2024-07-30 05:36:19

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

I am using mysql 5.5.24 and the following code works:

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

回复收藏 0 原文

哎呦我呸! 2024-07-30 05:36:19

您可以使用 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'
)
)

You can use SUBSTRING(locations.raw,-6,4) for 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'
)
)

回复收藏 0 原文