問題描述
我需要在 WHERE 子句中使用別名，但它一直告訴我它是一個未知列.有沒有辦法解決這個問題?我需要選擇評分高于 x 的記錄.評分計算為以下別名:
I need to use an alias in the WHERE clause, but It keeps telling me that its an unknown column. Is there any way to get around this issue? I need to select records that have a rating higher than x. Rating is calculated as the following alias:
sum(reviews.rev_rating)/count(reviews.rev_id) as avg_rating
推薦答案
您可以使用 HAVING 子句，可以看到別名，例如
You could use a HAVING clause, which can see the aliases, e.g.
HAVING avg_rating>5
但是在 where 子句中，您需要重復您的表達式，例如
but in a where clause you'll need to repeat your expression, e.g.
WHERE (sum(reviews.rev_rating)/count(reviews.rev_id))>5
但是！并非所有表達式都被允許 - 使用 SUM 之類的聚合函數將不起作用，在這種情況下，您需要使用 HAVING 子句.
BUT! Not all expressions will be allowed - using an aggregating function like SUM will not work, in which case you'll need to use a HAVING clause.
來自 MySQL 手冊:
不允許引用WHERE 子句中的列別名，因為列值可能還沒有當 WHERE 子句被確定被執行.參見 B.1.5.4 節，列別名問題".
It is not allowable to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section B.1.5.4, "Problems with Column Aliases".
這篇關于你可以在 mysql 的 WHERE 子句中使用別名嗎?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！