Using column alias in a WHERE clause doesn't work

工作中需要写一个 SQL 进行查询,本来我是这样写的:

1
2
3
4
5
SELECT document.id, (COALESCE(CAST(document.internal_info->>'proofread_time' AS int), 0) - p500_end.end_utc) / 60 AS proofread_time
FROM document
JOIN p500_end ON p500_end.doc_id = document.id
WHERE document.created_utc > 1658592000 AND document.deleted = 0 AND proofread_time > 60
ORDER BY id DESC

但是发现报这样的错误:

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
2
3
4
5
6
7
8
WITH results AS (
SELECT document.id, (COALESCE(CAST(document.internal_info->>'proofread_time' as int), 0) - p500_end.end_utc) / 60 AS proofread_time
FROM document
JOIN p500_end ON p500_end.doc_id = document.id
WHERE document.created_utc > 1658592000 AND document.deleted = 0
ORDER BY id DESC
)
SELECT id, proofread_time FROM results WHERE proofread_time > 60;

这样看起来就清晰多了。

作者

skyrover

发布于

2022-10-26

更新于

2022-10-26

许可协议

评论