問題描述
我正在運(yùn)行的查詢?nèi)缦拢俏沂盏酱隋e(cuò)誤:
<塊引用>#1054 - 'IN/ALL/ANY 子查詢'中的未知列'guaranteed_postcode'
SELECT `users`.`first_name`, `users`.`last_name`, `users`.`email`,SUBSTRING(`locations`.`raw`,-6,4) AS `guaranteed_postcode`從 `users` LEFT OUTER JOIN `locations`ON `users`.`id` = `locations`.`user_id`WHERE `guaranteed_postcode` NOT IN #這是使用假冒號(hào)的地方(SELECT `postcode` FROM `postcodes` WHERE `region` IN('澳大利亞'))我的問題是:為什么我不能在同一個(gè)數(shù)據(jù)庫(kù)查詢的 where 子句中使用假列?
您只能在 GROUP BY、ORDER BY 或 HAVING 子句中使用列別名.
<塊引用>標(biāo)準(zhǔn) SQL 不允許您引用 WHERE 中的列別名條款.強(qiáng)加了這個(gè)限制因?yàn)楫?dāng) WHERE 代碼是已執(zhí)行，列值可能尚未確定.
復(fù)制自 MySQL 文檔
正如評(píng)論中所指出的，改用 HAVING 可能會(huì)完成這項(xiàng)工作.請(qǐng)務(wù)必閱讀此問題:WHERE vs HAVING.
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?
You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.
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.
Copied from MySQL documentation
As pointed in the comments, using HAVING instead may do the work. Make sure to give a read at this question too: WHERE vs HAVING.
這篇關(guān)于在 MySQL 查詢的 WHERE 子句中使用列別名會(huì)產(chǎn)生錯(cuò)誤的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！