SQL笔试经典40题全解析：从核心考点到性能优化实战

1. 项目概述：为什么是这40道题？

如果你正准备面试数据分析师、后端开发或者任何需要和数据库打交道的岗位，刷SQL题几乎是必经之路。市面上题库浩如烟海，但“SQL笔试经典40题”这个名号，在圈内流传了十几年，至今仍是检验SQL功底的“试金石”。我第一次接触这套题还是刚入行那会儿，当时被里头的几道题卡得怀疑人生，但也正是通过死磕它们，我才真正理解了SQL的集合思维和逻辑拆解。这套题之所以经典，不在于它用了多炫酷的窗口函数或最新语法，而在于它精准地覆盖了SQL笔试中80%以上的核心考点和思维模式。

简单来说，这40题是一个高度浓缩的“考点地图”。它模拟了一个典型的电商业务数据库（通常包含学生、课程、成绩、教师，或者员工、部门、薪水等表），通过层层递进的查询需求，考察你对连接（JOIN）、子查询、聚合函数、分组（GROUP BY）、排序（ORDER BY）、条件筛选（CASE WHEN/HAVING）等核心操作的掌握深度。更重要的是，它考察你能否将复杂的业务问题（比如“查询每门课成绩最好的前两名学生”）拆解成一步步可执行的SQL逻辑。对于初学者，它是系统学习的绝佳路径；对于有经验者，它是查漏补缺、梳理知识体系的利器。接下来，我将把这40题拆解成几个核心模块，带你不仅写出答案，更理解背后的“为什么”。

2. 核心考点与解题思路全解构

面对40道题，盲目地一道一道刷效率很低。我的经验是，先按“考点”和“解题模式”将它们分类。当你发现不同题目背后是同一套思维模型时，学习效率会倍增。

2.1 基础查询与条件过滤：构建你的WHERE思维

大约有10道题属于这个范畴。它们看起来简单，却是所有复杂查询的基石。这里的关键不是记住WHERE的语法，而是建立“集合过滤”的思维。

核心思路：把数据库表想象成一张Excel表格，你的WHERE条件就是筛选器。但和Excel手动筛选不同，SQL要求你用精确的逻辑语言描述筛选条件。常见的坑点在于对NULL值的处理。例如，“查询没学过‘张三’老师课的学生姓名”。很多新手会直接写NOT IN (SELECT ...)，但如果子查询返回结果包含NULL，整个NOT IN条件可能会返回空结果集。更稳妥的做法是使用NOT EXISTS或在子查询中排除NULL。

实操心得：在写WHERE条件时，我养成了一个习惯——先问自己：“这个条件是否可能为NULL？”如果可能，就必须显式处理：WHERE column = ‘value’ OR column IS NULL或者使用WHERE ISNULL(column, ‘default’) = ‘value’。

2.2 多表连接（JOIN）的进阶玩法

这是40题中的重头戏，至少有15道题涉及多表连接。连接不是简单的拼表，其本质是根据关联键，将来自不同表的记录匹配组合成一个新的结果集。

1. 内连接（INNER JOIN） vs 左连接（LEFT JOIN）的选择这是面试高频考点。核心区别在于你对结果集完整性的要求。

• INNER JOIN：取交集。只返回两个表中能匹配上的记录。当你确定关联键在两边都存在且非空时使用。例如，“查询每个学生的每门课的成绩”，学生和成绩记录通常都是存在的。
• LEFT JOIN：以左表为基准。返回左表所有记录，即使右表没有匹配。右表无匹配则补NULL。经典场景是“查询所有学生的选课情况，包括没选课的学生”。这里“所有学生”是需求核心，所以学生表必须作为左表。

2. 自连接（Self-Join）的妙用这是容易让人困惑但极其强大的技巧。当问题涉及同一表内记录的相互比较时，就要想到自连接。例如，“查询比‘张三’工资高的所有员工”。你需要将员工表（假设为emp）想象成两个独立的副本：一个用于定位张三（e1），一个用于比较其他员工（e2）。

SELECT e2.* FROM emp e1, emp e2 WHERE e1.name = ‘张三‘ AND e2.salary > e1.salary;

自连接的核心是给同一个表起不同的别名（alias），然后在它们之间建立关联条件。

2.3 聚合函数与分组统计：超越SUM和COUNT

聚合函数（SUM,COUNT,AVG,MAX,MIN）配合GROUP BY是数据分析的支柱。40题中大量题目考察分组统计，难点往往在于分组键的选择和对HAVING子句的理解。

分组键（GROUP BY）的确定：一个黄金法则是，SELECT后面除了聚合函数之外的每一列，原则上都应该出现在GROUP BY子句中。例如，“查询每个部门每个岗位的平均工资”。分组键就是部门和岗位。

HAVING与WHERE的本质区别：这是另一个面试必问题。WHERE在分组前过滤原始行，它不能使用聚合函数。HAVING在分组后过滤分组结果，它专门用来对聚合值设定条件。例如，“查询平均成绩大于60分的学生学号”。

SELECT student_id, AVG(score) as avg_score FROM scores GROUP BY student_id HAVING AVG(score) > 60; -- HAVING过滤分组后的结果

如果写成WHERE AVG(score) > 60，语法上就是错误的。

进阶：多重分组与聚合有些题目要求先按一个维度分组，再在组内按另一个维度分析。例如，“查询每个班级中，男女生分别有多少人”。这需要按班级和性别两个字段进行分组。

SELECT class_id, gender, COUNT(*) as count FROM students GROUP BY class_id, gender;

2.4 子查询：化繁为简的嵌套艺术

子查询，即查询嵌套在另一个查询内部。它常用于无法通过单层JOIN或WHERE直接解决的问题。根据出现的位置，可分为标量子查询、列子查询和行子查询。

1. 标量子查询（返回单一值）通常用在SELECT列表或WHERE条件中，作为一个计算值或比较值。例如，“查询所有学生的姓名和其总成绩”。

SELECT name, (SELECT SUM(score) FROM scores s WHERE s.student_id = stu.id) as total_score FROM students stu;

这种写法可读性有时比JOIN更好，但要注意性能，特别是数据量大时。

2. 关联子查询（Correlated Subquery）这是子查询中的难点和重点。子查询的执行依赖于外层查询的当前行。例如，“查询每门课成绩最高的学生信息”。思路是：对于成绩表中的每一行，检查它的分数是否等于它所在课程的最高分。

SELECT * FROM scores s1 WHERE score = (SELECT MAX(score) FROM scores s2 WHERE s2.course_id = s1.course_id); -- 关键在这里：s1.course_id

关联子查询理解起来有点绕，但它是解决“组内比较”类问题的利器。

3. 使用EXISTS/NOT EXISTS代替IN当子查询可能返回大量结果或包含NULL值时，EXISTS通常更高效且语义更清晰。EXISTS只关心子查询是否有返回行，而不关心具体内容。例如，“查询选修了‘计算机科学’课程的学生”。

SELECT * FROM students s WHERE EXISTS (SELECT 1 FROM scores sc JOIN courses c ON sc.course_id = c.id WHERE sc.student_id = s.id AND c.name = ‘计算机科学‘);

2.5 窗口函数：现代SQL笔试的加分项

虽然经典40题诞生时窗口函数还不普及，但现在它已成为中高级SQL面试的标配。它能让你在分组聚合的同时，还能保留每一行的原始细节，实现排名、累加、移动平均等高级操作。

核心概念：OVER()子句定义了窗口的范围。PARTITION BY类似于GROUP BY，但不会将行折叠。ORDER BY决定了窗口内行的顺序。

经典应用场景：

1. 排名问题：“查询每门课成绩的前两名”。使用ROW_NUMBER()或RANK()。

   SELECT *, ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) as rank FROM scores WHERE rank <= 2; -- 注意：这里不能直接引用，需要嵌套一层

   需要写成子查询：

   SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) as rank FROM scores ) t WHERE t.rank <= 2;

2. 累计计算：“查询每个员工到当前月份的累计薪水”。使用SUM() OVER()。

   SELECT employee_id, month, salary, SUM(salary) OVER (PARTITION BY employee_id ORDER BY month) as cumulative_salary FROM salary_table;

注意事项：窗口函数执行顺序在WHERE和GROUP BY之后，在ORDER BY之前。所以你不能在WHERE中直接过滤窗口函数计算出的列（如上面的rank），必须使用子查询或公共表表达式（CTE）。

3. 高频难题精讲与避坑指南

接下来，我挑选几道公认的、容易出错的“经典40题”进行拆解，分享我的解题步骤和踩过的坑。

3.1 难题一：查询所有课程成绩均大于80分的学生

需求解析：这是典型的“全称量词”问题。SQL没有直接的“FOR ALL”语句，我们需要将其转化为“不存在一门课成绩小于等于80分”。

错误思路：SELECT student_id FROM scores GROUP BY student_id HAVING MIN(score) > 80。这个思路是正确的，而且是最优解之一。它利用了聚合函数的特性：如果一个学生所有成绩都大于80，那么他的最低成绩（MIN(score)）也必然大于80。

另一种思路（使用NOT EXISTS）：

SELECT DISTINCT s.name FROM students s WHERE NOT EXISTS ( SELECT 1 FROM scores sc WHERE sc.student_id = s.id AND sc.score <= 80 );

这个逻辑更直白：找不出任何一个该学生成绩小于等于80的记录。

避坑点：不要试图用COUNT去比较。例如HAVING COUNT(score > 80) = COUNT(score)，这在某些数据库里写法不标准，且效率低。

3.2 难题二：查询没学过“张三”老师所教任何一门课的学生

需求解析：这是“NOT IN”或“NOT EXISTS”的典型场景，但必须注意课程集合的获取。

步骤拆解：

1. 先找到“张三”老师教的所有课程ID。
2. 找到所有选了这些课程中任意一门的学生ID。
3. 从全体学生中排除这些学生。

SQL实现：

-- 方法1：使用NOT IN (注意子查询处理NULL) SELECT * FROM students WHERE id NOT IN ( SELECT DISTINCT student_id FROM scores WHERE course_id IN ( SELECT course_id FROM teaching WHERE teacher_id = (SELECT id FROM teachers WHERE name=‘张三‘) ) ); -- 更推荐方法2：使用NOT EXISTS SELECT * FROM students s WHERE NOT EXISTS ( SELECT 1 FROM scores sc JOIN teaching t ON sc.course_id = t.course_id JOIN teachers te ON t.teacher_id = te.id WHERE te.name = ‘张三‘ AND sc.student_id = s.id );

关键点：NOT EXISTS写法通常更安全，避免了NOT IN可能因NULL值导致的逻辑陷阱，而且关联子查询的写法逻辑链条更清晰。

3.3 难题三：查询每门课成绩最好的前两名学生

需求解析：这是分组Top-N问题，是面试超级高频题。在没有窗口函数的年代，解法非常巧妙（也绕），现在则推荐使用窗口函数。

解法一（使用窗口函数 ROW_NUMBER）：清晰高效

SELECT course_name, student_name, score FROM ( SELECT c.name as course_name, s.name as student_name, sc.score, ROW_NUMBER() OVER (PARTITION BY sc.course_id ORDER BY sc.score DESC) as rank FROM scores sc JOIN students s ON sc.student_id = s.id JOIN courses c ON sc.course_id = c.id ) t WHERE t.rank <= 2;

解法二（使用自连接和COUNT的传统方法）：理解思维思路：对于成绩表中的某一行a，如果同一门课下，分数比a高的记录数少于2个，那么a就是前两名。

SELECT s1.course_id, s1.student_id, s1.score FROM scores s1 WHERE ( SELECT COUNT(DISTINCT s2.score) FROM scores s2 WHERE s2.course_id = s1.course_id AND s2.score > s1.score ) < 2 ORDER BY s1.course_id, s1.score DESC;

这个解法能很好地锻炼你的关联子查询和集合思维，但在大数据量下性能不如窗口函数。

3.4 难题四：查询各科成绩最高分、最低分和平均分，并按平均分降序排列

需求解析：这是基础分组聚合，但考察格式化输出和对CASE WHEN的运用。有时会要求以“课程ID，课程名称，最高分，最低分，平均分，及格率，中等率，优良率，优秀率”的格式输出。

SQL实现：

SELECT c.id as 课程ID, c.name as 课程名称, MAX(sc.score) as 最高分, MIN(sc.score) as 最低分, ROUND(AVG(sc.score), 2) as 平均分, CONCAT(ROUND(SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), ‘%‘) as 及格率, CONCAT(ROUND(SUM(CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), ‘%‘) as 中等率, CONCAT(ROUND(SUM(CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), ‘%‘) as 优良率, CONCAT(ROUND(SUM(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), ‘%‘) as 优秀率 FROM scores sc JOIN courses c ON sc.course_id = c.id GROUP BY c.id, c.name ORDER BY 平均分 DESC;

核心技巧：这里大量使用了CASE WHEN表达式在聚合函数SUM中实现条件计数。SUM(CASE WHEN condition THEN 1 ELSE 0 END)等同于COUNT(IF(condition, 1, NULL))，是一种非常实用的技巧。

4. 从解题到实战：性能优化与思维提升

能写出正确答案只是第一步，在真实工作或处理海量数据时，查询性能至关重要。刷题时也要有意识地去思考优化。

4.1 索引：你的查询加速器

很多题目涉及WHERE和JOIN条件，这些字段通常是建立索引的候选。

• 等值查询（=）：在WHERE student_id = 1001或JOIN ... ON a.id = b.a_id的字段上建立索引，效果立竿见影。
• 范围查询（>, <, BETWEEN）：同样可以受益于索引，尤其是B-Tree索引。
• 排序和分组（ORDER BY, GROUP BY）：如果ORDER BY score或GROUP BY course_id经常出现，在这些字段上建立索引可以避免昂贵的文件排序（filesort）。

针对经典40题的数据表，我建议的索引策略如下：

-- 假设主表结构：students(id, name), courses(id, name), scores(id, student_id, course_id, score) CREATE INDEX idx_scores_sid ON scores(student_id); CREATE INDEX idx_scores_cid ON scores(course_id); CREATE INDEX idx_scores_cid_sid ON scores(course_id, student_id); -- 复合索引，对‘查询某学生某门课成绩‘类查询极佳 CREATE INDEX idx_scores_cid_score ON scores(course_id, score DESC); -- 对‘按课程查成绩排名‘类查询极佳

实操心得：索引不是越多越好。每个索引都会增加写操作（INSERT/UPDATE/DELETE）的开销。需要根据最频繁的查询模式来权衡。复合索引的列顺序至关重要，应遵循“最左前缀匹配原则”。

4.2 执行计划解读：看清数据库在想什么

当你发现查询变慢时，EXPLAIN命令是你的第一诊断工具。以MySQL为例，在SQL语句前加上EXPLAIN，数据库会告诉你它打算如何执行这条查询。

关键字段解读：

• type：访问类型，从好到坏大致是：system>const>eq_ref>ref>range>index>ALL。ALL（全表扫描）是我们要尽量避免的。
• key：实际使用的索引。如果为NULL，说明没用到索引。
• rows：预估需要扫描的行数。这个值越小越好。
• Extra：额外信息。出现Using filesort或Using temporary通常意味着性能瓶颈，需要优化。

例如，分析一个简单的连接查询：

EXPLAIN SELECT s.name, c.name, sc.score FROM students s JOIN scores sc ON s.id = sc.student_id JOIN courses c ON sc.course_id = c.id WHERE s.name = ‘张三‘;

通过查看EXPLAIN结果，你可以确认是否在s.name、sc.student_id、sc.course_id上正确使用了索引。

4.3 思维模式训练：如何面对一道新题

刷题的目的不是为了背答案，而是训练一种可迁移的解题思维。我的习惯是“四步法”：

1. 语义翻译：把中文业务需求，一字一句地翻译成逻辑描述。例如，“查询至少有一门课与学号为‘1001’的学生相同的其他学生”。翻译成：“先找出‘1001’学生选的所有课，再找出选了这些课中任意一门的学生，最后从这些学生中排除‘1001’本人。”
2. 逻辑拆解：将复杂的逻辑描述拆分成几个简单的、可顺序或嵌套执行的子步骤。如上例，可以拆成：a) 找课程集合；b) 找学生集合；c) 做差集。
3. 语法映射：将每个子步骤映射到SQL语法组件。a) 子查询；b)IN或JOIN；c)WHERE id != ‘1001‘。
4. 组装测试：将组件组装成完整的SQL，先在小数据集上验证结果是否正确，再思考是否有更优写法（如用EXISTS代替IN，用JOIN代替子查询）。

5. 常见错误与排查清单

根据我带新人和面试的经验，以下错误在初学者中极其普遍。你可以对照这个清单检查自己的代码。

最后，我的个人体会是，SQL学习是一个“先死后活”的过程。“死”是指初期要严格遵循语法，多做练习，把经典40题这样的标杆吃透，形成肌肉记忆。“活”是指在理解原理和思维模式后，能灵活应对各种复杂多变的业务查询需求。刷题时，不要满足于一种解法，多想想“还有没有别的写法？”“哪种写法性能更好？”。当你拿到一个新需求，能迅速在脑海中勾勒出查询的逻辑图，并转化为高效的SQL语句时，你就真正掌握了这门数据库沟通的语言。这套经典40题，常刷常新，每隔一段时间回头看看，总能有新的收获。