SQLite UNION查询用COALESCE按首个非空列排序失效如何解决

问题原因 #

SQLite 对 UNION/UNION ALL 这类组合查询的 ORDER BY 子句有特殊解析规则：组合查询后的顶层 ORDER BY 默认仅支持直接引用输出列名、或列的位置序号，直接在 ORDER BY 中使用基于输出列的函数表达式时，会触发解析错误，这就是你写法失败的核心原因。
另外补充：你提供的测试建表语句存在小语法错误，列定义顺序写反，正确写法应为 ID INT 而非 int ID，否则会直接建表失败。

可行解决方案 #

方案1（最通用，兼容性最好）

将整个 UNION 查询封装为子查询，在外层执行排序，无需调整原有查询逻辑：

SELECT * FROM (
    SELECT * FROM foo WHERE Date1 = '2021-11-12T008:30:00'
    UNION ALL
    SELECT * FROM foo WHERE Date2 = '2021-11-12T009:30:00'
) AS combined_foo
ORDER BY COALESCE(Date1, Date2)

方案2（提前计算排序字段）

在每个子查询中提前计算好排序用的COALESCE结果并指定别名，顶层ORDER BY直接引用别名排序：

SELECT *, COALESCE(Date1, Date2) AS sort_key FROM foo WHERE Date1 = '2021-11-12T008:30:00'
UNION ALL
SELECT *, COALESCE(Date1, Date2) AS sort_key FROM foo WHERE Date2 = '2021-11-12T009:30:00'
ORDER BY sort_key

两种方案都可以实现你要的排序需求，性能差异极小，可根据使用习惯选择。

内容的提问来源于stack exchange，提问作者arturn