1. 项目概述为什么这40道题能成为“经典”如果你正准备面试数据分析师、后端开发或者任何需要和数据库打交道的岗位那么“SQL笔试经典40题”这个名字你一定不陌生。它就像程序员界的“五年高考三年模拟”是无数求职者踏入职场前必须翻越的一座小山。我第一次接触这套题还是在准备我的第一次跳槽面试当时花了一周时间吭哧吭哧地刷从最初的磕磕绊绊到后来的行云流水这个过程让我对SQL的理解从“会写查询”提升到了“理解数据关系与业务逻辑”的层面。这套题之所以经典绝非偶然。它不像网上随手搜到的那些零散练习题而是经过精心设计覆盖了SQL查询中90%以上的核心知识点和面试高频考点。从最基础的SELECT、WHERE、GROUP BY到进阶的表连接JOIN、子查询、窗口函数再到考察逻辑思维的综合应用题它构建了一个完整的、由浅入深的学习路径。更重要的是这些题目大多基于一个模拟的“学生-课程-成绩”或“员工-部门-薪水”业务场景非常贴近实际工作中的数据分析需求。搞定这40题你不仅能应付笔试更能建立起一套解决实际数据查询问题的思维框架。接下来我将结合自己多次面试和带新人的经验为你彻底拆解这套经典题库不仅告诉你答案怎么写更会深入剖析每类题目背后的考察意图、常见陷阱以及最优解法的思考过程。2. 题库核心知识点体系拆解在开始逐题攻克之前我们有必要像搭积木一样先看清构成这40道题的所有“基础零件”。盲目刷题效率低下只有体系化地掌握背后的知识点才能做到举一反三。2.1 基础查询与过滤一切的开端这是SQL的基石几乎所有题目都从这里开始。经典40题的前几道通常会从这里入手但千万别小看里面藏着考察你严谨性的细节。SELECT 与 DISTINCTSELECT不只是选择列更要清楚何时使用DISTINCT去重。一个常见的坑是统计“有成绩的学生人数”和“选了课的学生人数”可能不同因为一个学生可能有多条成绩记录这时是否需要DISTINCT就需要结合业务语义判断。WHERE 条件过滤熟练使用比较运算符, , , , , 、逻辑运算符AND, OR, NOT和BETWEEN...AND...、IN、LIKE配合%和_是关键。这里常考NULL值的处理记住NULL与任何值包括它自己比较的结果都是UNKNOWN必须用IS NULL或IS NOT NULL来判断。ORDER BY 排序指定多列排序时的优先级以及DESC降序的使用。在分页查询LIMIT ... OFFSET ...或ROW_NUMBER()中排序是前提。注意很多新手会写出WHERE column NULL这样的错误语句这在逻辑上是永远不成立的。务必使用IS NULL。2.2 聚合函数与分组从明细到统计当问题中出现“每个”、“各类”、“平均”、“总计”等字眼时你就要立刻想到GROUP BY。聚合函数COUNT(),SUM(),AVG(),MAX(),MIN()是五大常用函数。特别要注意COUNT(*)统计所有行数COUNT(column)统计该列非NULL值的行数。GROUP BY这是核心。SELECT后面非聚合的列必须出现在GROUP BY子句中。经典题目里大量考察按部门统计薪资、按课程统计平均分、按月份统计订单量等。HAVING 子句这是分组后的过滤条件与WHERE的区别是执行时机不同。WHERE在分组前过滤行HAVING在分组后过滤组。例如“找出平均成绩大于80分的课程”就需要先按课程分组计算平均分再用HAVING AVG(score) 80过滤。2.3 多表连接关系的艺术现实中的数据很少只存在于一张表。JOIN是SQL中最体现功力的部分之一。40题中超过一半的题目涉及多表连接。INNER JOIN最常用返回两个表连接字段匹配的行。解题时必须明确连接条件ON是什么通常是外键关联主键。LEFT/RIGHT JOIN以左/右表为基准返回所有行即使另一表中没有匹配。常用于“查询所有学生及其选课信息没选课的也要显示”这类场景。要深刻理解NULL在结果集中出现的位置和含义。FULL JOIN返回两个表的所有行不匹配处用NULL填充。在某些数据库如MySQL中可能需要用UNION来模拟。自连接同一张表和自己连接。常用于解决“查找比自身前一名薪水高的员工”或“查找同一部门内薪水相同的员工”这类问题。关键是要通过别名如a,b将一张表虚拟成两张。2.4 子查询嵌套的逻辑子查询即一个查询嵌套在另一个查询内部。它提供了强大的灵活性但也可能影响性能。标量子查询返回单个值的子查询可以放在SELECT、WHERE、HAVING中。例如WHERE salary (SELECT AVG(salary) FROM employees)。列子查询返回一列数据的子查询常与IN、ANY、ALL操作符联用。例如WHERE department_id IN (SELECT id FROM departments WHERE location 北京)。行子查询返回一行数据的子查询较少用。表子查询返回一个虚拟表的子查询必须要有别名可以当作临时表参与JOIN。例如FROM (SELECT ...) AS t。相关子查询子查询的执行依赖于外层查询的值通常性能较差但能解决一些复杂逻辑。例如查找每个部门中薪水最高的员工。2.5 窗口函数现代SQL的利器这是进阶部分也是近年来面试的热点。它能在不减少原表行数的情况下进行聚合、排序、排名等计算。核心语法窗口函数 OVER (PARTITION BY 列 ORDER BY 列)。排名函数ROW_NUMBER()连续排名、RANK()并列跳号、DENSE_RANK()并列不跳号。经典题“求每个部门薪水前三的员工”就必须用它。聚合窗口函数SUM() OVER (PARTITION BY ...)、AVG() OVER (PARTITION BY ...)等。可以计算累计和、移动平均等。前后函数LAG()和LEAD()用于访问当前行之前或之后的行数据常用于计算环比、同比增长。2.6 集合运算与条件表达式UNION / UNION ALL合并多个查询的结果集。UNION会去重UNION ALL不会后者性能更好。CASE WHENSQL中的“if-else”语句功能极其强大。可用于数据分类、条件赋值、行列转换等。例如将成绩分数段划分为‘A’ ‘B’ ‘C’等级。3. 经典题型深度剖析与实战解答掌握了知识体系我们进入实战。我将挑选最具代表性的几类题型用模拟数据和详细步骤进行拆解。假设我们有一个简单的三表结构这也是经典40题最常见的模型students(学生表):s_id(学号),s_name(姓名)courses(课程表):c_id(课程号),c_name(课程名),t_id(教师号)scores(成绩表):s_id,c_id,score(成绩)teachers(教师表):t_id,t_name(教师名)3.1 题型一基础聚合与分组查询例题查询每门课程的平均成绩并按平均成绩降序排列。SELECT c.c_id, c.c_name, AVG(s.score) AS avg_score FROM scores s JOIN courses c ON s.c_id c.c_id GROUP BY c.c_id, c.c_name ORDER BY avg_score DESC;深度解析表连接成绩在scores表课程名在courses表所以必须先通过c_id进行JOIN。分组键选择我们按课程分组所以GROUP BY后面是c.c_id。为什么还要加上c.c_name因为在SELECT列表中出现了非聚合列c.c_name根据SQL标准它必须包含在GROUP BY子句中或者本身是一个聚合函数。虽然在某些数据库的宽松模式下可能允许省略但为了代码的严谨性和可移植性强烈建议将所有SELECT中的非聚合列都放入GROUP BY。聚合函数使用AVG()计算平均分。排序使用ORDER BY avg_score DESC实现降序。注意可以使用ORDER BY 3 DESC按第三列排序但不推荐因为列顺序改变会导致错误降低代码可读性。常见变体与陷阱查询每门课程的最高分/最低分将AVG()替换为MAX()或MIN()。查询平均成绩大于80分的课程在GROUP BY后使用HAVING AVG(s.score) 80。这里不能用WHERE因为WHERE是对原始行过滤而“平均成绩”是分组聚合后的结果。统计每门课程的选修人数使用COUNT(s.s_id)。注意如果学生可能缺考成绩为NULLCOUNT(score)和COUNT(*)的结果可能不同需要根据业务定义明确。3.2 题型二复杂多表连接与逻辑判断例题查询所有学生的选课情况包括学生姓名、课程名即使学生没有选课也要显示。SELECT st.s_name, c.c_name FROM students st LEFT JOIN scores sc ON st.s_id sc.s_id LEFT JOIN courses c ON sc.c_id c.c_id;深度解析连接类型选择题目要求“即使学生没有选课也要显示”这明确指向了LEFT JOIN。以students表为左表确保所有学生记录都被保留。连接顺序与逻辑首先学生表LEFT JOIN成绩表得到每个学生及其成绩记录没选课的学生其成绩相关字段为NULL。然后这个中间结果再LEFT JOIN课程表将课程号转换为课程名。对于没选课的学生第二次连接的课程信息自然也是NULL。结果解读查询结果中没选课的学生其c_name列会显示为NULL。常见变体与陷阱查询没有选课的学生在上面的LEFT JOIN基础上添加WHERE sc.s_id IS NULL。原理是LEFT JOIN后没选课的学生在scores表的对应字段全为NULL。查询选了所有课程的学生这是一个经典难题。思路通常是学生选课的数量 课程总数量。可以使用双重否定或GROUP BYHAVING COUNT来实现。-- 方法不存在一门课程是这个学生没选的 SELECT s_name FROM students st WHERE NOT EXISTS ( SELECT 1 FROM courses c WHERE NOT EXISTS ( SELECT 1 FROM scores sc WHERE sc.s_id st.s_id AND sc.c_id c.c_id ) );连接条件写错这是最高发的错误。务必检查ON后面的条件是否准确关联了相关键如s.s_id sc.s_id避免产生笛卡尔积导致结果爆炸。3.3 题型三子查询的综合应用例题查询成绩高于该课程平均成绩的学生成绩记录。SELECT sc1.s_id, sc1.c_id, sc1.score FROM scores sc1 WHERE sc1.score ( SELECT AVG(sc2.score) FROM scores sc2 WHERE sc2.c_id sc1.c_id -- 这是相关子查询为每门课程计算平均分 );深度解析理解需求比较的对象是“该课程”的平均分而不是全局平均分。因此对于外层查询的每一条成绩记录都需要计算其对应课程的平均分。使用相关子查询子查询(SELECT AVG(sc2.score) FROM scores sc2 WHERE sc2.c_id sc1.c_id)中引用了外层查询的sc1.c_id这使得子查询会为外层每一行记录执行一次计算出特定课程的平均分。性能考虑相关子查询在数据量大时可能较慢。另一种优化思路是使用窗口函数或先计算出每门课平均分作为临时表再连接这在题型四中会看到。常见变体与陷阱在SELECT中使用标量子查询例如查询每个学生及其平均成绩与全班平均成绩的差值。SELECT s_id, AVG(score) AS personal_avg, AVG(score) - (SELECT AVG(score) FROM scores) AS diff_from_overall FROM scores GROUP BY s_id;子查询返回多行时误用比较符如果子查询可能返回多个值就不能用,,而要用IN,ANY,ALL。例如“查询比部门内某个人工资高的员工”用 ANY(...)“查询比部门内所有人工资都高的员工”用 ALL(...)。3.4 题型四窗口函数解决排名与累计问题例题查询每门课程成绩的前两名允许并列。SELECT c_name, s_name, score, rank_in_course FROM ( SELECT c.c_name, st.s_name, sc.score, DENSE_RANK() OVER (PARTITION BY sc.c_id ORDER BY sc.score DESC) AS rank_in_course FROM scores sc JOIN students st ON sc.s_id st.s_id JOIN courses c ON sc.c_id c.c_id ) AS ranked_scores WHERE rank_in_course 2;深度解析窗口函数构造核心是DENSE_RANK() OVER (PARTITION BY sc.c_id ORDER BY sc.score DESC)。PARTITION BY sc.c_id按课程分区即在每个课程内部独立进行排名计算。ORDER BY sc.score DESC按成绩降序排列成绩最高的排第1。DENSE_RANK()排名函数。如果使用RANK()遇到并列成绩会跳号如 1,2,2,4而DENSE_RANK()是密集排名1,2,2,3。题目要求“允许并列”且通常理解“前两名”是指排名为1和2的学生使用DENSE_RANK()更符合直觉。如果要求“严格前两名并列则都取”则用RANK()并过滤rank 2可能包含排名第三如果并列第二存在。子查询包装窗口函数DENSE_RANK()产生的排名列不能直接在WHERE子句中使用因为SQL执行顺序中WHERE在SELECT之前。所以需要将整个查询作为子查询在外层进行过滤。表连接为了显示学生名和课程名需要关联students和courses表。常见变体与陷阱求累计和/移动平均使用SUM(score) OVER (PARTITION BY s_id ORDER BY exam_date)可以计算某个学生历次考试的成绩累计和。ORDER BY子句在这里定义了窗口框架的范围。ROW_NUMBER()vsRANK()vsDENSE_RANK()务必根据业务需求选择。ROW_NUMBER()总会生成唯一的连续序号即使值相同RANK()和DENSE_RANK()在值相同时会给相同的排名区别在于后续序号是否连续。性能窗口函数通常比使用自连接或相关子查询来实现相同排名逻辑要高效得多可读性也更强。4. 高频难题与进阶思路精讲经典40题中总有几个“拦路虎”它们综合运用多个知识点考察解题者的逻辑思维和SQL熟练度。4.1 连续性问题例题查询至少连续三天登录的用户。假设有表user_loginuser_id,login_date。-- 思路利用日期差和窗口函数将连续登录的日期分到同一个组 SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS consecutive_days FROM ( SELECT user_id, login_date, -- 核心技巧用登录日期减去一个由ROW_NUMBER生成的序列号连续日期的这个差值会相同 DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS date_group FROM user_login GROUP BY user_id, login_date -- 先去重同一天多次登录算一次 ) AS t GROUP BY user_id, date_group HAVING COUNT(*) 3; -- 连续天数大于等于3思路拆解这个解法非常巧妙。对于每个用户按登录日期排序并赋予行号1,2,3...。如果登录是连续的那么login_date - row_number会得到一个固定的日期。例如连续登录123号计算出的date_group都是0号。非连续登录则会导致date_group值变化。最后按user_id和date_group分组统计天数即可。4.2 行列转换问题例题将学生每门课程的成绩由多行记录转换为一行姓名课程1成绩课程2成绩...。这就是典型的“行转列”PIVOT。在支持PIVOT语法的数据库如SQL Server中可以直接使用。在MySQL中通常使用CASE WHEN配合聚合函数实现。SELECT s.s_name, MAX(CASE WHEN c.c_name 数学 THEN sc.score ELSE NULL END) AS 数学, MAX(CASE WHEN c.c_name 语文 THEN sc.score ELSE NULL END) AS 语文, MAX(CASE WHEN c.c_name 英语 THEN sc.score ELSE NULL END) AS 英语 FROM students s LEFT JOIN scores sc ON s.s_id sc.s_id LEFT JOIN courses c ON sc.c_id c.c_id GROUP BY s.s_id, s.s_name;思路拆解通过CASE WHEN为每个课程创建一列当课程名匹配时返回成绩否则返回NULL。然后使用MAX或SUM聚合函数因为每个学生每门课最多一条成绩MAX和SUM效果一样将多行合并为一行。GROUP BY学生信息完成聚合。4.3 最大/最小值对应整行数据问题例题查询每门课程成绩最高的学生信息及成绩。这是一个常见误区很多人先分组找到最高分再去关联但如果有并列第一这样关联可能会丢失数据。更稳健的方法是使用窗口函数。-- 方法1使用窗口函数推荐 SELECT c_name, s_name, score FROM ( SELECT c.c_name, st.s_name, sc.score, RANK() OVER (PARTITION BY sc.c_id ORDER BY sc.score DESC) AS rk FROM scores sc JOIN students st ON sc.s_id st.s_id JOIN courses c ON sc.c_id c.c_id ) AS t WHERE rk 1; -- 方法2使用子查询 IN SELECT c.c_name, st.s_name, sc.score FROM scores sc JOIN students st ON sc.s_id st.s_id JOIN courses c ON sc.c_id c.c_id WHERE (sc.c_id, sc.score) IN ( SELECT c_id, MAX(score) FROM scores GROUP BY c_id );思路拆解方法1利用RANK()找出每门课排名第一的所有记录完美处理并列。方法2使用子查询先找出每门课的最高分组合(c_id, score)然后外层查询用IN匹配也能处理并列情况且逻辑清晰。5. 实战避坑指南与性能优化浅谈刷题不仅要会写还要写得好、写得快。以下是我在面试和工作中总结出的血泪教训。5.1 思维误区与语法陷阱GROUP BY与SELECT列不匹配这是最常被忽略的语法错误。在严格模式下SELECT中的非聚合列必须出现在GROUP BY中。养成好习惯分组时检查SELECT列表。NULL值的处理聚合函数如COUNT(),SUM(),AVG()会忽略NULL但COUNT(*)不会。在WHERE条件中判断NULL必须用IS NULL。NULL参与任何比较运算结果都是UNKNOWN在WHERE中会被当作FALSE处理。JOIN与WHERE的执行顺序在逻辑上JOIN包括ON条件发生在WHERE之前。这意味着如果你把本应属于连接条件的过滤写在了WHERE里在LEFT JOIN时可能会错误地过滤掉那些你本想保留的NULL行。原则表间关联条件放ON对结果集的最终过滤放WHERE。IN与EXISTS的选择当子查询结果集很大时EXISTS通常比IN性能更好因为EXISTS一旦找到匹配就会停止而IN需要处理整个子查询结果集。反之如果子查询结果集很小IN的列表清晰易读。5.2 写出高效SQL的几点习惯明确需求先想后写动笔前先用自然语言或画图理清数据关系有哪些表如何连接和业务逻辑要计算什么如何分组过滤。思路清晰代码才能清晰。优先使用JOIN慎用子查询大多数现代数据库优化器对JOIN的处理优于相关子查询。尽可能将子查询重写为JOIN尤其是相关子查询。善用窗口函数替代复杂自连接如前所述排名、累计计算等问题窗口函数的性能和可读性远胜于自连接。SELECT *是大忌只选择需要的列。特别是在生产环境或表很宽时这能减少网络传输和内存开销。为连接条件和常用过滤条件建立索引虽然笔试不考但这是解决“慢SQL”的终极法宝。在scores.s_id,scores.c_id这类外键上建立索引能极大提升连接速度。5.3 面试现场应对策略先沟通再动笔拿到题目先和面试官确认表结构字段名、含义、关系、数据样例和期望的输出格式。避免因理解偏差导致全盘皆输。分步实现展示思路对于复杂题目不要试图一步写出完美答案。可以先写出主体框架SELECT ... FROM ... JOIN ... ON ...然后逐步添加WHERE,GROUP BY最后处理HAVING和ORDER BY。一边写一边解释你的思考过程。考虑边界情况主动提出并处理可能的边界情况如成绩为NULL怎么办有并列名次怎么处理这体现了你的严谨性。讨论性能优化即使题目没要求在写出答案后可以简单提一句“如果数据量很大可以考虑在XX字段加索引”或“这个子查询或许可以改用JOIN优化”这绝对是加分项。刷完这40题你收获的绝不仅仅是40个答案。你构建起的是一套面对数据查询问题时如何拆解、分析、选择工具并最终高效解决的系统性思维。这套思维才是你通过SQL笔试乃至应对未来工作中复杂数据挑战的真正底气。剩下的就是在实际项目中不断练习和深化了。