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决定了窗口内行的顺序。经典应用场景排名问题“查询每门课成绩的前两名”。使用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;累计计算“查询每个员工到当前月份的累计薪水”。使用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 EXISTSSELECT 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”的典型场景但必须注意课程集合的获取。步骤拆解先找到“张三”老师教的所有课程ID。找到所有选了这些课程中任意一门的学生ID。从全体学生中排除这些学生。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访问类型从好到坏大致是systemconsteq_refrefrangeindexALL。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 思维模式训练如何面对一道新题刷题的目的不是为了背答案而是训练一种可迁移的解题思维。我的习惯是“四步法”语义翻译把中文业务需求一字一句地翻译成逻辑描述。例如“查询至少有一门课与学号为‘1001’的学生相同的其他学生”。翻译成“先找出‘1001’学生选的所有课再找出选了这些课中任意一门的学生最后从这些学生中排除‘1001’本人。”逻辑拆解将复杂的逻辑描述拆分成几个简单的、可顺序或嵌套执行的子步骤。如上例可以拆成a) 找课程集合b) 找学生集合c) 做差集。语法映射将每个子步骤映射到SQL语法组件。a) 子查询b)IN或JOINc)WHERE id ! ‘1001‘。组装测试将组件组装成完整的SQL先在小数据集上验证结果是否正确再思考是否有更优写法如用EXISTS代替IN用JOIN代替子查询。5. 常见错误与排查清单根据我带新人和面试的经验以下错误在初学者中极其普遍。你可以对照这个清单检查自己的代码。错误类型错误示例正确写法/解释核心原因SELECT列与GROUP BY不匹配SELECT department, employee, AVG(salary) FROM emp GROUP BY departmentSELECT department, employee, AVG(salary) FROM emp GROUP BY department, employee或 使用聚合函数包裹employee违反了SQL标准。在ONLY_FULL_GROUP_BY模式下会报错。在WHERE中使用聚合函数SELECT department, AVG(salary) FROM emp WHERE AVG(salary) 5000 GROUP BY departmentSELECT department, AVG(salary) FROM emp GROUP BY department HAVING AVG(salary) 5000WHERE在分组前执行此时聚合值还未计算。过滤聚合结果必须用HAVING。NULL值处理不当SELECT * FROM table WHERE column ! ‘value‘(如果column有NULLNULL行不会出现)SELECT * FROM table WHERE column ! ‘value‘ OR column IS NULL或SELECT * FROM table WHERE ISNULL(column, ‘‘) ! ‘value‘任何与NULL的比较操作, !, , 结果都是UNKNOWN在WHERE中会被当作FALSE过滤掉。笛卡尔积灾难SELECT * FROM table1, table2忘记写关联条件SELECT * FROM table1 JOIN table2 ON table1.id table2.t1_id多表查询时未指定关联条件导致结果集是两表行数的乘积数据量爆炸。混淆LEFT JOIN与INNER JOIN想要所有客户及其订单但用了INNER JOIN导致没有订单的客户丢失。明确需求如果需要保留所有客户即使没订单必须用LEFT JOIN客户表在左。对连接类型的语义理解不清。INNER JOIN取交集LEFT JOIN保左表全量。IN与EXISTS性能误区盲目认为EXISTS一定比IN快。当子查询结果集很小外表很大时IN可能更快。当子查询结果集很大外表较小时EXISTS通常更优。需要结合EXPLAIN分析。对数据库优化器的工作原理不了解。现代数据库优化器已经很智能很多时候会自动转换。但在涉及NULL时NOT EXISTS语义更安全。窗口函数使用顺序错误SELECT *, ROW_NUMBER() OVER() as rn FROM table WHERE rn 1窗口函数在WHERE后执行不能直接在WHERE中引用别名。需嵌套子查询SELECT * FROM (SELECT *, ROW_NUMBER() OVER() as rn FROM table) t WHERE rn 1不了解SQL语句的执行顺序。逻辑顺序是FROM WHERE GROUP BY HAVING SELECT (包括窗口函数计算) ORDER BY。最后我的个人体会是SQL学习是一个“先死后活”的过程。“死”是指初期要严格遵循语法多做练习把经典40题这样的标杆吃透形成肌肉记忆。“活”是指在理解原理和思维模式后能灵活应对各种复杂多变的业务查询需求。刷题时不要满足于一种解法多想想“还有没有别的写法”“哪种写法性能更好”。当你拿到一个新需求能迅速在脑海中勾勒出查询的逻辑图并转化为高效的SQL语句时你就真正掌握了这门数据库沟通的语言。这套经典40题常刷常新每隔一段时间回头看看总能有新的收获。