SQLite中含Null值的Year字段排序问题求助

需求回顾 #

1. 存在year值的记录：按year升序排序，再按name升序排序
2. year为NULL的记录：单独按name降序排序，且排在所有有year值的记录之后

原代码错误分析 #

第一段代码错误：

ORDER by CASE WHEN year = NULL then Name Else year, name

• SQL中判断NULL必须用IS NULL，而非= NULL（NULL与任何值比较结果都是NULL，永远不成立）
• CASE表达式只能返回单个值，无法同时返回year和name两个列，语法结构完全错误

第二段代码错误：

ORDER by YEAR NULLS LAST
   , IIF(year != NULL, name, name desc) ;

• 判断非NULL需用IS NOT NULL，而非!= NULL
• IIF函数仅能返回列/值，不能在函数内部指定排序方向（desc是排序关键字，不能写在函数参数里）

正确实现代码 #

以下是两种稳妥的实现方式：

方式一：分组排序（逻辑清晰，兼容性好）

ORDER BY
  -- 让有year值的记录排在前面，year为NULL的排在后面
  CASE WHEN year IS NOT NULL THEN 0 ELSE 1 END,
  -- 有year值的记录按year升序排列
  year ASC,
  -- 有year值的记录按name升序排列
  CASE WHEN year IS NOT NULL THEN name ELSE NULL END ASC,
  -- year为NULL的记录按name降序排列
  CASE WHEN year IS NULL THEN name ELSE NULL END DESC;

方式二：简洁写法（利用SQLite排序特性）

ORDER BY
  year NULLS LAST,
  -- 根据year是否为NULL动态指定name的排序方向
  name COLLATE BINARY CASE WHEN year IS NULL THEN DESC ELSE ASC END;

说明 #

• 两种写法都能满足需求，方式一更直观，适合所有SQLite版本；方式二更简洁，需SQLite 3.30.0及以上版本支持条件化排序方向
• COLLATE BINARY是为了确保字符串排序的一致性，可根据实际字符集需求调整（如COLLATE NOCASE忽略大小写）

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