一、IF 与它的替身

IF(条件, 真值, 假值) 是 MySQL 的写法，SQLite 叫 iif，参数顺序完全一样。 它只能处理两种情况，再多就得嵌套。

嵌套两层以上就该换 CASE，因为括号一多就没人看得懂。 这一点和编程语言的 if-else 一样，不是语法问题，是可读性问题。 还有一个细节：IF 的两个分支都会先算出来，再挑一个。 所以别在分支里放「会报错」的表达式，指望另一个分支救场——那救不了。

二、IFNULL、COALESCE、NULLIF

三个函数名字像，方向全不一样。 IFNULL 是「空了就换」，COALESCE 是「取第一个非空」，NULLIF 是「相等就变空」。

IFNULL 只接两个参数，COALESCE 能接一串。 所以「有 A 用 A，没有用 B，再没有用 C」这种需求，COALESCE(A, B, C) 一句就够。 NULLIF 的用法是「把占位值转成空」。 比如 0 表示「没填」，NULLIF(分, 0) 就把它变成 NULL，后面的 AVG 就会自动跳过。 这一招比到处写 CASE WHEN 分 = 0 THEN NULL 干净得多。 而且改口径的时候只改一处。

三、CASE WHEN 的两种写法

简单 CASE 只能做「等于比较」，搜索 CASE 能写任意条件。 日常几乎只用第二种，因为它能表达区间。

CASE 从上往下判，第一个为真的分支就返回，后面的不再看。 所以分支顺序会影响结果：>= 60 写在 >= 90 前面，所有人都会变成「及格」。 CASE 的返回值类型要一致。 一个分支给数字、一个分支给字符串，引擎会按第一支的类型统一，可能转出奇怪的东西。 最后那个 ELSE 不是可选的废话：不写就默认给 NULL。 所有分支都没命中时，结果是 NULL，不是「原值」，也不是「空串」。

四、用条件函数分档统计

GROUP BY 后面可以直接用 SELECT 里那个别名，MySQL 和沙箱都成立。 所以「先把行分档，再按档统计」只要一句，不用建中间表。 这一招的价值在于「口径写在 SQL 里」：分档规则改了，只改一处，统计自动跟着变。 要是把分档搬到应用层，两份规则迟早会不一致。 代价是每次查询都要算一遍分档。 数据量大、又天天查同一套分档时，可以建一个「档位」列存下来，用触发器维护。 但先别急着优化，先确认这个查询真的慢。 过早加冗余列，后面维护成本比省下来的那点时间贵得多。

五、动手 Demo：条件判断函数，和把 NULL 挡在门外

数据是五个学生五条成绩，其中两条是 NULL（缺考）。 这个「空」是整段的支点：三种平均分、分档统计、CASE 的 ELSE，全靠它撑起来。

import sqlite3

c = sqlite3.connect(u":memory:")
c.execute(u"CREATE TABLE tb_s (id INTEGER, score REAL)")
c.executemany(u"INSERT INTO tb_s VALUES (?, ?)",
               [(1, 95), (2, None), (3, 78), (4, None), (5, 42)])

# ① 三种平均分
print(c.execute(u"SELECT AVG(score) FROM tb_s").fetchone())
print(c.execute(u"SELECT SUM(score) / COUNT(*) FROM tb_s").fetchone())
print(c.execute(u"SELECT AVG(coalesce(score, 0)) FROM tb_s").fetchone())

# ② 分档，注意 NULL 那一支必须自己写
print(c.execute(u"SELECT id, CASE WHEN score IS NULL THEN '缺考' "
                u"WHEN score >= 90 THEN '优' WHEN score >= 60 THEN '及格' "
                u"ELSE '不及格' END FROM tb_s").fetchall())

# ③ 按档统计
print(c.execute(u"SELECT CASE WHEN score IS NULL THEN '缺考' "
                u"WHEN score >= 60 THEN '及格' ELSE '不及格' END AS 档, "
                u"COUNT(*) FROM tb_s GROUP BY 档").fetchall())

实跑输出：

--------------------------------------------------------------------
第四十八课：条件判断函数，和把 NULL 挡在门外
--------------------------------------------------------------------

  **教材 10.5 讲三个：`IF()`、`IFNULL()`、`CASE WHEN`。**
  **沙箱对应的是 `iif()`、`ifnull()`、`CASE`，三个都能跑。**
  **这一课的重点是第四个：`COALESCE`。教材没细讲它，但它比 `IFNULL` 好用。**

  一、IF 与它的替身
  --------------------------------------------------------------------

  写法                      结果
  ------------------------------------------------
  `iif(1 > 0, '真', '假')`  真
  `iif(1 < 0, '真', '假')`  假
  `if(1 > 0, '真', '假')`   沙箱无此函数，用 `iif`
  **`IF(条件, 真值, 假值)` 是 MySQL 的写法，SQLite 叫 `iif`，参数顺序完全一样。**
  **它只能处理「两种情况」，再多就得嵌套，而嵌套两层以上就该换 `CASE`。**
  **还有一个细节：`IF` 的两个分支都会先算出来，再挑一个。**
  **所以别在分支里放「会报错」的表达式，指望另一个分支救场——那救不了。**

  二、IFNULL、COALESCE、NULLIF
  --------------------------------------------------------------------
  id score
  --------
  1  95
  2  NULL
  3  78
  4  NULL
  5  42
  5 rows in set

  写法                       结果  意思
  -----------------------------------------------
  `ifnull(NULL, 1)`          1     空了就换 1
  `coalesce(NULL, NULL, 2)`  2     取第一个非空的
  `nullif(1, 1)`             NULL  相等就变空
  `nullif(1, 2)`             1     不等就保留
  **`IFNULL` 只接受两个参数，`COALESCE` 可以接一串，返回第一个非空值。**
  **所以「有 A 用 A，没有用 B，再没有用 C」这种需求，`COALESCE(A, B, C)` 一句就够。**
  **`NULLIF(a, b)` 的方向不一样：它是「相等就变成空」。**
  **常用在「把占位值转成空」——比如 0 表示「没填」，`NULLIF(分, 0)` 就把它变成空。**

  算法（算平均分）           结果
  ----------------------------------
  `AVG(score)`               71.6667
  `SUM(score) / COUNT(*)`    43.0
  `AVG(coalesce(score, 0))`  43.0
  **第一行 71.6667，第二第三行都是 43.0，这两组数值得盯着看。**
  **第一行只除 3 个有效分，后两行除 5 个——差别全在分母上。**
  **「缺考算不算 0 分」这个业务问题，就落在这三个写法里。**
  **SQL 不会替你决定，它只按你写的算。所以写之前先想清楚要哪种口径。**
  **这一条和第 9.3 篇讲的 `AVG` 分母是同一件事，只是换了个场景。**

  三、CASE WHEN 的两种写法
  --------------------------------------------------------------------

  写法       结果
  ----------------------------------------------
  简单 CASE  `CASE 列 WHEN 值 THEN 结果 ... END`
  搜索 CASE  `CASE WHEN 条件 THEN 结果 ... END`
  **简单 CASE 只能做「等于比较」，搜索 CASE 能写任意条件。**
  **日常几乎只用第二种，因为它能表达区间，而第一种不能。**

  id  score  SQL 算的  Python 算的
  --------------------------------
  1   95.0   优        优
  2   NULL   缺考      缺考
  3   78.0   及格      及格
  4   NULL   缺考      缺考
  5   42.0   不及格    不及格
  **第三列和第四列必须一致，这是「两种语言算同一件事」的对照。**
  **两列一致，说明这一层逻辑可以放心下推到 SQL；不一致，就得先查清楚是哪边错了。**
  **注意第一行：`score` 是 NULL，两个分支都没进，落到 `ELSE`。**
  **所以 `CASE` 里那个 `WHEN score IS NULL` 不是保险，是必需——不写就会把缺考算成不及格。**

  四、用条件函数做分组统计
  --------------------------------------------------------------------

  档      人数
  ------------
  不及格  1
  及格    2
  缺考    2
  **`GROUP BY` 后面可以直接用 `SELECT` 里那个别名，这在 MySQL 和沙箱里都成立。**
  **所以「先把行分档，再按档统计」只要一句，不用建中间表。**
  **这一招的价值在于「口径写在 SQL 里」：分档规则改了，只改一处，统计自动跟着变。**
  **要是把分档搬到应用层，两份规则迟早会不一致。**

三种平均分是 71.6667、43.0、43.0。 第一行只除 3 个有效分，后两行除 5 个——差别全在分母上，不在分子。 CASE 那一段的输出里，两个 NULL 都落在「缺考」。 把 WHEN score IS NULL 那一支删掉再跑，它们会变成「不及格」，因为 NULL >= 60 是未知。 分档统计给出三档：不及格 1、及格 2、缺考 2。 三档加起来是 5，和总行数对得上——这是检查 CASE 有没有漏分支的最快办法。 要注意 GROUP BY 档 用的是别名。 这在 MySQL 和沙箱都成立，但标准 SQL 不保证，换个库可能要重复写整段 CASE。

30 秒实验

1. 把 WHEN score IS NULL 那一支删掉，看两条缺考记录变成什么档。
2. 把 coalesce(score, 0) 换成 nullif(score, 0)，想想结果会怎么变。
3. 把分档的 >= 60 写到 >= 90 前面，看所有人的档位变成什么。

常见误区

1. CASE 里忘了空值那一支，缺考被算成不及格。
2. 分支顺序反了，>= 60 写在前面，所有人都成了「及格」。
3. 用 IFNULL 想接三个参数，报参数个数错。

下一篇 10.6 进系统信息函数，其中一半是「讲清这里缺什么」。

• ☐ （本篇完，下一篇 10.6 系统信息函数）

相关笔记

• 日期和时间函数
• 聚合函数查询
• 流程控制语句
• 🛢️ MySQL从入门到精通 · 索引