聚合与数据库：GROUP BY、聚合函数与 HAVING

0. 本讲导读

上一讲把 SQL 的骨架搭起来了：SELECT 造一张新表，FROM 说数据在哪，WHERE 把不想要的行筛掉，JOIN … ON … 把两张表拼起来，ORDER BY 排序，LIMIT 截断。这一整套有一个共同点，值得先停下来看清楚：

它们全都是「逐行」的。 WHERE salary > 40000 判断的是当前这一行；ORDER BY name 比较的是两行之间；连 JOIN 也只是把「A 的某一行」和「B 的某一行」并成更长的一行。输入表有 9 行，输出表最多也就那 9 行的某个子集（或者 join 之后的若干组合）。没有任何一个子句能同时看见多行然后给出一个数。

于是下面这些问题，用上一讲的工具一个都答不出来：

• 公司一共有几个人？
• Computer 部门的平均工资是多少？
• 哪个部门人最多？
• 哪些主管手底下管着不止一个人？

这些问题的共同结构是：先按某个标准把行分成几堆，然后对每一堆整体算一个数。「几个人」是对全表这一堆数数；「每个部门的平均工资」是先按 division 分堆、再对每堆的 salary 求平均。这个「把多行的信息合成一个值」的动作就叫聚合（aggregation）。

本讲补上的正是这套机制，一共三件东西：

1. GROUP BY——按指定列把行分堆，每堆最后只剩一行输出；
2. 聚合函数（aggregation function）——COUNT / MAX / MIN / AVG / SUM，它们吃「一堆行的某一列」，吐一个值；
3. HAVING——分完堆之后，按「堆的性质」把整堆整堆地筛掉。它对组做的事，正是 WHERE 对行做的事。

加进来之后，SQL 子句的执行顺序也要更新一次。这一讲最容易被忽略、但考试和调试时最要命的一句话是：SQL 不是从上往下执行的。WHERE 在分堆之前跑，HAVING 在分堆之后跑，SELECT 排在两者之后——理解了这个顺序，「为什么 WHERE COUNT(*) > 1 会报错」这类问题就不用背了。

本讲开头还有两段复习：Scheme 的动态作用域（dynamic scope），以及对 Lecture 21 宏（macro）展开规则的一处更正。它们跟 SQL 无关，但都是「名字怎么查」「表达式什么时候被求值」这门课的母题，也都会进期末的考察范围，所以放在第 1、2 节讲完再进 SQL。

1. 复习：动态作用域下，parent 到底怎么定

先把这个复习题放在最前面，因为它考的是这门课贯穿始终的那个问题：查一个名字的时候，从哪一帧开始找，找不到往哪儿走。

回忆 Scheme 里两种造过程的方式：

关键的一句话，也是这道复习题唯一想让你记住的：

场景一：g 是普通过程

(define x 1)
(define h (mu () x))
(define (g h)
    (h)
)
(define (f)
    (define x 2)
    (g h)
)

scm> (f)
1

为什么是 1？一步一步走。

Global frame
    x  ──→ 1
    h  ──→ mu ()  [无定义环境]
    g  ──→ func g(h)      定义于 Global
    f  ──→ func f()       定义于 Global

f1: f  [parent=Global]          ← f 是普通过程，定义在 Global
    x  ──→ 2                    ← 只遮蔽，不改全局的 x
    正在算：(g h)

f2: g  [parent=Global]          ← g 是普通过程 ⇒ parent = 定义处 Global
    h  ──→ mu ()
    正在算：(h)

f3: h  [parent=f2]              ← h 是 mu 过程 ⇒ parent = 调用处 f2
    正在算：x
    查找路径：f3 ✗ → f2 ✗ → Global ✓ 得到 1

最容易错的一步是第 5 步。很多人看到「是 mu 触发的查找」，就以为整条查找链都会变成动态的，于是沿着 f3 → f2 → f1 走，答出 2。不对。 f2 的 parent 在创建 f2 的那一刻就定死了——因为 g 是普通过程，它的 parent 只能是定义处的 Global。f1 根本不在这条链上。

场景二：把 g 也改成 mu

(define x 1)
(define h (mu () x))
(define g (mu (h)
    (h)
))
(define (f)
    (define x 2)
    (g h)
)

scm> (f)
2

唯一的改动是 g 从普通过程变成 mu 过程。改动很小，链条却整个变了。

Global frame
    x  ──→ 1
    h  ──→ mu ()
    g  ──→ mu (h)
    f  ──→ func f()

f1: f  [parent=Global]
    x  ──→ 2
    正在算：(g h)

f2: g  [parent=f1]              ← g 现在是 mu ⇒ parent = 调用处 f1
    h  ──→ mu ()
    正在算：(h)

f3: h  [parent=f2]              ← h 是 mu ⇒ parent = 调用处 f2
    正在算：x
    查找路径：f3 ✗ → f2 ✗ → f1 ✓ 得到 2

这两个答案我在本仓库的 Scheme 解释器上实际跑过，分别是 1 和 2，与讲义一致。

场景三：自己造一个变式检验理解

讲义只给了两个场景。想确认自己真的懂了，最好的办法是自己改一处，先预测再运行。这次把 h 改回普通过程，g 保持 mu：

(define x 1)
(define (h) x)          ; h 现在是普通过程
(define g (mu (h) (h))) ; g 还是 mu
(define (f)
    (define x 2)
    (g h)
)

scm> (f)
1

预测的依据还是那一句：看开这一帧的是哪种过程。 f1: f 的 parent 是 Global；g 是 mu，所以 f2: g 的 parent 是调用处 f1；但 h 现在是普通过程，它定义在 Global，所以 f3: h 的 parent 是 Global，跟 f2、f1 全都断开了。查 x 直接在 Global 命中，得 1。实际运行确认是 1。

把三个场景并排看，规律就很清楚了——决定答案的只有「最后一环 h 是哪种过程」以及「往上追的那条链上各帧的 parent 是怎么定的」：

场景一和场景三的 f3 parent 完全不同，答案却都是 1——这说明不能靠背结论，只能一帧一帧推。

2. 更正：宏的第二步是「带引号的代入」

Lecture 21 讲宏的时候，把展开的第二步说成了「代入（substitution）」，暗示可以把算子数原样塞进宏体。这句话不准确，本讲专门更正。先把正式规则抄下来（来自 Scheme Spec）：

问题出在第 2 步的「未求值」到底怎么实现。准确的说法是：它是一次带引号的代入（quoted substitution）。理由不难理解——调用一个宏，等价于「把参数加上引号，调用一个普通过程，然后对结果调 eval」。既然是调普通过程，参数就得是值；一段还没求值的代码要当成值传，唯一的办法就是给它加引号，变成一个数据（list）。

拿 Lecture 21 那个 check 宏来看差别：

(define-macro (check expr)
     (list 'if expr ''passed
          (list 'quote (list 'failed: expr))
     )
)
(define x -2)


scm> (check (> x 0))
(failed: (> x 0))

check 想做的事是：条件成立就返回符号 passed，不成立就返回一段说明「哪个条件挂了」的数据——注意是把那个条件表达式原文打印出来，而不是打印它的值。所以宏体里出现了两层 quote：''passed（两个引号）和 (list 'quote …)。

如果第 2 步是裸代入（错误的理解）

假设把 expr 直接换成 (> x 0)、不加引号，宏体就变成：

; 宏体（原样）
(list 'if expr ''passed
    (list 'quote (list 'failed: expr))
)

; 裸代入 expr，NO quote
(list 'if (> x 0) ''passed
    (list 'quote (list 'failed: (> x 0)))
)

接下来这个宏体本身要被求值（它就是一段普通的 Scheme 代码，用 list 造一棵语法树）。求值 list 调用要先求值所有算子数，于是里面的 (> x 0) 在这里就被算成了 #f：

; 宏返回的是
(quote (failed: #f))

; 第 3 步在调用处求值它，解释器显示
(failed: #f)

(failed: #f) 完全没用——它只告诉你「有个东西是假的」，却没告诉你哪个条件假了。这正是必须加引号的原因。

正确的：带引号的代入

; 宏体（原样）
(list 'if expr ''passed
    (list 'quote (list 'failed: expr))
)

; 代入 expr，WITH quote
(list 'if '(> x 0) ''passed
    (list 'quote (list 'failed: '(> x 0)))
)

对照一下 ''passed：外层 quote 让宏返回的代码里出现 'passed，第 3 步求值 'passed 得到符号 passed。要是只写一个 'passed，宏返回的代码里就成了裸的 passed，第 3 步会把它当成一个名字去查——大概率报 Error: unknown identifier: passed。

scm> (define-macro (check expr) ...)
Error: unknown identifier: define-macro

3. 热身：把同一张表 join 三次

进入聚合之前，先做完上一讲留下的那道 23.sql Q1。它不涉及任何新语法，纯粹练「自连接（self join）」——同一张表在 FROM 里出现多次，靠别名区分。这个技巧在做 HW 06 时会反复用到。

数据：records 表

本讲用的示例表是 61A 一直在用的 records，五列：name、division、title、salary、supervisor。

题目

要求输出三列：员工 employee、他的主管 middle_manager、他主管的主管 skip_manager。按这三列依次排序。有人是自己的主管，不用特殊处理。

怎么想到的

看到题的第一反应通常是「这得递归吧？沿着 supervisor 一路往上爬」。不用。 题目要的层数是固定的——正好两层。一旦层数固定，就不是递归问题，而是「把表接起来」的问题。

关键的思路转换是这一句：records 这张表既是「员工表」，也是「主管表」——它们是同一张表，只是用途不同。 于是：

SELECT a.name AS employee, b.name AS middle_manager, c.name AS skip_manager
FROM records AS a
JOIN records AS b
JOIN records AS c
ON a.supervisor = b.name AND b.supervisor = c.name
ORDER BY employee, middle_manager, skip_manager;

实际输出（本机 SQLite）：

employee|middle_manager|skip_manager
Alyssa P Hacker|Ben Bitdiddle|Oliver Warbucks
Ben Bitdiddle|Oliver Warbucks|Oliver Warbucks
Cy D Fect|Ben Bitdiddle|Oliver Warbucks
Eben Scrooge|Oliver Warbucks|Oliver Warbucks
Lana Lambda|Lana Lambda|Lana Lambda
Lem E Tweakit|Ben Bitdiddle|Oliver Warbucks
Louis Reasoner|Alyssa P Hacker|Ben Bitdiddle
Oliver Warbucks|Oliver Warbucks|Oliver Warbucks
Robert Cratchet|Eben Scrooge|Oliver Warbucks

验证：追一行

拿 Louis Reasoner 走一遍。三重 join 概念上就是三层嵌套循环：对 a 的每一行，配 b 的每一行，再配 c 的每一行，一共 $9^3 = 729$ 种组合，然后用 ON 的条件筛。

ambiguous column name: name

4. 为什么需要聚合：逐行的工具答不了整体的问题

现在换个问题：公司一共有几个人？

用上一讲的全部工具试一遍，你会发现每一样都差一点：

症结在于：SELECT 是一个「对每一行做同样的事」的子句。 输入表有几行，它就产出几行（WHERE 只能减少行数，ORDER BY 只能调换行的顺序）。而「有几个人」这个答案，需要同时看到全部 9 行才算得出来。这是一个逐行工具在结构上做不到的事。

再看一个更麻烦的：每个部门的平均工资是多少？ 这里有两层需求叠在一起：

1. 分堆：Computer 的行归一堆、Accounting 的行归一堆、Administration 的行归一堆；
2. 压缩：每一堆的 salary 列合成一个平均值。

这正好对应本讲的两样东西：GROUP BY 负责第 1 步，聚合函数负责第 2 步。它们几乎总是一起出现，因为分了堆不压缩没意义，不分堆也可以压缩（整张表就是一堆）。

加上这两样之后，一条查询的完整骨架长这样（子句必须按这个顺序书写，虽然不是按这个顺序执行）：

SELECT <columns>
FROM <table>
JOIN <table>
ON <condition>
WHERE <condition>
GROUP BY <columns>
ORDER BY <columns>
LIMIT <n>;

5. GROUP BY：把行分成堆

GROUP BY <columns> 做的事只有一件：把 <columns> 上取值相同的行归到同一堆里。 然后——这是最重要的一句——每一堆最终只输出一行。

所以你可以直接算出输出有几行：看 GROUP BY 那些列一共有多少种不同的取值组合。 9 行的 records 按 division 分堆，division 只有 Computer / Accounting / Administration 三种值，输出就是 3 行。

在 records 上真跑一遍

sqlite> SELECT division, COUNT(*) FROM records GROUP BY division;
division|COUNT(*)
Accounting|2
Administration|2
Computer|5

裸列（bare column）：SELECT 里放了不该放的东西

现在看讲义里那个专门用来「踩坑」的例子。原表 9 行：

sqlite> SELECT name, division FROM records;
Alyssa P Hacker|Computer
Ben Bitdiddle|Computer
Cy D Fect|Computer
Eben Scrooge|Accounting
Lana Lambda|Administration
Lem E Tweakit|Computer
Louis Reasoner|Computer
Oliver Warbucks|Administration
Robert Cratchet|Accounting

那么下面这条会输出什么？

SELECT name, division
FROM records
GROUP BY division;

本机实测结果与幻灯片一致：

sqlite> SELECT name, division FROM records GROUP BY division;
name|division
Eben Scrooge|Accounting
Lana Lambda|Administration
Alyssa P Hacker|Computer

像 name 这样既不在 GROUP BY 里、也不被聚合函数包住的列，SQLite 管它叫裸列（bare column）。

sqlite> SELECT division, name, MAX(salary) FROM records GROUP BY division;
Accounting|Eben Scrooge|75000
Administration|Oliver Warbucks|150000
Computer|Ben Bitdiddle|60000

sqlite> SELECT division, name, COUNT(*) FROM records GROUP BY division;
Accounting|Eben Scrooge|2
Administration|Lana Lambda|2
Computer|Alyssa P Hacker|5

按多列分堆

GROUP BY 后面可以跟多列，这时「同一堆」的意思是这几列的值全都相同：

sqlite> SELECT fur, height FROM dogs GROUP BY fur, height;
curly|31
curly|32
long|26
long|46
long|47
short|28
short|35
short|52

（这里用的是 HW 06 的 dogs 表，8 只狗。因为 8 只狗的 (fur, height) 组合两两不同，所以分出了 8 堆，每堆 1 行——分堆分得太细，等于没分。）

GROUP BY 后面也可以跟表达式，不一定是列名。比如按身高的十位数分档：

sqlite> SELECT height/10, COUNT(*) FROM dogs GROUP BY height/10;
2|2
3|3
4|2
5|1

SQL 里 整数/整数 是整除，所以 26/10 是 2。这条查询说：身高 20 多的有 2 只，30 多的 3 只，40 多的 2 只，50 多的 1 只。

GROUP BY 和 DISTINCT 的关系

不带任何聚合函数的 GROUP BY，效果跟 SELECT DISTINCT 一样——都是「把重复的取值合成一个」：

sqlite> SELECT division FROM records GROUP BY division;
Accounting
Administration
Computer

sqlite> SELECT DISTINCT division FROM records;
Computer
Accounting
Administration

内容一样，顺序不一样（DISTINCT 按遇到的先后，GROUP BY 这里恰好按字母序）。两者的意图不同，选哪个看你想表达什么：

看到题目里出现「每个」「各自」「分别」，条件反射就该是 GROUP BY。

6. 聚合函数：把一堆行压成一个值

聚合函数是唯一能「同时看见多行」的东西。它们全都是这个形状：吃一组行的某一列，吐一个值。

COUNT(*) 和 COUNT(column) 技术上有区别，但不在 61A 的范围内——讲义明确说了这一点。想知道区别的话：COUNT(*) 数行，COUNT(column) 数这一列非 NULL 的值。造一张有空值的小表就能看出来：

sqlite> CREATE TABLE t (a TEXT, b INTEGER);
sqlite> INSERT INTO t VALUES ('x', 1), ('y', NULL), ('z', 3), ('x', NULL);
sqlite> SELECT COUNT(*), COUNT(b), COUNT(DISTINCT a) FROM t;
4|2|3

4 行，但 b 只有 2 个非空值；a 列有 x, y, z, x 四个值、3 种取值。61A 的作业表基本没有 NULL，所以两种写法结果一样，用 COUNT(*) 就行。

没有 GROUP BY 时，整张表就是一个组

这是理解聚合函数最省事的一条心法。SELECT COUNT(*) FROM records; 之所以返回一行，不是因为有什么特殊规则，而是因为「不分堆」就等于「所有行归成一堆」，一堆一行。

分堆之后，每一堆各算一次

把上一步的 WHERE 换成 GROUP BY，就从「算一个部门」变成「一次算完所有部门」：

sqlite> SELECT division, MAX(salary), MIN(salary), AVG(salary), SUM(salary), COUNT(*)
   ...> FROM records GROUP BY division;
division|MAX(salary)|MIN(salary)|AVG(salary)|SUM(salary)|COUNT(*)
Accounting|75000|18000|46500.0|93000|2
Administration|150000|25000|87500.0|175000|2
Computer|60000|25000|38000.0|190000|5

验证 Computer 那一行：5 个人，工资 40000/60000/35000/25000/30000。最大 60000 ✓，最小 25000 ✓，和 190000 ✓，平均 38000 ✓，人数 5 ✓。同一次分堆可以喂给任意多个聚合函数——数据库只扫一遍，每个函数各自累加自己的东西。

MAX / MIN 对字符串也管用

sqlite> SELECT MAX(name), MIN(name) FROM records;
Robert Cratchet|Alyssa P Hacker

按字典序比较。字符串列上的 MAX 通常没什么实际意义，但知道它不报错就行。

sqlite> SELECT MAX(salary), name, division FROM records;
150000|Oliver Warbucks|Administration

SELECT name, salary FROM records ORDER BY salary DESC LIMIT 1;

sqlite> SELECT division, COUNT(*) FROM records WHERE COUNT(*) > 1 GROUP BY division;
misuse of aggregate: COUNT()

sqlite> SELECT COUNT(*), SUM(salary), MAX(salary) FROM records WHERE salary > 1000000;
0|NULL|NULL

sqlite> SELECT division, COUNT(*) FROM records WHERE salary > 1000000 GROUP BY division;
<没有任何输出>

COUNT(DISTINCT c) 和 SELECT DISTINCT c

两者容易混。SELECT DISTINCT division FROM records; 给你有哪些取值，SELECT COUNT(DISTINCT division) FROM records; 给你有几种取值：

sqlite> SELECT DISTINCT division FROM records;
Computer
Accounting
Administration

sqlite> SELECT COUNT(DISTINCT division) FROM records;
3

顺带注意 SELECT DISTINCT 的输出没有排序（这里是 Computer 排在最前，因为 Alyssa 是表里第一行）。这也再次说明：要顺序就写 ORDER BY。

7. HAVING：给「组」用的 WHERE

问题：哪些主管手底下管着不止一个人？

第一步好办，按 supervisor 分堆数人数：

sqlite> SELECT supervisor, COUNT(*) FROM records GROUP BY supervisor;
supervisor|COUNT(*)
Alyssa P Hacker|1
Ben Bitdiddle|3
Eben Scrooge|1
Lana Lambda|1
Oliver Warbucks|3

第二步要把 COUNT(*) = 1 的那三行去掉。直觉上该用 WHERE，但 WHERE 在分堆之前跑，那时候还没有「组」这个东西，更没有 COUNT(*) 这个值。所以 SQL 另给了一个子句：

sqlite> SELECT supervisor, COUNT(*) FROM records
   ...> GROUP BY supervisor HAVING COUNT(*) > 1;
supervisor|COUNT(*)
Ben Bitdiddle|3
Oliver Warbucks|3

HAVING 里能写什么

HAVING 的条件不一定要含聚合函数，写普通列也合法（虽然那种情况通常该用 WHERE）：

sqlite> SELECT division, COUNT(*) FROM records
   ...> GROUP BY division HAVING division LIKE 'A%';
Accounting|2
Administration|2

也可以在 HAVING 里用一个没出现在 SELECT 里的聚合函数——「按什么筛」和「输出什么」是两码事：

sqlite> SELECT division, COUNT(*) FROM records
   ...> GROUP BY division HAVING MAX(salary) > 70000;
Accounting|2
Administration|2

这条查询问的是「哪些部门里有人年薪超过 70000」，输出的却是这些部门的人数。Computer 最高才 60000，所以被筛掉了。

另外，SQLite 允许 HAVING 引用 SELECT 里定义的别名：

sqlite> SELECT division, AVG(salary) AS avg_sal FROM records
   ...> GROUP BY division HAVING avg_sal > 40000;
Accounting|46500.0
Administration|87500.0

sqlite> SELECT division FROM records HAVING COUNT(*) > 1;
a GROUP BY clause is required before HAVING

把「哪个部门人最多」写完整

导读里那四个问题，现在只剩最后一个没答。「人最多」这种「取第一名」的需求，SQL 里的标准做法不是 HAVING，而是先聚合、再排序、再截断：

SELECT division, COUNT(*) AS n
FROM records
GROUP BY division
ORDER BY n DESC
LIMIT 1;

division|n
Computer|5

这里 ORDER BY 排的是聚合之后的那张 3 行的表，不是原始的 9 行——这正是执行顺序把 ORDER BY 放在第 7 步的意义。

8. 更新后的执行顺序

SQL 是声明式（declarative）语言：你写的是「我要什么」，不是「怎么算」。所以它不是从上往下执行的。加上 GROUP BY 和 HAVING 之后，完整顺序是八步：

把这张表读懂，本讲的四个「为什么」全都不用背了：

写查询的固定套路

上一讲给过一个「按问题清单写查询」的方法，现在补上两条，正好和执行顺序一一对应：

注意提问的顺序就是执行的顺序，而不是书写的顺序。按这个顺序想，写出来的时候再按 SQL 要求的语法顺序排列即可。

sqlite> SELECT division, COUNT(*) FROM records HAVING COUNT(*) > 1 GROUP BY division;
near "GROUP": syntax error

9. 实战：把中文需求翻译成查询

讲义这一节让大家在本地装好 SQLite，用 Spotify 和 IMDb 两个数据集练手（仓库地址 github.com/phrdang/cs61a-su26-lec23，跟着 README 的 Installation 步骤走）。那两份数据本页拿不到，但套路完全一样，所以下面改用 HW 06 里现成的 dogs / parents / sizes 三张表来练——这也正是你接下来要做的作业。

dogs:    name       fur      height
         ace        long     26
         bella      short    52
         charlie    long     47
         daisy      long     46
         ellie      short    35
         finn       curly    32
         ginger     short    28
         hank       curly    31

parents: parent  child        sizes:  size      min  max
         ace     bella                toy       24   28
         ace     charlie              mini      28   35
         daisy   hank                 medium    35   45
         finn    ace                  standard  45   60
         finn    daisy
         finn    ginger
         ellie   finn

需求一：每种毛发的狗有几只、平均多高？只要数量超过 2 的，按平均身高从高到低排

按套路逐条问：

SELECT fur, COUNT(*) AS n, AVG(height) AS avg_h
FROM dogs
GROUP BY fur
HAVING COUNT(*) > 2
ORDER BY avg_h DESC;

fur|n|avg_h
long|3|39.666666666666664
short|3|38.333333333333336

验证：curly 只有 finn 和 hank 两只，被 HAVING 筛掉了。long 是 ace 26 / charlie 47 / daisy 46，平均 $(26+47+46)/3 = 119/3 \approx 39.67$ ✓。short 是 bella 52 / ellie 35 / ginger 28，平均 $115/3 \approx 38.33$ ✓。39.67 > 38.33，所以 long 排前面 ✓。

需求二：哪些家长有不止一个孩子？他们最高的孩子多高？

这次要 join：孩子的名字在 parents.child，身高在 dogs.height，两张表靠名字对上。

SELECT p.parent, COUNT(*) AS num_kids, MAX(d.height)
FROM parents AS p
JOIN dogs AS d
ON p.child = d.name
GROUP BY p.parent
HAVING COUNT(*) > 1;

parent|num_kids|MAX(d.height)
ace|2|52
finn|3|46

验证：ace 的孩子是 bella(52) 和 charlie(47)，最高 52 ✓。finn 的孩子是 ace(26)、daisy(46)、ginger(28)，最高 46 ✓。

需求三：WHERE 和 HAVING 同时上场

「只看身高超过 30 的狗，每种毛发各有几只」——「身高超过 30」是行的条件，必须用 WHERE，而且它在分堆之前就把矮狗踢掉了：

sqlite> SELECT fur, COUNT(*) FROM dogs WHERE height > 30 GROUP BY fur;
curly|2
long|2
short|2

对比不加 WHERE 的结果 curly|2 long|3 short|3：long 少了 ace(26)，short 少了 ginger(28)。

需求四：结合上一讲的 join 条件

「按体型分类，每一类有几只狗」。体型不是 dogs 表里的列，得跟 sizes 表连接（条件是 min < height <= max，这正是 HW 06 第一题的条件）：

SELECT size, COUNT(*) AS n
FROM dogs, sizes
WHERE height > min AND height <= max
GROUP BY size
HAVING n > 2;

size|n
mini|3
standard|3

这条查询把本讲和上一讲的东西全用上了：隐式 join（FROM 里写两张表）、WHERE 里的 join 条件、GROUP BY、聚合函数、HAVING。去掉 HAVING 的完整结果是 mini|3 standard|3 toy|2，medium 一只都没有——ellie 身高 35，按 height > min AND height <= max 落进了 mini（28,35]，不是 medium（35,45]。边界值落在哪一侧，取决于你用 > 还是 >=，这是 HW 06 最容易错的地方。

聚合查询写错了怎么调

聚合查询有个讨厌的地方：它经常不报错，只是给你一个错的数。 出错时最有效的办法是把 GROUP BY 那一行连同聚合函数一起删掉，先把「分堆之前长什么样」看清楚。

拿需求二举例。假如结果不对，先看中间表：

sqlite> SELECT p.parent, d.name, d.height
   ...> FROM parents AS p JOIN dogs AS d ON p.child = d.name;
parent|name|height
ace|bella|52
ace|charlie|47
daisy|hank|31
finn|ace|26
finn|daisy|46
finn|ginger|28
ellie|finn|32

7 行，每行是「一个家长 + 他的一个孩子的完整信息」。看到这张表，剩下的事就一目了然了：按 parent 分堆 ⇒ ace 2 行、daisy 1 行、finn 3 行、ellie 1 行；COUNT(*) 就是孩子数，MAX(d.height) 就是最高孩子的身高。

本讲小结

磁盘上的表
   │ FROM / JOIN … ON …        ← 数据从哪来，怎么对应
   ▼
  行
   │ WHERE / LIKE              ← 逐行筛（此处不能用聚合函数）
   ▼
  更少的行
   │ GROUP BY                  ← 分堆！单位从「行」变成「组」
   ▼
  组
   │ HAVING                    ← 整组整组地筛（此处可以用聚合函数）
   ▼
  更少的组
   │ SELECT / AS               ← 一组输出一行；聚合函数在这里求值
   ▼
  输出表
   │ ORDER BY / LIMIT          ← 排序、截断
   ▼
  最终结果

动手练习

答案都在本机 SQLite 上跑过。用到的表就是本页第 3 节的 records 和第 9 节的 dogs / parents / sizes。

1. 不看答案，说出输出有几行

下面三条查询各输出多少行？

(a) SELECT COUNT(*) FROM records;
(b) SELECT COUNT(*) FROM records GROUP BY division;
(c) SELECT title, COUNT(*) FROM records GROUP BY title;

Big Wheel|1
Chief Accountant|1
Programmer|2
Programmer Trainee|1
Scrivener|1
Secretary|1
Technician|1
Wizard|1

2. 找出下面这条查询的两处错误

SELECT division, name, AVG(salary) AS avg_sal
FROM records
WHERE COUNT(*) > 1
HAVING avg_sal > 30000
GROUP BY division;

misuse of aggregate: COUNT()

near "GROUP": syntax error

SELECT division, AVG(salary) AS avg_sal
FROM records
GROUP BY division
HAVING COUNT(*) > 1 AND AVG(salary) > 30000;

Accounting|46500.0
Administration|87500.0
Computer|38000.0

3. 写一条查询：每种毛发里，最高和最矮的狗差多少？只要差距超过 15 的

SELECT fur, MAX(height) - MIN(height) AS spread
FROM dogs
GROUP BY fur
HAVING MAX(height) - MIN(height) > 15;

fur|MAX(height) - MIN(height)
curly|1
long|21
short|24

4. 只统计 Computer 部门的人，按主管分堆数人数——结果为什么是 3 行而不是 1 行？

SELECT supervisor, COUNT(*) AS n
FROM records
WHERE division = 'Computer'
GROUP BY supervisor;

supervisor|n
Alyssa P Hacker|1
Ben Bitdiddle|3
Oliver Warbucks|1

5. 判断对错

1. GROUP BY 会让输出按分组列排好序，所以不用再写 ORDER BY。
2. 只要 SELECT 里有聚合函数，就必须写 GROUP BY。
3. SELECT division, COUNT(*) FROM records WHERE salary > 1000000 GROUP BY division; 会输出 3 行，每行 COUNT(*) 是 0。
4. HAVING 里可以用没出现在 SELECT 里的聚合函数。