sql - 为什么我得到列 "ended"不存在？

Question

我正在尝试在 SQL 中执行条件 ORDER BY 但我收到错误:column "ended"does not exist pointing to the case when "ended "' 查询的一部分。

这是我的查询:

SELECT (EXTRACT(EPOCH FROM SUBMISSION_DEADLINE)
       -EXTRACT(EPOCH FROM now())) AS "datediff"
    , (EXTRACT(EPOCH FROM SUBMISSION_DEADLINE)>EXTRACT(EPOCH FROM now())) AS "ended", *
FROM imdown_api_v1_contest
ORDER BY "ended" DESC, case when "ended" = true then "datediff" else "-datediff" END ASC;

我需要先获取尚未结束的比赛，并按照最快结束的比赛排在第一位。然后我需要所有已结束的，并按最近结束的排序。

Answer 1

执行order by时未定义column ended，sql按下一个顺序执行查询:

1. 来自
2. 在哪里
3. 订购
4. 选择

因此，您需要将完整表达式复制到 order by 子句中或使用子查询，例如:

select * from (
    SELECT (EXTRACT(EPOCH FROM SUBMISSION_DEADLINE)-EXTRACT(EPOCH FROM now())) AS "datediff", (EXTRACT(EPOCH FROM SUBMISSION_DEADLINE)>EXTRACT(EPOCH FROM now())) AS "ended", *
    FROM imdown_api_v1_contest
) tmpq
ORDER BY "ended" DESC, case when "ended" = true then "datediff" else "-datediff" END ASC