我正在运行的查询如下,但是我收到了这个错误:
#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 子句中使用假列?
您只能在 GROUP BY、ORDER BY 或 HAVING 子句中使用列别名。
标准 SQL 不允许您在 WHERE 子句中引用列别名。施加此限制是因为在执行 WHERE 代码时,可能尚未确定列值。
正如评论中所指出的,使用 HAVING 代替可能会完成这项工作。请务必阅读此问题:WHERE vs HAVING。
正如维克多所指出的,问题出在别名上。但是,可以通过将表达式直接放入 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'
)
)
但是,我想这是非常低效的,因为必须为外部查询的每一行执行子查询。
标准 SQL(或 MySQL)不允许在 WHERE 子句中使用列别名,因为
评估 WHERE 子句时,可能尚未确定列值。
(来自 MySQL documentation)。您可以做的是计算 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'
)
)
这样可以避免在表达式变得复杂时重复表达式,从而使代码更易于维护。
select @code:=sum(2), 2*@code
在 MySQL 5.5 中工作,但对我来说,在 5.6 中,第二列在第一次调用时产生 NULL,并在再次运行时返回 2 倍 上一个结果 .有趣的是,select @code:=2, 2*@code
和 select @code:=rand(), 2*@code
似乎都适用于我的 5.6(今天)。但这些确实是在 SELECT 子句中写入和读取;在您的情况下,您将其设置在 WHERE 中。
也许我的回答为时已晚,但这可以帮助其他人。
您可以用另一个 select 语句将其括起来并使用 where 子句。
SELECT * FROM (Select col1, col2,...) as t WHERE t.calcAlias > 0
calcAlias 是计算的别名列。
您可以将 HAVING 子句用于在 SELECT 字段和别名中计算的过滤器
WHERE
和 HAVING
之间的区别。它们并不相同。 stackoverflow.com/search?q=where+vs+having
我正在使用 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'
)
)
您可以将 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'
)
)
HAVING
的执行速度比WHERE
慢having
起作用的原因是必须在您到达having
时计算列值。如上所述,where
不是这种情况。