03 · 计算、汇总与多表查询
对应第 10～18 章，使用第一单元的数据。先执行 USE showdocs_mysql_book;。本单元把问题从「查出几行」推进到「这几行代表什么、为什么算出来是这个数」。
先记住数据的粒度：learners 一行是一位学员，courses 一行是一门课程，enrollments 一行是一位学员对一门课程的报名。报名明细共有 3 行，但只有 2 位不同学员。连接、汇总或去重时，一旦忘记这个区别，就容易写出能执行却算错钱的 SQL。
第 10～11 章：表达式与函数
计算列只改变查询结果
假设页面要显示「课程名 / 分类」和九折价格，不需要给表新增两列。可以在读取时计算：
SELECT course_id, CONCAT(title, ' / ', category) AS label,
ROUND(price * 0.90, 2) AS discounted_price
FROM courses ORDER BY course_id;
SELECT learner_id, COALESCE(email, '未填写') AS contact
FROM learners ORDER BY learner_id;第一条的结果是：
| course_id | label | discounted_price |
|---|---|---|
| 101 | SQL 入门 / 数据库 | 35.10 |
| 102 | MySQL 事务 / 数据库 | 71.10 |
| 103 | Linux 入门 / 运维 | 44.10 |
| 104 | SQL 报表 / 数据库 | 53.10 |
price * 0.90 对每一行进行乘法，ROUND(..., 2) 四舍五入到两位小数，AS 给结果列命名。查询结束后，101 在表中的价格仍是 39.00。要保存新价格，必须显式执行 UPDATE。
别名方便显示和后续排序，但同一层查询的 WHERE 不能直接引用刚定义的计算列别名。筛选九折价不超过 40 元，可以在 WHERE 中重复表达式，或先用派生表生成这一列：
SELECT course_id, discounted_price
FROM (
SELECT course_id, ROUND(price * 0.90, 2) AS discounted_price
FROM courses
) AS discounts
WHERE discounted_price <= 40
ORDER BY course_id;结果只有 101 / 35.10。括号里的查询产生临时的关系结果，discounts 是它在外层的名字，不是新建了一张持久表。
字符串函数遇到 NULL 怎么办
第二条查询的 COALESCE(email, '未填写') 从左到右返回第一个非 NULL 的值，因此林二显示「未填写」。它只改变显示结果，没有把占位文字写进邮箱列。
SELECT CONCAT('邮箱：', NULL) AS missing_label,
CONCAT('邮箱：', COALESCE(NULL, '未填写')) AS readable_label,
CONCAT_WS(' / ', '林二', NULL, '学员') AS joined_label,
COALESCE('', '未填写') AS empty_text;四列依次为 NULL、邮箱：未填写、林二 / 学员、空字符串。CONCAT 的任一参数为 NULL 时结果为 NULL；CONCAT_WS 在分隔符非 NULL 的前提下跳过后续 NULL 参数；COALESCE 不会把空字符串当作 NULL。如果业务同时把空串视作缺失，可先用 NULLIF(email, '') 转成 NULL。
数值、日期和条件表达式各有用途
| 任务 | 常用表达式 | 应注意的含义 |
|---|---|---|
| 展示两位小数 | ROUND(price, 2) | 舍入是显示或业务规则，不等于浮点数会变精确；金额列仍应使用 DECIMAL |
| 按月展示日期 | DATE_FORMAT(enrolled_at, '%Y-%m') | 返回格式化文本，可用于展示或分组 |
| 计算日期差 | DATEDIFF('2026-10-03', '2026-10-01') | 返回 2，表示日期部分之间相差两天 |
| 加一天 | DATE_ADD('2026-10-01', INTERVAL 1 DAY) | 比拼接日期字符串可靠 |
| 避免除零 | numerator / NULLIF(denominator, 0) | 分母为零时结果变为 NULL，是否再显示为零由业务决定 |
CASE 可以把数值映射为业务标签：
SELECT course_id, price,
CASE
WHEN price < 50 THEN '低于50元'
WHEN price < 70 THEN '50至不足70元'
ELSE '70元及以上'
END AS price_band
FROM courses ORDER BY course_id;101、103 属于第一档，104 属于第二档，102 属于第三档。条件从上到下判断，命中后就采用该分支；没有 ELSE 且没有条件成立时返回 NULL。
筛选十月报名宜写 enrolled_at >= '2026-10-01' AND enrolled_at < '2026-11-01'。这直接对原列做范围比较，也能在以后改为带时间字段时保留正确边界。DATE_FORMAT(enrolled_at, '%Y-%m') = '2026-10' 虽容易理解，却给每行包了一层函数，通常不能直接用普通日期索引做同样的范围定位。
第 12 章：先确定统计口径
SELECT COUNT(*) AS learners, COUNT(email) AS known_emails FROM learners;
SELECT COUNT(*) AS registrations, COUNT(DISTINCT learner_id) AS learners,
SUM(paid) AS revenue, AVG(paid) AS average_paid FROM enrollments;第一条为 3 人、2 个已知邮箱。第二条为 3 条报名、2 位报名学员、收入 157.00、平均每次报名实付约 52.333333。这里的平均数是 (39 + 79 + 39) / 3，不是每位付费学员的平均消费。
COUNT 数的究竟是什么
| 表达式 | 本例结果 | 统计口径 |
|---|---|---|
COUNT(*)，查询 learners | 3 | 所有学员行，邮箱为空也计数 |
COUNT(email)，查询 learners | 2 | 邮箱非 NULL 的行数，不是不同邮箱数 |
COUNT(*)，查询 enrollments | 3 | 报名次数 |
COUNT(DISTINCT learner_id)，查询 enrollments | 2 | 至少有一次报名的不同学员数 |
AVG(paid)，查询 enrollments | 约 52.333333 | 所有非 NULL 实付记录的平均数 |
AVG(DISTINCT paid)，查询 enrollments | 59.00 | 先把金额值去重，再对 39、79 求平均 |
最后一行通常不是想要的口径：两个不同学员恰好都支付 39 元，不应该因此只保留一笔。DISTINCT 去重的是选定值，不能代替业务去重。
每位付费学员平均消费应写成收入除以不同学员数：
SELECT SUM(paid) / NULLIF(COUNT(DISTINCT learner_id), 0)
AS revenue_per_paying_learner
FROM enrollments;结果是 78.50。如果想算「所有注册学员的人均收入」，分母就应为 learners 中的 3 人，而非报名表中的 2 人；SQL 无法替你决定这两个业务指标哪个正确。
没有匹配行不等于结果一定是零
SELECT COUNT(*) AS registrations, SUM(paid) AS raw_revenue,
COALESCE(SUM(paid), 0) AS display_revenue,
AVG(paid) AS average_paid
FROM enrollments WHERE course_id = 104;得到一行：0 / NULL / 0 / NULL。不带 GROUP BY 的聚合即使输入为空也会给出一行；COUNT 返回零，SUM、AVG 返回 NULL。加上 GROUP BY course_id 后，因为没有任何组，结果则是零行。报表中「无数据」和「金额为零」是否等价，要先确定再使用 COALESCE。
第 13 章：分组前过滤与分组后过滤
SELECT course_id, COUNT(*) AS registrations, SUM(paid) AS revenue
FROM enrollments
WHERE enrolled_at >= '2026-10-01'
GROUP BY course_id
HAVING COUNT(*) >= 2
ORDER BY revenue DESC, course_id;预期只返回 101，报名人数 2、收入 78.00。可以把这条查询拆成六步理解；这是理解结果的逻辑顺序，不表示优化器必须按这个物理步骤执行：
FROM读报名明细，一行代表一次报名。WHERE先筛选日期，保留三条符合条件的明细。GROUP BY course_id把两条 101 放进一组，一条 102 放进另一组。- 每组计算
COUNT(*)、SUM(paid)，得到101 / 2 / 78和102 / 1 / 79。 HAVING COUNT(*) >= 2去掉只有一条报名的 102 组。ORDER BY给剩下的结果排序。
WHERE COUNT(*) >= 2 不合法，因为此时还没有组可数。反过来，虽然有些原始列条件能放在 HAVING 中表达，筛选明细的条件仍应尽量写在 WHERE，把两种责任分清楚。
为什么不能随便选一列
下面是一个错误思路，请不要作为正常示例执行：
SELECT course_id, learner_id, SUM(paid)
FROM enrollments GROUP BY course_id;101 这一组既有学员 1 又有学员 2，应显示哪一个 learner_id？开启默认的 ONLY_FULL_GROUP_BY 时，这种没有确定答案的选择会被拒绝。关闭模式可能让查询通过，但不会让业务含义变正确。
正确方法取决于问题：想看每门课收入，就不选择学员；想看每门课中每位学员的收入，就按两个字段分组。MySQL 也能识别部分函数依赖，例如由主键唯一确定的同表字段，但不能据此假设任意非分组列都能选。
SELECT course_id, learner_id, SUM(paid) AS paid_by_learner
FROM enrollments
GROUP BY course_id, learner_id
ORDER BY course_id, learner_id;结果三行，分组键不同意味着统计粒度不同。GROUP BY 不保证结果顺序；即使本次输出恰好有序，展示时也应明确 ORDER BY。
第 14 章：用存在性表达筛选
SELECT c.course_id, c.title FROM courses AS c
WHERE NOT EXISTS (
SELECT 1 FROM enrollments AS e WHERE e.course_id = c.course_id
)
ORDER BY c.course_id;预期返回无人报名的 103、104。相关子查询通过 c.course_id 引用外层行。可以逐门课推导：101 找到两条报名，EXISTS 为真，NOT EXISTS 为假；102 同样被排除；103、104 找不到报名，因此保留。SELECT 1 表示只检查有无记录，数字 1 不参与报名条件。
三种子查询解决不同形状的问题
| 类型 | 常见写法 | 对结果形状的要求 |
|---|---|---|
| 标量子查询 | price > (SELECT AVG(price) ...) | 一列、至多一行；零行按 NULL 处理，多行会报错 |
| 集合子查询 | course_id IN (SELECT course_id ...) | 此处需要一列，可有多行 |
| 存在性子查询 | EXISTS (SELECT 1 ...) | 只判断是否存在行，不把行值当作单个数使用 |
SELECT course_id, title, price
FROM courses
WHERE price > (SELECT AVG(price) FROM courses)
ORDER BY course_id;
SELECT course_id, title FROM courses
WHERE course_id IN (
SELECT course_id FROM enrollments WHERE learner_id = 1
)
ORDER BY course_id;第一条课程均价为 (39 + 79 + 49 + 59) / 4 = 56.50，因此返回 102 和 104。第二条返回学员 1 报名的 101、102。
用 = (SELECT course_id FROM enrollments WHERE learner_id = 1) 会因为返回两行而报错。不要随意加 LIMIT 1 隐藏错误：那会把「学员报名的所有课」变成「其中一门课」，业务问题已经改变。
NOT EXISTS 与 NOT IN 的选择
上一单元已经解释，x NOT IN (101, NULL) 对很多 x 得到 UNKNOWN，行会被过滤。本例 enrollments.course_id 声明为 NOT NULL，因此使用 NOT IN 也能得到未报名课程；面对允许空值的子查询结果时，应明确排除 NULL，或用 NOT EXISTS 直接表达「找不到匹配行」。
相关子查询只是逻辑上依赖外层行，不能仅凭写法断言它一定逐行执行且很慢。优化器可能改写执行方式；性能比较应看执行计划和真实数据。
第 15～16 章：连接与零记录
SELECT l.learner_name, c.title, e.paid
FROM enrollments AS e
JOIN learners AS l ON l.learner_id = e.learner_id
JOIN courses AS c ON c.course_id = e.course_id
ORDER BY e.learner_id, e.course_id;
SELECT c.course_id, c.title, COUNT(e.learner_id) AS registrations,
COALESCE(SUM(e.paid), 0) AS revenue
FROM courses AS c
LEFT JOIN enrollments AS e ON e.course_id = c.course_id
GROUP BY c.course_id, c.title
ORDER BY c.course_id;内连接把关联的事实拼起来
第一条返回：
| learner_name | title | paid |
|---|---|---|
| 陈一 | SQL 入门 | 39.00 |
| 陈一 | MySQL 事务 | 79.00 |
| 林二 | SQL 入门 | 39.00 |
以报名为起点，沿 learner_id 找到一位学员，沿 course_id 找到一门课。两个被连接的键都是主键，所以每条报名恰好匹配一位学员、一门课程，最终仍是三行。这里出现两个「陈一」是因为他报了两门课，不是连接出错。
如果没有关联条件而做 learners CROSS JOIN courses，三位学员与四门课会得到 12 种组合。这可用于生成「所有可能组合」，但不是已经发生的报名。发现行数膨胀时，先检查连接键和关系基数，不要立即加 DISTINCT 掩盖问题。
外连接为什么能保留零报名课程
第二条以 courses 为左表。101 匹配两行，102 匹配一行，103、104 各产生一行右表字段全部为 NULL 的补位记录。聚合前一共五行，聚合后四行：
| course_id | registrations | revenue |
|---|---|---|
| 101 | 2 | 78.00 |
| 102 | 1 | 79.00 |
| 103 | 0 | 0.00 |
| 104 | 0 | 0.00 |
COUNT(*) 会把补位行也数成 1；COUNT(e.learner_id) 不数 NULL，因此能得到零。选右表的非空键来计数，比选择本来就可为空的业务字段可靠，否则真实记录也可能被漏掉。
ON 和 WHERE 放错位置会改变结果
只统计 10 月 2 日及之后的报名，同时保留所有课程：
SELECT c.course_id, COUNT(e.learner_id) AS registrations
FROM courses AS c
LEFT JOIN enrollments AS e
ON e.course_id = c.course_id
AND e.enrolled_at >= '2026-10-02'
GROUP BY c.course_id
ORDER BY c.course_id;结果为 101→1、102→1、103→0、104→0。日期在 ON 中定义「哪些报名允许匹配」；无法匹配的左表课程仍会留下。如果把该日期条件搬到 WHERE，103、104 的日期是 NULL，比较结果为 UNKNOWN，补位行会被删掉。这里的外连接便失去了保留零报名的作用。
若只想显示「数据库」课程，则 WHERE c.category = '数据库' 是合理的：它明确筛选需要输出的左表课程，与右表报名是否存在无关。
自连接是给同一张表分配两个角色
找出同类别的课程组合，每一对只出现一次：
SELECT a.course_id AS first_course, b.course_id AS second_course
FROM courses AS a
JOIN courses AS b
ON a.category = b.category AND a.course_id < b.course_id
ORDER BY first_course, second_course;得到 (101,102)、(101,104)、(102,104)。a、b 指同一张表的两个逻辑角色。< 同时排除了课程与自身配对，以及 (102,101) 这样的镜像重复。
显式 ON 更方便审查业务关系。NATURAL JOIN 会自动使用两表所有同名列作为连接条件；将来增加同名字段可能悄悄改变结果，不适合用来省去对连接条件的思考。
多个一对多关系怎样避免重复汇总
假设以后又有课程评论表，一门课两条报名、三条评论，直接同时连接两张明细表会产生六行，报名金额可能被重复三次。解决办法通常是先把各自明细汇总成「每课一行」，再连接课程表。当前数据可先练习一侧预聚合：
SELECT c.course_id, COALESCE(r.registrations, 0) AS registrations,
COALESCE(r.revenue, 0) AS revenue
FROM courses AS c
LEFT JOIN (
SELECT course_id, COUNT(*) AS registrations, SUM(paid) AS revenue
FROM enrollments GROUP BY course_id
) AS r ON r.course_id = c.course_id
ORDER BY c.course_id;得到相同的四行统计。关键不是「子查询总比连接好」，而是进入下一次连接前，先把每份数据的粒度固定下来。
第 17 章：组合兼容的结果集
SELECT course_id FROM courses WHERE category = '数据库'
UNION
SELECT course_id FROM courses WHERE price < 50
ORDER BY course_id;第一个分支得到 101、102、104，第二个得到 101、103；UNION 去掉重复编号，最终得到 101～104。改成 UNION ALL 后，101 会出现两次，总共五行。
连接通常横向补充列，UNION 则把兼容结果纵向叠放。两个分支需要相同列数，并按列的位置组合；列名相同不代表会自动对齐列。如果第一列一个分支是编号、另一个分支是名称，即使 MySQL 能转换类型，业务含义也很可能错了。
SELECT course_id, '数据库分类' AS reason
FROM courses WHERE category = '数据库'
UNION
SELECT course_id, '价格低于50' AS reason
FROM courses WHERE price < 50
ORDER BY course_id, reason;这次即使用 UNION，101 仍会出现两行，因为 reason 不同，完整结果行不重复。去重范围是所有输出列，不能把它理解成「按第一列去重」。结果列名由第一个查询决定；末尾的 ORDER BY 排整个结果，并不保证先显示第一分支的行。
不需要去重时，UNION ALL 能清楚表达保留全部记录的意图，也避免不必要的去重工作。但不能为了速度把确实需要去重的业务改成 ALL。
第 18 章：全文检索有专门的索引
在练习库中创建一次全文索引；重复执行创建语句会报索引已存在：
ALTER TABLE courses ADD FULLTEXT INDEX ft_summary (summary);
SELECT course_id, title FROM courses
WHERE MATCH(summary) AGAINST('+queries' IN BOOLEAN MODE)
ORDER BY course_id;默认全文检索配置下命中 101。MATCH(summary) 指定参与检索的列，AGAINST 提供检索词与模式；这里的列集合与全文索引相匹配。布尔模式中 +queries 表示必须包含这个词。
SELECT course_id, title,
MATCH(summary) AGAINST('queries' IN NATURAL LANGUAGE MODE) AS score
FROM courses
WHERE MATCH(summary) AGAINST('queries' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC, course_id;
SELECT course_id, title FROM courses
WHERE MATCH(summary) AGAINST('+sql -reports' IN BOOLEAN MODE)
ORDER BY course_id;两条在本练习数据下均命中 101。第二条要求包含 sql，同时排除包含 reports 的 104。自然语言模式计算相关度，具体分值依赖当前文档集合，不应硬编码成稳定业务值；布尔模式允许表达必须包含、排除等条件，也不能假设输出自动按相关度排序。
| 检索方式 | 主要问题 | 注意点 |
|---|---|---|
LIKE '%queries%' | 文本中是否有这段字符 | 不理解词语，前导通配符通常不利于普通 B-tree 索引定位 |
| 自然语言全文检索 | 哪些文档与这些词相关 | 使用词项与相关度，结果依赖分词及索引配置 |
| 布尔全文检索 | 哪些文档满足词项条件 | +、- 等运算符有专门语义 |
| 查询扩展 | 根据初次结果补充相关词再次检索 | 可能增加召回，也可能带入不想要的语义 |
全文检索受最小词长、停用词和解析器影响。用很短的词或停用词测试却没有结果，不必立即认定 SQL 有误。这里特意使用英文简介；中文连续文本需要评估官方 ngram 解析器，不能把英文分词效果直接套过去。
完成后清理本单元增加的索引，便于从头重复实验：
ALTER TABLE courses DROP INDEX ft_summary;练习与核对
练习一：包括没有报名的人
输出所有学员及报名课程数，结果应为 陈一→2、林二→1、周三→0。再改成只统计课程 101，仍保留三位学员，结果应为 1、1、0。
参考答案与推导
SELECT l.learner_id, l.learner_name, COUNT(e.course_id) AS registrations
FROM learners AS l
LEFT JOIN enrollments AS e ON e.learner_id = l.learner_id
GROUP BY l.learner_id, l.learner_name
ORDER BY l.learner_id;
SELECT l.learner_id, l.learner_name, COUNT(e.course_id) AS registrations
FROM learners AS l
LEFT JOIN enrollments AS e
ON e.learner_id = l.learner_id AND e.course_id = 101
GROUP BY l.learner_id, l.learner_name
ORDER BY l.learner_id;以学员为保留侧，数报名表的非空键；限制报名匹配范围的条件放在 ON。WHERE e.course_id = 101 会把周三过滤掉。
练习二：找出累计消费超过 100 元的学员
结果应只有陈一，总消费 118.00。这是筛选单条明细，还是筛选已经汇总后的组？
参考答案与推导
SELECT l.learner_id, l.learner_name, SUM(e.paid) AS total_paid
FROM learners AS l
JOIN enrollments AS e ON e.learner_id = l.learner_id
GROUP BY l.learner_id, l.learner_name
HAVING SUM(e.paid) > 100
ORDER BY total_paid DESC, l.learner_id;必须在 HAVING 中判断累计金额。若写 WHERE e.paid > 100，三条报名都会被排除，因为没有单笔超过 100 元。
练习三：课程收入是不是课程标价之和
101 的当前价格为 39 元，报名两次；102 的当前价格为 79 元，报名一次。本数据中 SUM(c.price) 与 SUM(e.paid) 恰巧相等。这个等式能长期成立吗？
答案是不能。课程调价、优惠、历史订单都可能使当前标价与实际收款不同。收入应由报名记录中的 paid 计算；当前课程价格描述的是现在的商品，不是过去支付的事实。
练习四：解释一行 NULL 与零行的区别
查询没有人报名的 104，单独 SUM(paid) 得到一行 NULL，按 course_id 分组则没有组、返回零行。若产品要求所有课程都显示零值，应从 courses 出发外连接，再对聚合结果做 COALESCE，不能只给报名表加一个 GROUP BY 就期望自动出现缺失课程。
继续阅读
- 知识专题
- 应用实践
- 知识专题