我正在运行的查询如下,但我得到这个错误:
未知列'guaranteed_postcode'在' in /ALL/ANY子查询'
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'
)
)
我的问题是:为什么我不能在同一个DB查询的where子句中使用假列?
标准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'
)
)
这避免了在表达式变得复杂时重复它,使代码更容易维护。
你可以使用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'
)
)
正如维克多指出的,问题出在别名上。不过,可以通过将表达式直接放入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'
)
)
但是,我认为这是非常低效的,因为必须对外部查询的每一行执行子查询。