SQLite：首条记录为NULL时如何保留NULL并维持计算字段数值类型

问题场景 #

现有数据集：

CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 values(1,1);
INSERT INTO t1 values(1,0);

执行以下升序排序查询时：

SELECT 
  IIF(b > 0, a / b, NULL) AS c
FROM t1
ORDER BY b ASC

首条记录的c值为NULL，导致该列被识别为STRING类型；但如果改为降序排序，首条是数值结果，列则会被正确识别为数值类型。需要在保留NULL值的前提下，让列始终被识别为REAL或INT类型。

可行解决方案 #

1. 显式转换NULL为目标数值类型 #

通过CAST指定NULL的类型，让整个IIF表达式的类型统一为数值型：

-- 转为INT类型
SELECT 
  IIF(b > 0, a / b, CAST(NULL AS INT)) AS c
FROM t1
ORDER BY b ASC

如果需要REAL类型，替换为CAST(NULL AS REAL)即可：

SELECT 
  IIF(b > 0, a / b, CAST(NULL AS REAL)) AS c
FROM t1
ORDER BY b ASC

2. 用同类型运算生成NULL #

利用NULLIF生成与a/b同类型的NULL（NULLIF(0,0)结果为NULL，类型为INT），避免无类型NULL导致的类型推导错误：

SELECT 
  IIF(b > 0, a / b, NULLIF(0,0)) AS c
FROM t1
ORDER BY b ASC

3. 用COALESCE锚定类型 #

通过COALESCE绑定一个同类型的占位值（该值永远不会被实际选中，仅用于固定列类型）：

SELECT 
  COALESCE(IIF(b > 0, a / b, NULL), CAST(0 AS INT)) AS c
FROM t1
ORDER BY b ASC

原理说明 #

问题根源在于数据库推导列类型时，会优先根据结果集第一条记录判断。无类型的NULL会被默认识别为STRING，显式指定NULL的类型后，整个表达式的类型会被固定为数值型，不受结果集排序顺序影响。

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