Using column alias in a WHERE clause doesn't work
工作中需要写一个 SQL 进行查询,本来我是这样写的:
1 | SELECT document.id, (COALESCE(CAST(document.internal_info->>'proofread_time' AS int), 0) - p500_end.end_utc) / 60 AS proofread_time |
但是发现报这样的错误:
ERROR: column "proofread_time" does not exist
然后我把 WHERE 条件里的 proofread_time 替换成 (COALESCE(CAST(document.internal_info->>'proofread_time' AS int), 0) - p500_end.end_utc) / 60
,就正常了。
咦,好奇怪,难道 alias 不能在 WHERE 条件里用吗,有点反直觉,于是我去查了下文档:
An output column’s name can be used to refer to the column’s value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.
原因就是 WHERE 语句和 HAVING 语句是在 column aliases 之前做的,所以没法引用,而 ORDER BY 和 GROUP BY 是在其之后,所以可以使用 aliased column
很古怪吧,反直觉!
但是把这么一长串的表达式写两遍真的很难受,所以我使用了 WITH 表达式来解决这个问题:
1 | WITH results AS ( |
这样看起来就清晰多了。
Using column alias in a WHERE clause doesn't work
https://microndgt.github.io/2022/10/26/Using-column-alias-in-a-WHERE-clause-doesn-t-work/