MySQL多表查询实战：电影演员票房中的SELECT、JOIN与聚合

电影、演员、票房这三张表凑在一起，是我见过最适合练 MySQL 数据查询的素材组合。它不像电商订单表那样字段繁多到劝退，也不像单表练习那样查完WHERE就没下文了——电影表给你维度、演员表给你多对多关系、票房表给你可计算的数值，一条SELECT语句能从最简单的单表筛选一路练到多表连接加分组聚合。这套基于电影、演员及票房应用的数据查询练习，核心就是把SELECT从"能查出东西"练到"能按业务问题精确查出东西"。不管你是刚学完SELECT * FROM的新手，还是能写 JOIN 但一遇到"每组取最大值"就卡壳的进阶选手，下面这些内容都能直接拿去对照练。

1. 先把数据场景和表结构吃透

1.1 四张表的定位与关系梳理

很多人做查询练习有个坏习惯：上来就写 SQL，写完发现结果不对，再回头猜字段含义，来回折腾半小时。我的建议是，动手前先花五分钟把表关系画清楚——不用画图，在纸上列四行就够。

这套场景的骨架是四张表：电影表（movie）存影片自身属性，演员表（actor）存演员自身属性，演出关系表（movie_actor）记录"谁演了哪部片子、演什么角色"，票房表（box_office）记录每部片子的上映日期、票房收入、制作成本和观影人次。

关键在于理解movie_actor 这张中间表存在的必要性。一部电影有多个演员，一个演员也参演多部电影，这是典型的多对多关系。关系型数据库不能在一个字段里塞数组，所以必须拆出一张只存"电影ID + 演员ID"的关联表。这张表看着简单，但它是后面所有"查询某演员参演的全部电影""统计每部片子的主演人数"这类题目的必经之路。

还有一点容易被忽略：票房表用 movie_id 做主键，意味着它和电影表是一对一关系。这个设计选择很实在——一部电影只有一条票房记录，用 movie_id 当主键天然保证了唯一性，也避免了有人往表里插重复的票房数据。如果换成自增 ID 做主键，你就得额外加唯一约束，多一层管理成本。

把关系理清后，画出来的结构是这样的：movie 和 box_office 一对一，movie 和 actor 通过 movie_actor 多对多。剩下的所有查询题，本质都是在这三条线上做文章。

1.2 一份可以直接抄走的建表与造数脚本

练习查询必须有数据。空表跑SELECT什么都验证不了，所以先把建表和插数的脚本准备好。我按最小可用原则设计字段，够用又不冗余，你可以直接复制执行。

CREATE DATABASE IF NOT EXISTS film_demo DEFAULT CHARSET utf8mb4; USE film_demo; CREATE TABLE movie ( movie_id INT PRIMARY KEY, title VARCHAR(100) NOT NULL, release_year YEAR, genre VARCHAR(30), director VARCHAR(50), duration_min INT, country VARCHAR(30), rating DECIMAL(3,1) ); CREATE TABLE actor ( actor_id INT PRIMARY KEY, actor_name VARCHAR(50) NOT NULL, gender CHAR(1), birth_year YEAR, nationality VARCHAR(30) ); CREATE TABLE movie_actor ( movie_id INT, actor_id INT, role_name VARCHAR(50), is_lead TINYINT DEFAULT 0, PRIMARY KEY (movie_id, actor_id), FOREIGN KEY (movie_id) REFERENCES movie(movie_id), FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ); CREATE TABLE box_office ( movie_id INT PRIMARY KEY, release_date DATE, domestic_gross DECIMAL(12,2), overseas_gross DECIMAL(12,2), budget DECIMAL(12,2), tickets_sold INT, FOREIGN KEY (movie_id) REFERENCES movie(movie_id) );

字段类型上有几个地方值得说一说，这些是练习时容易被判错、实际项目里也容易踩的点：

rating用DECIMAL(3,1)而不是FLOAT，因为评分要参与AVG()计算和比较。浮点数在 MySQL 里存8.7可能变成8.699999，你写WHERE rating = 8.7就查不出来，这是新手最常见的"明明有数据却查不到"的原因之一。DECIMAL是精确值类型，AVG(rating)出来的结果也更符合预期。

票房金额用DECIMAL(12,2)，12 位有效数字、2 位小数，最大能存到百亿级别，国内加上海外票房完全够用，也不会因为精度问题在求和时出现尾差。

movie_actor的主键设成(movie_id, actor_id)的联合主键，这一手同时解决了两件事：保证同一部片子里同一个演员不会被重复插入，以及让"按电影查演员"和"按演员查电影"两个方向都能走主键索引。

紧接着把示例数据灌进去。数据量不用大，十几部电影、二十来个演员、三十来条演出关系就足够覆盖所有查询题型，而且结果你肉眼就能验证对错——这点非常重要，数据量大到几十万行的时候，你根本没法判断结果对不对，练习效果反而变差。

INSERT INTO movie VALUES (1,'长风渡月',2019,'剧情','陈默',128,'中国',8.6), (2,'深海回声',2021,'科幻','林越',142,'中国',8.1), (3,'第八号站台',2018,'悬疑','陈默',115,'中国',7.9), (4,'夏日邮局',2020,'爱情','苏青',106,'中国',8.3), (5,'孤岛纪年',2022,'剧情','周砚',134,'中国',8.8), (6,'迷城追踪',2017,'动作','林越',121,'中国',7.2), (7,'雪线之上',2023,'冒险','周砚',147,'中国',8.4), (8,'旧照片',2016,'剧情','苏青',98,'中国',7.6), (9,'北纬四十度',2021,'纪录片','陈默',89,'中国',9.0), (10,'无人区来信',2019,'悬疑','周砚',127,'中国',8.0); INSERT INTO actor VALUES (101,'沈听澜','F',1985,'中国'), (102,'陆行舟','M',1982,'中国'), (103,'苏白露','F',1990,'中国'), (104,'程亦寒','M',1978,'中国'), (105,'江晚晴','F',1993,'中国'), (106,'顾岩','M',1988,'中国'), (107,'温桦','F',1995,'中国'), (108,'谢承宇','M',1980,'中国'); INSERT INTO movie_actor VALUES (1,101,'沈月如',1),(1,102,'周砚清',1),(1,106,'老陈',0), (2,103,'林深',1),(2,104,'舰长',1),(2,108,'研究员B',0), (3,102,'陆征',1),(3,105,'苏晓',1), (4,105,'江愿',1),(4,107,'温小满',1),(4,101,'客串老师',0), (5,104,'岛主',1),(5,103,'小满',1),(5,106,'渔民甲',0), (6,106,'顾锋',1),(6,108,'反派B',1), (7,101,'领队',1),(7,104,'向导',1),(7,107,'队员C',0), (8,103,'成年女儿',1),(8,102,'父亲',1), (9,108,'旁白',0), (10,105,'记者',1),(10,106,'司机',1),(10,102,'站长',0); INSERT INTO box_office VALUES (1,'2019-07-12',52300.00,8100.00,12000.00,1250), (2,'2021-08-06',98700.00,26400.00,32000.00,2340), (3,'2018-11-23',31200.00,4300.00,9000.00,780), (4,'2020-02-14',44800.00,5600.00,8000.00,1090), (5,'2022-09-30',132600.00,41800.00,40000.00,3080), (6,'2017-06-16',27600.00,7200.00,15000.00,690), (7,'2023-01-20',76400.00,12900.00,22000.00,1780), (8,'2016-04-08',18900.00,2100.00,6000.00,470), (9,'2021-11-05',8700.00,600.00,3000.00,215), (10,'2019-12-13',39500.00,4900.00,10000.00,960);

注意box_office的金额单位我用的是"万元"，这是一个刻意的设计。真实项目里一定要在字段注释或者文档里写清单位，我见过不止一次因为"一个按元存、一个按万元存"导致汇总表差了四个数量级的翻车事故。练习时先约定好单位，养成习惯。

2. 单表查询：SELECT 的执行顺序比语法顺序更要紧

2.1 为什么 WHERE 里不能用 SELECT 起的别名

这是每个学 SQL 的人都会撞一次的墙。你写了这么一条：

SELECT title, domestic_gross + overseas_gross AS total_gross FROM box_office WHERE total_gross > 100000;

报错Unknown column 'total_gross' in 'where clause'。明明上面刚起的别名，下面就不认识了。

原因在于 SQL 的逻辑执行顺序和我们书写的顺序并不一致。写的时候是从上往下：SELECT…FROM…WHERE…GROUP BY…HAVING…ORDER BY…LIMIT。但数据库内部的逻辑处理顺序是这样的：

SELECT排在第 6 位，WHERE排在第 3 位。别名在第 6 步才产生，第 3 步当然不认识它。而ORDER BY在第 8 步，此时别名已经存在，所以ORDER BY total_gross DESC完全合法。

搞懂这张顺序表的好处远不止解决这一个报错。它解释了另外几个常见现象：WHERE里不能用聚合函数（聚合发生在GROUP BY之后，WHERE之前还没分组），HAVING里可以用聚合函数但通常不建议放非聚合的行级条件（因为它在GROUP BY之后才执行，过滤越早性能越好），以及COUNT(*)配合LEFT JOIN时统计值和直觉不一致（因为WHERE在连接之后执行，会把LEFT JOIN补出来的 NULL 行干掉）。

正确写法很简单——要么把表达式重复一遍，要么套一层子查询：

SELECT title, domestic_gross + overseas_gross AS total_gross FROM box_office WHERE domestic_gross + overseas_gross > 100000;

重复表达式的写法在 MySQL 里没有性能问题，优化器能识别。但字段一多、表达式一长就很啰嗦，这时候用子查询或者 CTE 会更清晰，第 4 节会展开讲。

2.2 条件过滤：BETWEEN、IN、LIKE 各自的适用边界

把WHERE用对，是单表查询的核心。三个高频操作符各有各的坑。

BETWEEN 是闭区间。WHERE release_year BETWEEN 2018 AND 2021包含 2018 和 2021 两年，等价于>= 2018 AND <= 2021。这个事实听上去是常识，但一旦字段换成DATETIME类型，坑立刻出现：WHERE release_date BETWEEN '2021-01-01' AND '2021-12-31'会漏掉2021-12-31 14:30:00这一天的数据，因为'2021-12-31'被补全成2021-12-31 00:00:00。安全的写法是查左闭右开区间：

WHERE release_date >= '2021-01-01' AND release_date < '2022-01-01'

IN 相比一串 OR 更可读，也更利于优化器。WHERE genre IN ('剧情','悬疑','科幻')和三个OR连写逻辑等价，但IN在 MySQL 8.0 里会被优化成更高效的索引查找方式（range访问），而且当列表元素超过十几个时，IN写起来省事得多。需要注意的是IN里可以放子查询，但数据量大时IN (子查询)的性能可能远不如EXISTS或 JOIN，这一点在第 4 节细说。

LIKE 的百分号位置决定能不能走索引。title LIKE '夏日%'是前缀匹配，能用上索引；title LIKE '%夏日%'是全文模糊匹配，索引直接失效，得扫全表。这不是说模糊匹配不能用——数据量小的练习表无所谓——但你得知道代价在哪。真实场景里如果确实需要"包含"查询，可以考虑全文索引或者额外的分词检索方案，而不是硬用LIKE '%...%'。

还有一组必须单独拎出来说的：NULL的所有比较都返回UNKNOWN。WHERE nationality = NULL、WHERE nationality <> NULL都是错的，永远查不出数据，必须用IS NULL或IS NOT NULL。这条规则在实际项目中造成的 bug 数量，我觉得能排进 SQL 问题前三。

2.3 排序与分页：ORDER BY 的隐含规则和 LIMIT 的边界

ORDER BY看着简单，但有几个行为不写清楚绝对会误解。

MySQL 默认把NULL当最小值，升序时排最前、降序时排最后。如果业务上要求"空值排最后"，得显式写：

ORDER BY rating IS NULL, rating DESC;

这里rating IS NULL返回 0 或 1，IS NULL的结果是 0（非空）时排在前面，1（空）时排后面，起到了"空值沉底"的作用。这个技巧在展示类查询里很实用。

多列排序是从左到右逐级生效的。ORDER BY genre ASC, rating DESC的含义是：先按类型分组排，同类型内部再按评分从高到低。千万别理解成"两个字段平行排序"，那是不可能的——排序必须有一个优先级链。

分页的LIMIT offset, size在大偏移量下会变慢。LIMIT 100000, 20的执行方式是先扫描并丢弃前 100000 行，再取 20 行。数据量一大就是灾难。常见的优化方案是"基于游标的分页"，记住上一页最后一条记录的排序键，下次用WHERE id > 上次最后值 LIMIT 20，把OFFSET彻底绕开。练习阶段可能体会不到差别，但知道这个坑存在，将来写真实业务代码时就不容易踩。

顺便给一个练习里很有用的算式：要取第 N 页（每页 size 条），偏移量offset = (N - 1) * size。比如每页 3 条、要第 4 页，就是LIMIT 9, 3。

3. 聚合与分组：票房统计类查询的看家本领

3.1 GROUP BY 与聚合函数的搭档关系

单表查询筛的是"行"，分组聚合算的是"类"。票房场景天然适合聚合——按年份算平均票房、按类型算总票房、按导演算作品数，全是这个套路。

一个最容易出问题的地方是ONLY_FULL_GROUP_BY模式。MySQL 5.7 之后默认开启，规则是：SELECT列表里出现的非聚合列，必须出现在GROUP BY里，否则报错。比如：

-- 报错：title 既不是聚合列，也不在 GROUP BY 里 SELECT genre, title, AVG(rating) FROM movie GROUP BY genre; -- 正确：要么不用 title，要么给它一个聚合 SELECT genre, COUNT(*) AS cnt, AVG(rating) AS avg_rating FROM movie GROUP BY genre;

有些人图省事直接把自己的 sql_mode 改掉关了这个检查，这属于自找麻烦。因为在宽松模式下SELECT genre, title … GROUP BY genre虽然能跑，但返回的title是随机某一行，结果不可预测——同一句 SQL 换个执行计划结果就变了。老老实实按规则写。

聚合函数的 NULL 行为必须记住，这是最容易算错的点：

AVG那个行为特别坑。假设有个演员的birth_year是 NULL，当你要算"每个年代出生的演员平均出生年份"，AVG(birth_year)会把这个 NULL 直接忽略掉，分母少算一个人。业务上是应该算 0 还是应该排除？这得由你来定，但数据库默认帮你选了"排除"，你就得知道。

COUNT(*)和COUNT(col)的差异在多表连接里会放大成严重错误。像"统计每部电影有几位演员参演"，如果写成：

SELECT m.movie_id, COUNT(a.actor_id) AS actor_cnt FROM movie m LEFT JOIN movie_actor ma ON m.movie_id = ma.movie_id LEFT JOIN actor a ON ma.actor_id = a.actor_id GROUP BY m.movie_id;

用COUNT(a.actor_id)时，没有演员的电影a.actor_id是 NULL，计数为 0，这恰好是想要的结果。但如果误写成COUNT(*)，那部没有演员的电影也会被算成 1（因为LEFT JOIN补了一行全 NULL 的记录）。这个差异在练习里经常被判错，值得反复念叨。

3.2 WHERE 和 HAVING 到底谁管什么

一句话概括：WHERE过滤行，HAVING过滤组。执行顺序上WHERE在分组前，HAVING在分组后，这就决定了它们能用的东西不一样。

WHERE里不能写聚合函数，因为那时还没分组，聚合结果不存在。HAVING里可以写聚合函数，也可以写SELECT里起的聚合别名（MySQL 允许，标准 SQL 不允许，但既然用 MySQL 就可以用）。

看一个票房场景的典型需求："找出总票房超过 5 亿、且影片数量不少于 2 部的类型"。

SELECT m.genre, COUNT(*) AS film_cnt, ROUND(SUM(b.domestic_gross + b.overseas_gross), 2) AS total_gross FROM movie m JOIN box_office b ON m.movie_id = b.movie_id WHERE m.release_year >= 2018 GROUP BY m.genre HAVING total_gross > 50000 AND film_cnt >= 2 ORDER BY total_gross DESC;

三个过滤层各司其职：WHERE m.release_year >= 2018先砍掉 2018 年之前的数据（行级），GROUP BY按类型归堆，HAVING再筛掉不达标的分组（组级）。

性能上的重要区别：能放WHERE的条件绝不要放HAVING。因为WHERE先执行，过滤掉的行根本不进入分组计算，运算量直接减少；而HAVING必须先把所有行分组算完，再筛掉整组，前面的计算全白做了。这个差别在小表上看不出来，几十万行的时候可能就是几百毫秒和几秒的差距。

3.3 票房指标计算：从原始字段推导业务指标

练聚合最好不要只写SUM和COUNT，那样太机械。真实业务里的指标几乎都是算出来的，动手推导一遍参数，理解会深很多。

指标一：总票房。domestic_gross + overseas_gross。别直接假设有total_gross字段，表里没有就是没有，得自己算。这也是为什么很多练习判题会用"总票房超过 X"来卡人——考的就是你会不会做字段运算。

指标二：平均票价。总票房 / tickets_sold。这里有个单位陷阱：票房存的是万元，人次存的是万人，直接除得到的是"万元/万人"，数值上等于"元/人"，恰好是对的。但如果票房按元存、人次按人存，那就是元/人，也是对的。只有当两边单位不一致（一边万元一边人）时才会错。所以写之前先在纸上把量纲对一遍：

万元 / 万人 = 元/人 ✓ 元 / 人 = 元/人 ✓ 万元 / 人 = 万分之一元/人 ✗ 要乘 10000

指标三：投资回报率。总票房 / budget。这个指标比单纯看票房高低更有信息量——一部 5 亿票房、成本 4 亿的片子，远不如一部 1 亿票房、成本 2000 万的片子赚钱。计算时注意budget不能为 0，实际写查询要加WHERE budget > 0把它排除掉，否则要么结果无穷大，要么在别的数据库里直接除零报错。

指标四：单馆产出、场均人次这类需要额外院线数据的指标，这个场景里没有，但思路可以迁移——所有"总产出 / 某类资源数量"的比率型指标，套路都一样，关键是搞清楚分子分母的量纲和口径。

把这些指标算出来之后，再用ORDER BY排个序，就能得到"按投资回报率排名""按平均票价排名"这类很有业务价值的榜单。这类查询几乎就是真实业务报表的雏形。

4. 多表连接：三张表怎么串才不出错

4.1 INNER JOIN 和 LEFT JOIN 的选型逻辑

连接类型的选择是查询正确性的第一道关。选错了不会报错，只会悄悄地少数据或者多数据，比报错更危险。

INNER JOIN只保留两边都能匹配上的行。用它来回答"有哪些电影同时有票房记录"这种问题，逻辑上是"交集"。

LEFT JOIN保留左表全部行，右表匹配不上就补 NULL。用它来回答"列出所有电影及其票房，没票房的也要列出来"。

判断标准其实很简单：问自己"主表是谁，主表的数据要不要全部保留"。要保留全部，就用LEFT JOIN把主表放左边；只要匹配上的，就用INNER JOIN。

看一个考试里经典对比题："查询每部电影的标题、导演和总票房，没有票房数据的电影总票房显示为空"。

SELECT m.title, m.director, b.domestic_gross + b.overseas_gross AS total_gross FROM movie m LEFT JOIN box_office b ON m.movie_id = b.movie_id;

如果把LEFT JOIN换成INNER JOIN，没有票房记录的片子就直接从结果里消失了。这两种结果集的区别,取决于题目要求的口径，但绝大多数"列出全部…"的描述都指向LEFT JOIN。

一个高频的连锁陷阱：LEFT JOIN之后再在WHERE里写右表的过滤条件，会导致LEFT JOIN退化成INNER JOIN。比如：

-- 本意是"所有电影，票房超过 1 亿的显示金额，否则显示空" -- 但这样写，没票房的电影全没了 FROM movie m LEFT JOIN box_office b ON m.movie_id = b.movie_id WHERE b.domestic_gross > 50000;

原因还是执行顺序——WHERE在连接之后执行，NULL 去和 50000 比较返回UNKNOWN，该行被过滤。正确做法是把条件挪到ON里：

FROM movie m LEFT JOIN box_office b ON m.movie_id = b.movie_id AND b.domestic_gross > 50000;

条件放在ON里是"连接时筛选"，不满足条件的右表行补 NULL 保留左表；放在WHERE里是"连接后筛选"，NULL 行被干掉。这一条我认为是 JOIN 部分最值得记住的细节，没有之一。

4.2 多对多关系：一部电影的多位演员怎么展示

movie_actor这张表的价值在多对多查询里体现得最明显。题目常问："查询参演演员总数超过 2 人的电影"。

SELECT m.movie_id, m.title, COUNT(ma.actor_id) AS actor_cnt FROM movie m JOIN movie_actor ma ON m.movie_id = ma.movie_id GROUP BY m.movie_id, m.title HAVING COUNT(ma.actor_id) > 2 ORDER BY actor_cnt DESC;

注意GROUP BY里同时写了movie_id和title。技术上只写movie_id就够了（因为它是主键，函数依赖能推出 title），MySQL 8.0 的ONLY_FULL_GROUP_BY也认这个函数依赖。但显式写出来可读性更好，而且换到部分数据库（比如某些旧版本）就必须要写，养成习惯不吃亏。

如果题目要求把演员名字横向拼成一列，用GROUP_CONCAT：

SELECT m.title, GROUP_CONCAT(a.actor_name ORDER BY a.actor_id SEPARATOR ' / ') AS cast_list FROM movie m JOIN movie_actor ma ON m.movie_id = ma.movie_id JOIN actor a ON ma.actor_id = a.actor_id GROUP BY m.movie_id, m.title;

GROUP_CONCAT有两个默认值要注意：分隔符默认是逗号（可以改），长度默认限制 1024 字节（超了会被静默截断，不报错）。演员多的片子确实可能超，必要时用SET SESSION group_concat_max_len = 100000;调大，但改之前想清楚是不是真的需要把几十个名字拼一行——展示层做这件事往往更合适。

反向查询同样重要："查询参演电影数量 ≥ 3 部的演员"。把movie_actor和actor连起来，按演员分组计数就行，逻辑结构完全对称，唯一变化是分组维度和连接对象。这类题目练多了会形成肌肉记忆：看到"每个 X 的 Y 数量"，条件反射就是"按 X 分组，对 Y 计数"。

4.3 子查询、EXISTS 与 JOIN 的等价改写

同一道题往往有三种以上写法，选哪个不只是风格问题，还涉及性能和可读性。

拿这个需求举例："查询参演过'剧情'类电影的演员姓名"。

写法一，IN子查询：

SELECT actor_name FROM actor WHERE actor_id IN ( SELECT ma.actor_id FROM movie_actor ma JOIN movie m ON ma.movie_id = m.movie_id WHERE m.genre = '剧情' );

写法二，EXISTS：

SELECT a.actor_name FROM actor a WHERE EXISTS ( SELECT 1 FROM movie_actor ma JOIN movie m ON ma.movie_id = m.movie_id WHERE ma.actor_id = a.actor_id AND m.genre = '剧情' );

写法三，JOIN+DISTINCT：

SELECT DISTINCT a.actor_name FROM actor a JOIN movie_actor ma ON a.actor_id = ma.actor_id JOIN movie m ON ma.movie_id = m.movie_id WHERE m.genre = '剧情';

三者结果一样，但有细微差别。IN子查询语义最直观，但 MySQL 对它的优化在历史上不太稳定，早期版本会把它改写成半连接（semi-join），某些情况性能反而不如 JOIN。EXISTS是"相关子查询"，外层每行执行一次，只要找到匹配就短路返回，在子查询结果集很大但外查询结果集较小时表现好。JOIN通常是最稳的，但必须加DISTINCT，否则一个演员参演多部剧情片就会重复出现。

我的实际选择习惯是：优先 JOIN，语义要求"存在性判断"且只关心有没有时用 EXISTS，IN留给简单的固定列表或者子查询结果集极小的情况。这个偏好没有什么理论绝对性，但跟着它走基本不会出大错。

5. 典型题型实战：从入门题到综合题逐层拆解

5.1 单表筛选与排序题

入门题通常是这个形态："查询 2020 年之后上映、评分不低于 8.0 的电影，按评分降序排列，评分相同的按上映年份升序排列，只取前 5 条"。

SELECT title, release_year, genre, director, rating FROM movie WHERE release_year > 2020 AND rating >= 8.0 ORDER BY rating DESC, release_year ASC LIMIT 5;

这种题看着简单，但判题系统往往卡细节：字段顺序、别名拼写、大小写、排序方向。我见过有人把ORDER BY rating DESC, release_year ASC写成ORDER BY rating, release_year DESC，结果列表整体不对。多字段排序时，每个字段后面的方向修饰必须紧跟自己那个字段，不能想当然地认为一个关键字管全局。

另外注意release_year > 2020和>= 2020的区别。"2020 年之后"是严格大于，包含 2020 年得用>=。中文题干里"以上/以后/之后"在计算机语境里的边界含义并不统一，遇到模棱两可的，先用SELECT把临界数据查出来看看业务上合不合理，比瞎猜强。

5.2 分组统计与排名题

进阶题会叠加聚合和排序："统计每个类型电影的平均评分和总票房，只统计票房记录存在的电影，按总票房降序排列，显示前三名"。

SELECT m.genre, COUNT(*) AS film_cnt, ROUND(AVG(m.rating), 2) AS avg_rating, ROUND(SUM(b.domestic_gross + b.overseas_gross), 2) AS total_gross FROM movie m JOIN box_office b ON m.movie_id = b.movie_id GROUP BY m.genre ORDER BY total_gross DESC LIMIT 3;

拆解一下这道题的关键决策点。为什么用JOIN而不是LEFT JOIN：题干写了"只统计票房记录存在的电影"，这是明确的交集语义，JOIN正确。如果题干是"统计每个类型，没有票房的按 0 计算"，那就必须LEFT JOIN加上IFNULL或者COALESCE兜底。

为什么AVG要包ROUND：AVG出来的结果是DECIMAL，位数可能很长，判题时字符串比对会挂。养成对浮点或精度类结果做四舍五入的习惯，一般在 1 到 2 位小数之间，具体看题干要求。

ORDER BY用别名还是表达式：这里用别名total_gross没问题，因为ORDER BY在SELECT之后执行。但如果同一句还要在HAVING里过滤这个值，HAVING也能用别名（MySQL 特性），而WHERE不行——还是那句老话，看执行顺序。

5.3 多表综合题：一次查询回答多个业务问题

综合题的典型形态："查询参演电影数不少于 2 部、且参演电影平均评分超过 8.0 的演员，显示演员姓名、参演电影数、平均评分，按平均评分降序"。

SELECT a.actor_name, COUNT(DISTINCT ma.movie_id) AS film_cnt, ROUND(AVG(m.rating), 2) AS avg_rating FROM actor a JOIN movie_actor ma ON a.actor_id = ma.actor_id JOIN movie m ON ma.movie_id = m.movie_id GROUP BY a.actor_id, a.actor_name HAVING film_cnt >= 2 AND avg_rating > 8.0 ORDER BY avg_rating DESC;

这道题有三个值得说的细节。

第一，COUNT(DISTINCT ma.movie_id)里的DISTINCT是不是多余？在这个具体场景下确实多余，因为movie_actor的主键是(movie_id, actor_id)，同一个演员在同一部片子里不可能出现两行。但如果你在查询里还JOIN了别的表（比如票房表），那一个演员在多部电影、每部电影一条票房记录，COUNT就可能重复计数。加上DISTINCT是低成本的保险，代价几乎为零，收益是结果一定对，我倾向于加上。

第二，GROUP BY里带上a.actor_name。虽然a.actor_id是主键能函数依赖推出actor_name，但显式列出来更直观，也避免不同版本行为差异。

第三，HAVING里直接用了两个别名film_cnt和avg_rating。这是 MySQL 的便利特性，标准 SQL 不允许，但既然在用 MySQL 就不用纠结。如果你写的脚本将来要迁移到别的数据库，老老实实把聚合表达式重复一遍。

5.4 用窗口函数处理"每组取第一名"

如果有"查询每个类型评分最高的那部电影"这类需求，传统写法定是子查询配MAX：

SELECT m.genre, m.title, m.rating FROM movie m JOIN ( SELECT genre, MAX(rating) AS max_rating FROM movie GROUP BY genre ) t ON m.genre = t.genre AND m.rating = t.max_rating;

这个写法的隐患是：如果同一类型里有两部电影评分相同且都是最高，两部都会被选出来。有时候这是期望行为（并列第一都展示），有时候不是（只要一条）。另外如果最高分有 5 部并列而业务只要 1 部，还得加额外的去重逻辑。

MySQL 8.0 之后可以用窗口函数写得干净利落：

SELECT genre, title, rating FROM ( SELECT genre, title, rating, ROW_NUMBER() OVER (PARTITION BY genre ORDER BY rating DESC) AS rn FROM movie ) t WHERE rn = 1;

ROW_NUMBER()保证每组只出一行，RANK()会把并列的都留下（下一名跳号），DENSE_RANK()并列的都留下（下一名不跳号）。三个函数的选择取决于业务口径：要唯一代表用ROW_NUMBER，要"并列第一都算"用RANK或DENSE_RANK，二者区别在于并列之后的编号是否连续。这个区别在奖金分配、等级评定这类场景里会造成实际差异，不是学术争论。

窗口函数不能直接写在WHERE里（因为WHERE执行早于窗口函数计算），必须套一层子查询或者 CTE，这一点和聚合函数别名不能用在WHERE是同一个道理。

6. 报错与结果不符的排查实录

6.1 高频报错速查表

练习时遇到的错误大致就这几类，整理成表方便对号入座：

ambiguous（字段歧义）这个错误在电影场景里出现频率极高，因为movie和box_office都有movie_id，movie和movie_actor也都有。只要连接两张表，凡是同名字段就必须带前缀。有人图省事只在 SELECT 里加前缀，WHERE 和 ON 里不加，照样报错——规则是全句生效的，不是只看 SELECT 那一块。

Subquery returns more than 1 row也很典型。比如"查询和《长风渡月》同导演的电影"：

-- 错误示范 SELECT title FROM movie WHERE director = (SELECT director FROM movie WHERE title = '长风渡月');

如果同名的电影有两部，子查询返回两行，=直接炸。哪怕数据里只有一部，这种写法也是脆弱的。正确姿势是IN：

SELECT title FROM movie WHERE director IN (SELECT director FROM movie WHERE title = '长风渡月') AND title <> '长风渡月';

那个AND title <> '长风渡月'别漏了，否则结果里会包含它自己——这是另一个"结果多了一条"的常见来源。

6.2 不报错但结果不对的五步排查法

最难受的情况是 SQL 跑通了、有结果、但和预期不符。这种情况我一般按下面五步走，基本都能定位。

第一步，把SELECT换回SELECT *并去掉所有聚合。先看一眼原始连接结果长什么样。很多"数字对不上"的问题，在这一步就能看到原因——比如本该一对一的连接实际出来了一对多，行数被放大了。

第二步，数行数。SELECT COUNT(*)对比各表数据量，看看连接之后的行数是变多了还是变少了。变多通常是漏了连接条件或者连接条件不唯一（笛卡尔积），变少通常是INNER JOIN丢数据或者WHERE条件误杀。

第三步，检查NULL。把可疑字段单独SELECT出来，用IS NULL数一下有多少空值。空值参与计算的结果几乎总是反直觉的。

第四步，检查GROUP BY的粒度。分组字段是不是漏了？比如"按电影统计演员数"却只GROUP BY genre，那统计的其实是"每个类型的演员数"。分组粒度和业务问题的粒度必须严格对齐。

第五步，用已知答案的小数据集验证。把范围缩到一部电影、一个演员，手工算一遍期望值，再和 SQL 结果对。范围缩小之后问题往往一眼可见。

这五步里，第一步是最有效的，我把这条当铁律用。真实工作里遇到复杂的多表查询对不上数，全都是从"把中间结果摊开看"这一招开始定位的。

6.3 几个我踩过的具体坑和避坑技巧

坑一：ORDER BY遇到中文排序不符合预期。默认排序规则下，中文按编码排，结果和拼音顺序完全不同。如果业务要求按拼音排序，得指定COLLATE或者加一个拼音字段。练习里一般不考，但真实报表里是刚需。

坑二：DECIMAL参与除法后精度丢失。SELECT budget / tickets_sold FROM box_office返回的小数位数由除法规则决定，可能和你预期的不一样。要控制精度显式CAST或ROUND，比如ROUND(budget / tickets_sold, 2)。另外除法要注意分母为 0，虽然 MySQL 的/遇 0 返回NULL不报错，但结果变成 NULL 会让后续计算全部塌掉。

坑三：LIMIT和ORDER BY的顺序不能颠倒。LIMIT 5 ORDER BY rating DESC是语法错误，必须ORDER BY在前。而且如果只写LIMIT不写ORDER BY，取到的"前 5 条"顺序是不确定的，每次执行可能不一样。凡是要"前 N 条"，ORDER BY必须写全，把并列情况也定下来，否则就是赌博。

坑四：字符串比较默认不区分大小写。MySQL 默认排序规则下'abc' = 'ABC'返回 1，这是很多人意想不到的。如果业务要求严格区分，得用BINARY或者指定_bin结尾的排序规则。

坑五：COUNT(*)的统计口径。前面提过，再强调一遍：COUNT(*)数的是行数，不是某个字段的非空值数量。多表连接后，一行的实际含义可能已经不是"一部电影"而是"一条电影-演员关系"，此时COUNT(*)数出来的是关系数，COUNT(DISTINCT movie_id)才是电影数。这个区别在写统计报表的时候至关重要。

坑六：别在练习表上迷信"执行计划"。十几行数据的表，MySQL 优化器有一半时间直接全表扫描，因为你加了索引它也不用——数据量太小，扫表比走索引还快。这时候纠结EXPLAIN的type字段没意义。想验证索引效果，得自己造几万行数据再看。知道这一点，能省下不少对着小表EXPLAIN自我怀疑的时间。

我自己练这套东西的时候有个习惯，每写出一条能跑的查询，就故意改坏它一次——把LEFT JOIN换成INNER JOIN，把WHERE里的条件挪到ON，把COUNT(*)换成COUNT(col)，然后看结果怎么变。改坏一次，对这个语法的理解就深一层，比看书上十行解释管用得多。这套电影、演员、票房的数据查询，真正值钱的不是把题做对，而是通过一次次"故意改错"把SELECT的执行顺序、JOIN的保留逻辑、聚合的 NULL 行为刻进脑子里。这几个点通了，换成电商订单、社交关注、物流运单，套路是一模一样的。