問題描述
我正在運行的查詢如下，但是我收到此錯誤:
<塊引用>#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 #這是使用假冒號的地方(SELECT `postcode` FROM `postcodes` WHERE `region` IN('澳大利亞'))我的問題是:為什么我不能在同一個數據庫查詢的 where 子句中使用假列?
您只能在 GROUP BY、ORDER BY 或 HAVING 子句中使用列別名.
<塊引用>標準 SQL 不允許您引用 WHERE 中的列別名條款.強加了這個限制因為當 WHERE 代碼是已執行，列值可能尚未確定.
復制自 MySQL 文檔
正如評論中所指出的，改用 HAVING 可能會完成這項工作.請務必閱讀此問題: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.
這篇關于在 MySQL 查詢的 WHERE 子句中使用列別名會產生錯誤的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！