一、两种写法

-- 简单 CASE：等值判断
CASE subject WHEN '语文' THEN score END

-- 搜索 CASE：任意条件
CASE WHEN score >= 60 THEN '及格' ELSE '不及格' END

第一种只做等值判断——它把 subject 依次跟每个 WHEN 后面的值比。 第二种能写任意条件——>=、BETWEEN、IS NULL 都行。 能写成第一种的就写第一种，它更清楚。 但范围判断、 多列组合判断只能用第二种——所以实际项目里第二种用得更多。 没有 ELSE 时返回 NULL。 这个行为在行列转换里正好用得上—— 「不是这门课」的那些行会返回 NULL，正好被聚合函数忽略。

二、行转列：从长表到宽表

先看数据长什么样。 九行，每个学生的每门课一行——这叫「长表」，适合存储。

但人不这么看。 人想看的是「一行一个学生、一列一门课」——这叫「宽表」，适合展示。 从长表变宽表，就是「行转列」。SQL 里没有专门的语法，得用 CASE 拼：

SELECT sno AS 学号,
       MAX(CASE WHEN subject = '语文' THEN score END) AS 语文,
       MAX(CASE WHEN subject = '数学' THEN score END) AS 数学,
       MAX(CASE WHEN subject = '英语' THEN score END) AS 英语,
       SUM(score) AS 总分
FROM tb_score
GROUP BY sno

这段的三步要理解清楚： 第一步，CASE 对每一行只返回「这一行是不是这门课」的成绩， 其它返回 NULL；第二步，GROUP BY sno 把同一学生的三行压成一组； 第三步，MAX 从一组里挑出那个非 NULL 的值。 所以 MAX 在这里不是「求最大值」，是「从一组里挑出唯一的非空值」。这个用法很反直觉， 但它是标准做法——别换成 SUM，理由下一段说。

三、为什么用 MAX 不用 SUM

正常情况下，一个人一门课只有一条记录，MAX 和 SUM 结果一样。所以很多人图省事写 SUM—— 然后某天数据里出现了重复记录。

第二行是最典型的：同一人在同一门课上不小心插了两条。SUM 会给 176——一眼就能看出不对； 但如果这个人本来总分是 300 多，多了一门课的分数混在里面，很难发现。 第三行是「补考」场景。 有的系统会把两次成绩都存着，这时候 MAX 取最高的（合理）， SUM 加起来（不合理）。 所以用 MAX 是防御性的写法。 它假设「可能有重复」，然后取「最合理的那个」。 这个思路值得学：写 SQL 时多想想「万一数据不干净会怎样」。

四、用 CASE 自定义排序

SELECT subject, ROUND(AVG(score),2) AS 均分
FROM tb_score GROUP BY subject
ORDER BY CASE subject WHEN '语文' THEN 1
                      WHEN '数学' THEN 2 ELSE 3 END

这是 CASE 的另一个用法：不想按字典序排，想按「自己定义的顺序」排。 按字典序排，「数学」会排在「语文」前面——因为拼音 s 在 y 前。但教学顺序里，语文通常排第一。 用 CASE 给每门课编个号再排，就能按教学顺序来。这个技巧还能用在「按状态排序」上： 待处理 → 处理中 → 已完成——这个顺序字典序给不出来，只能自己定。 更好的做法是「加一个排序列」。 如果科目表里有 sort_no 字段， 直接 JOIN 过来 ORDER BY sort_no 就行——比在 SQL 里硬编码清楚，改顺序也不用改代码。 但在没有那个字段的时候，CASE 是最快的办法。 这也说明一件事：排序规则应该由数据决定，不该由 SQL 决定—— 硬编码在 CASE 里的顺序，改一次就要改一次代码。

五、动手 Demo：九行变三行的行列转换

这一段用三个学生、三门课共九行数据，演示从长表到宽表的完整过程； 再故意不写聚合函数，看不写会出什么结果；最后用 CASE 做一次自定义排序。

import sqlite3

c = sqlite3.connect(u":memory:")
c.execute(u"CREATE TABLE tb_score (sno TEXT, subject TEXT, score INT)")
c.executemany(u"INSERT INTO tb_score VALUES (?,?,?)", [
    ('S01','语文',88), ('S01','数学',92), ('S01','英语',79),
    ('S02','语文',95), ('S02','数学',61), ('S02','英语',84),
    ('S03','语文',72), ('S03','数学',88), ('S03','英语',90)])

q = u"""SELECT sno,
       MAX(CASE WHEN subject='语文' THEN score END) AS 语文,
       MAX(CASE WHEN subject='数学' THEN score END) AS 数学,
       MAX(CASE WHEN subject='英语' THEN score END) AS 英语,
       SUM(score) AS 总分
       FROM tb_score GROUP BY sno"""
for r in c.execute(q):
    print(r)

# 不写聚合函数会怎样
q2 = u"SELECT sno, CASE WHEN subject='语文' THEN score END AS 语文 "
     u"FROM tb_score GROUP BY sno"
print(c.execute(q2).fetchall())

实跑输出：

--------------------------------------------------------------------
CASE 与行列转换
--------------------------------------------------------------------


  ① 原始数据是竖着的
  --------------------------------------------------------------------
  sno  subject  score
  S01  语文       88
  S01  数学       92
  S01  英语       79
  S02  语文       95
  S02  数学       61
  S02  英语       84
  S03  语文       72
  S03  数学       88
  S03  英语       90
  九行，每个学生的每门课一行。这种结构叫「长表」，适合存储。
  但人不这么看——人想看的是「一行一个学生，一列一门课」。这叫「宽表」。
  从长表变宽表，就是所谓的「行转列」。SQL 里没有专门的语法，得用 CASE 拼。

  ② CASE 的两种写法
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | -- 简单 CASE：等值判断
  | CASE subject WHEN '语文' THEN score END
  | 
  | -- 搜索 CASE：任意条件
  | CASE WHEN score >= 60 THEN '及格' ELSE '不及格' END

  第一种只做等值判断，第二种能写任意条件。
  注意「没有 ELSE」时返回 NULL——这在行列转换里正好用得上，下面会看到。

  ③ 行转列：一行一个学生
  --------------------------------------------------------------------
  学号   语文  数学  英语  总分   均分
  S01  88  92  79  259  86.33
  S02  95  61  84  240  80.00
  S03  72  88  90  250  83.33
  三行九列的数变成了三行六列——这就是行转列。
  关键在那个 MAX：CASE 对每一行只返回一个值（这门课的成绩），其它是 NULL；
  GROUP BY 把同一学号的行压成一组，MAX 从一组里挑出非 NULL 的那个。
  为什么用 MAX 不用 SUM？因为 SUM 遇到只有一个非 NULL 值时结果相同，但语义上 MAX 更准——
  万一数据里有重复（同一人同一门课两条记录），MAX 取最高的，SUM 会加起来。

  ④ 不写 MAX 会怎样
  --------------------------------------------------------------------
  不写聚合函数：
  sno  语文
  S01  88
  S02  95
  S03  72
  每门课有 3 条记录，但分组之后只剩一行——数据库不知道该保留哪一个。
  SQLite 的做法是「随便挑组里的第一行」，所以它给你的是 S01 那组的语文成绩 88。
  这个结果纯属碰运气：换个插入顺序、加个索引，它挑的可能是另一行。
  SQL Server 会直接报错：列名无效，因为它要求 SELECT 里的列必须在 GROUP BY 里或者被聚合。
  所以这段在 SQL Server 上根本过不去——这反而是好事，SQLite 的「随便挑」才是坑。

  ⑤ 用 CASE 排序：自定义顺序
  --------------------------------------------------------------------
  科目  均分
  语文  85.00
  数学  80.33
  英语  84.33
  这是 CASE 的另一个用法：不想按字母序排，想按「自己定义的顺序」排。
  按字典序排，「数学」会排在「语文」前面（拼音 s 在 y 前）；
  用 CASE 给每门课编个号再排，就能按教学顺序来。
  同样的技巧还能用在「按状态排序」上：待处理 → 处理中 → 已完成，
  这个顺序字典序给不出来，只能自己定。

第一段三行就是结果：九行变三行，每行六列。S01 是 88、92、79、总分 259； S02 是 95、61、84、总分 240；S03 是 72、88、90、总分 250。 注意 S02 的数学只有 61——如果按「总分排序」，S02 排最后， 但它的语文是 95，最高。这就是宽表的价值：一眼看出结构。 第二段是不写 MAX 的结果：S01 88、S02 95、S03 72。看起来没错—— 因为它挑的是每组第一行，而第一行正好是语文。 这个「正好」很危险。 换个插入顺序、加个索引， 它挑的可能是另一行——结果就变成 NULL 或者别的科目。SQL Server 更严格，这种写法直接报错。

30 秒实验

1. 把行列转换里的 MAX 换成 SUM，跑一次。 数据干净的时候结果一样——然后手动插一条重复记录，再跑一次，看它翻倍。
2. 把 CASE 里的科目顺序换一下（比如先写「英语」），看结果列的顺序变不变。 列的顺序由 SELECT 决定，跟 CASE 里的顺序无关。
3. 想一想：如果科目有二十门，这个写法还合适吗？ 不合适——二十个 CASE 太长，而且每加一门都要改 SQL。 那种情况应该在程序里做行列转换，或者用动态 SQL 拼。

常见误区

1. 「行列转换用 SUM 也行，反正数据不重复」。「数据不重复」是个假设，不是保证。 一旦有重复，SUM 会翻倍而且不报错。 MAX 是防御性的写法。
2. 「CASE 里不写 ELSE 会报错」。不会，它返回 NULL。 而在行列转换里，「返回 NULL」正是我们要的——聚合函数会自动忽略 NULL。
3. 「科目顺序应该写在 CASE 里」。能写，但改一次顺序就要改一次 SQL。 更好的做法是加一个 sort_no 字段，用 JOIN 带过来排序——顺序由数据决定，不由代码决定。

第 8 章到此结束。 下一篇进第 9 章 9.1a，讲子查询： 「查询里套查询」是 SQL 的分水岭——从这里开始，语句开始有层次，也就开始难读了。

• ☐ （本篇完，下一篇 9.1a 子查询基础）

相关笔记

• 使用 UNION ALL 合并
• 流程控制
• 48 课 MySQL从入门到精通 · 索引
• 🪟 SQL Server从入门到精通 · 索引