1. 从“刷题”到“破题”为什么40道经典SQL题能帮你打通任督二脉如果你正在准备数据分析、后端开发或者任何与数据库打交道的面试那么“SQL笔试经典40题”这个名字你一定不陌生。它就像程序员界的“五年高考三年模拟”流传甚广几乎成了面试准备的标配。但很多人刷完这40题感觉只是记住了答案换个场景又懵了。问题出在哪因为大多数人只停留在“刷题”层面而没有“破题”。这40道题之所以经典绝非偶然。它们不是随意拼凑的查询语句而是精心设计的“场景切片”几乎覆盖了SQL核心语法的所有应用场景从最基础的增删改查CRUD到多表连接的灵魂操作JOIN再到聚合分析与窗口函数的进阶玩法最后到考验逻辑思维的子查询和条件判断。更重要的是它们模拟了真实业务中最常见的数据处理需求比如排名、分组统计、连续登录、留存分析、部门最高薪等。把这些题吃透你掌握的不仅仅是一堆SELECT语句而是一套解决实际数据问题的思维框架。我自己带团队面试过上百人发现一个规律能清晰、优雅地解答这40题中难题的候选人在实际工作中处理复杂数据需求时思路也往往更缜密写出的SQL性能更好。反之那些靠死记硬背答案的一旦遇到题目变体或真实业务中更混乱的数据就容易露怯。所以今天我们不罗列40题的答案网上随处可见而是带你“破题”拆解每一类题型背后的核心考点、解题思路和那些容易踩进去的“性能坑”。无论你是刚入门的新手还是想巩固内功的老手这篇“心法”都比单纯的题海战术更有价值。2. 基石篇单表查询与WHERE的艺术——远比你想象的复杂很多人觉得单表查询简单不就是SELECT * FROM table WHERE ...吗但在经典40题中单表查询部分恰恰是考察你对数据理解、函数运用和逻辑严谨性的起点。这里埋着新手最容易忽略的“暗坑”。2.1 精准过滤WHERE子句中的“NULL陷阱”几乎所有题目都会涉及WHERE条件过滤。一个经典问题是“查询没有奖金comm为NULL的员工信息。” 新手会直接写SELECT * FROM emp WHERE comm NULL。结果一条数据都查不出来。这就是著名的“NULL陷阱”在SQL中NULL代表未知值它不等于任何值甚至不等于它自己。因此 NULL、! NULL的比较永远返回未知UNKNOWN被当作FALSE处理。正确的写法是使用IS NULL或IS NOT NULL-- 查询奖金为空的员工 SELECT ename, sal, comm FROM emp WHERE comm IS NULL; -- 查询有奖金的员工 SELECT ename, sal, comm FROM emp WHERE comm IS NOT NULL;注意在聚合函数中COUNT(column)会忽略该列的NULL值而COUNT(*)会计算所有行。这也是一个常见考点。2.2 函数运用日期、字符串与数字处理的细节单表查询中大量使用了各类函数这是考察你对SQL内置工具箱的熟悉程度。日期函数是重灾区。比如题目“查询入职时间在1981年第二季度的员工”。你不能直接写WHERE hiredate BETWEEN 1981-04-01 AND 1981-06-30吗可以但这依赖于你对日期格式的隐式转换不够健壮。更规范的写法是使用日期提取函数-- 假设数据库是MySQL SELECT ename, hiredate FROM emp WHERE YEAR(hiredate) 1981 AND QUARTER(hiredate) 2; -- 或者在SQL Server中 SELECT ename, hiredate FROM emp WHERE DATEPART(year, hiredate) 1981 AND DATEPART(quarter, hiredate) 2;这样做的好处是无论hiredate字段的存储格式如何查询逻辑都是清晰的并且可以利用到函数索引如果存在的话。字符串函数常用于模糊查询和字段拼接。例如“查询名字以‘S’开头的员工”要用LIKE S%。这里的关键是理解通配符%代表任意多个字符_代表一个字符。如果名字中本身包含%或_就需要使用转义字符如LIKE %\%% ESCAPE \来查找包含百分号的字段。这是一个容易被忽略的细节。数字与聚合的初步结合如“查询公司平均工资”。新手会写SELECT AVG(sal) FROM emp;但这里有个隐含考点AVG函数是否包含NULL答案是不包含。AVG(sal)只计算sal非NULL的行。如果comm奖金字段很多是NULL想计算平均奖金将NULL视为0则需要使用COALESCE或IFNULL函数SELECT AVG(COALESCE(comm, 0)) FROM emp;。这个细节在后续多表聚合中会放大影响。3. 灵魂篇多表连接JOIN的七种武器与选择逻辑多表连接是SQL的灵魂也是40题中最核心、最易错的部分。很多人知道INNER JOIN、LEFT JOIN但面对“查找每个部门最高薪的员工”或“查找没有员工的部门”这类问题时依然无从下手。关键在于理解每种JOIN的本质和适用场景。3.1 INNER JOIN寻找共同拥有的交集INNER JOIN是最常用的连接返回两个表中连接字段匹配的行。它的逻辑很直观你有我也有我们才能牵手成功。在40题中大部分涉及员工(emp)和部门(dept)信息的题目如“查询员工及其部门名称”用的就是它SELECT e.ename, d.dname FROM emp e INNER JOIN dept d ON e.deptno d.deptno;这里有一个关键细节如果某个员工emp的deptno为NULL或者某个部门dept在员工表中没有对应记录则该行不会出现在结果中。这是INNER JOIN的排他性。3.2 LEFT/RIGHT JOIN保留一方的全部寻找另一方的匹配这是面试中的高频考点尤其是LEFT JOIN。它的逻辑是以左表FROM后的表为基准无论右表是否有匹配左表的所有行都会返回右表匹配不上则用NULL填充。经典题目“查询所有部门及其员工信息包括没有员工的部门。” 这就是LEFT JOIN的典型场景部门表是主表SELECT d.dname, e.ename FROM dept d LEFT JOIN emp e ON d.deptno e.deptno ORDER BY d.dname;此时如果一个部门如OPERATIONS没有员工那么e.ename字段就会显示为NULL。一个高级技巧利用LEFT JOIN和WHERE ... IS NULL来查找“不存在”的关系。例如“查找没有员工的部门”SELECT d.dname FROM dept d LEFT JOIN emp e ON d.deptno e.deptno WHERE e.empno IS NULL; -- 关键连接后员工号为NULL说明没匹配上这个模式非常强大可以替代NOT IN或NOT EXISTS子查询并且在某些数据库优化器下性能更好。3.3 FULL JOIN, CROSS JOIN 与 SELF JOINFULL JOIN返回左右两表的全部行匹配的合并不匹配的各自用NULL补充。在40题中可能用于对比两张表数据的完整情况但实际业务中较少使用MySQL不支持FULL JOIN需用UNION模拟。CROSS JOIN笛卡尔积两表行数相乘。慎用但在生成序列或组合所有可能性时有用40题中可能用于计算排名或生成报告框架。SELF JOIN同一张表与自己连接。这是解决层次关系或对比问题的利器。经典题目“查询每个员工及其经理的名字”。因为经理信息也存储在emp表中mgr字段指向经理的empno所以需要自连接SELECT e.ename AS 员工, m.ename AS 经理 FROM emp e LEFT JOIN emp m ON e.mgr m.empno; -- 用LEFT JOIN因为最高领导没有经理mgr为NULL自连接时给表起不同的别名如e,m是必须的否则字段无法区分。JOIN的选择逻辑总结先问自己两个问题1. 我需要哪张表的全部信息2. 匹配不上的记录是否需要保留答案决定了使用INNER、LEFT还是其他JOIN。4. 核心篇聚合函数与GROUP BY——数据汇总的思维跃迁从这一部分开始SQL从“取数据”进入到“分析数据”的领域。GROUP BY与聚合函数SUM,AVG,COUNT,MAX,MIN的结合是进行数据统计分析的基石。40题中大量题目在此处设置障碍。4.1 GROUP BY的本质切割与聚合理解GROUP BY的关键在于想象一个过程它根据指定的列将原始数据表切割成若干个互不重叠的“小组”。然后聚合函数在每个小组内部独立运算。例如“查询每个部门的平均工资”SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno;数据库引擎会先将emp表按deptno的值分成N组假设有3个部门然后分别在每个部门组内计算sal的平均值。一个致命的常见错误SELECT列表中出现了非聚合列且未在GROUP BY子句中。例如-- 错误示例 SELECT ename, deptno, AVG(sal) FROM emp GROUP BY deptno;ename没有出现在GROUP BY中也没有被聚合。在一个部门组里有多个员工名字数据库不知道该返回哪一个。所有主流数据库都会报错。这是笔试中绝对会考的语法点。4.2 HAVING子句对“分组结果”进行过滤WHERE和HAVING的区别是另一个核心考点。WHERE在分组前过滤原始行HAVING在分组后过滤分组结果。例如“查询平均工资大于2000的部门”SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno HAVING AVG(sal) 2000; -- 对分组后的聚合结果进行过滤你不能写成WHERE AVG(sal) 2000因为WHERE执行时分组还没发生AVG(sal)无意义。性能提示尽可能用WHERE先过滤掉不需要的数据减少参与分组计算的数据量最后再用HAVING对少数分组结果进行过滤。例如先过滤掉工资低于1000的员工再计算部门平均工资SELECT deptno, AVG(sal) AS avg_sal FROM emp WHERE sal 1000 -- 先过滤效率更高 GROUP BY deptno HAVING AVG(sal) 2000;4.3 经典难题解析“查找每个部门工资最高的员工”这是40题中最经典的题目之一它完美结合了分组聚合和自连接/子查询。错误解法是-- 错误这返回的是每个部门的最高工资而不是对应的员工信息 SELECT deptno, ename, MAX(sal) FROM emp GROUP BY deptno;如前所述ename与MAX(sal)不对应。正确解法1使用子查询关联思路先找到每个部门的最高工资作为一个临时结果再用这个结果回原表匹配。SELECT e.deptno, e.ename, e.sal FROM emp e INNER JOIN ( SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY deptno ) t ON e.deptno t.deptno AND e.sal t.max_sal;这个解法清晰易懂但需要注意如果一个部门有多个员工并列最高薪他们会全部被查出来。正确解法2使用窗口函数现代SQL更推荐如果数据库支持窗口函数如MySQL 8.0, PostgreSQL, SQL Server等可以使用ROW_NUMBER()或RANK()这更简洁高效SELECT deptno, ename, sal FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rn FROM emp ) AS ranked WHERE rn 1;PARTITION BY deptno相当于在部门内分组ORDER BY sal DESC按工资降序排ROW_NUMBER()给每个部门内的行从1开始编号。取rn1就是每个部门最高薪。如果考虑并列可以用RANK()。5. 进阶篇子查询、窗口函数与CASE WHEN——编写“智能”SQL掌握了基础连接和聚合你已经能解决80%的问题。剩下的20%难题需要子查询、窗口函数和条件判断这些更高级的“武器”来攻克。5.1 子查询SQL中的嵌套思维子查询即一个查询嵌套在另一个查询内部。根据出现的位置可分为标量子查询返回单个值的子查询常用在SELECT列表或WHERE条件中。如“查询工资高于公司平均工资的员工”SELECT ename, sal FROM emp WHERE sal (SELECT AVG(sal) FROM emp);列子查询返回一列数据的子查询常与IN,ANY,ALL联用。如“查询和‘SCOTT’在同一个部门的员工”SELECT ename, deptno FROM emp WHERE deptno IN (SELECT deptno FROM emp WHERE ename SCOTT) AND ename ! SCOTT;行子查询返回一行数据的子查询较少用。表子查询返回一个虚拟表的子查询必须要有别名常用在FROM子句或JOIN中。前面“部门最高薪”的解法1就用到了。子查询的性能陷阱关联子查询子查询引用了外层查询的列可能导致性能低下因为它对外层查询的每一行都可能执行一次子查询。在可能的情况下尽量将其改写为JOIN。例如上面的“同部门员工”查询用JOIN通常更优SELECT e2.ename, e2.deptno FROM emp e1 JOIN emp e2 ON e1.deptno e2.deptno WHERE e1.ename SCOTT AND e2.ename ! SCOTT;5.2 窗口函数聚合与排名的革命窗口函数是SQL功能的巨大飞跃。它允许你在不减少行数不GROUP BY的情况下对数据的“窗口”进行计算。语法核心是OVER()子句。核心应用1排名问题除了前面提到的部门最高薪还有“部门内工资排名”、“公司内工资排名”等。-- 对所有员工按工资降序排名并列名次相同且不留空位 SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) AS rank_sal, DENSE_RANK() OVER (ORDER BY sal DESC) AS dense_rank_sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS row_num_sal FROM emp;RANK(): 并列会占用名次如1,2,2,4。DENSE_RANK(): 并列不占用名次如1,2,2,3。ROW_NUMBER(): 强制连续编号即使并列也给出不同序号如1,2,3,4。核心应用2移动平均与累计求和“查询每个员工及其前N个同事的平均工资”或“计算每月销售额的累计总和”这类问题用窗口函数轻而易举。-- 计算每个员工按入职日期排序到当前员工为止的累计工资总和 SELECT ename, hiredate, sal, SUM(sal) OVER (ORDER BY hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM emp;ROWS BETWEEN ... AND ...定义了窗口的框架这里是“从第一行到当前行”。5.3 CASE WHENSQL中的条件逻辑CASE WHEN让SQL具备了灵活的流控制能力用于数据分类、条件赋值等。在40题中常用于数据透视或复杂条件判断。经典场景数据分类与统计“将员工按工资等级分类高、中、低并统计人数”。SELECT CASE WHEN sal 3000 THEN 高 WHEN sal 1500 THEN 中 ELSE 低 END AS salary_level, COUNT(*) AS emp_count FROM emp GROUP BY CASE WHEN sal 3000 THEN 高 WHEN sal 1500 THEN 中 ELSE 低 END;注意GROUP BY后面需要重复CASE表达式或者使用列别名取决于数据库支持MySQL允许但某些数据库如Oracle早期版本不允许在GROUP BY中使用别名。结合聚合函数实现复杂统计“统计每个部门工资大于2000和小于等于2000的员工人数各有多少”。这可以用SUM(CASE WHEN ...)实现这是一种“行转列”的简单形式SELECT deptno, SUM(CASE WHEN sal 2000 THEN 1 ELSE 0 END) AS high_sal_count, SUM(CASE WHEN sal 2000 THEN 1 ELSE 0 END) AS low_sal_count FROM emp GROUP BY deptno;这里CASE WHEN为每个符合条件的行返回1否则返回0SUM函数将这些1累加起来就得到了计数。这比分别写两个子查询要高效得多。6. 实战篇组合拳破解复杂业务场景题经典40题的后半部分往往是前面所有知识点的综合应用。我们挑两个最典型的复杂场景题拆解其解题思路。6.1 连续登录/活跃天数问题这是一个非常经典的业务场景题变体很多如“查询连续登录超过7天的用户”。其核心思路是利用窗口函数或自连接为每个用户的登录日期生成一个连续的序列或分组标识。假设有表user_login(user_id, login_date)。 解法通常使用窗口函数ROW_NUMBER()和日期差值法SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp FROM (SELECT DISTINCT user_id, login_date FROM user_login) t -- 先去重同一天多次登录算一次 ) tmp GROUP BY user_id, grp HAVING COUNT(*) 7; -- 连续天数阈值思路解析子查询中ROW_NUMBER()为每个用户按登录日期生成连续编号1,2,3...。用登录日期减去这个编号的天数。如果日期是连续的那么login_date - row_number会得到一个相同的固定日期。这个固定日期grp就成为了连续日期序列的“组标识”。外层按user_id和这个grp分组MIN和MAX得到该连续序列的起止日期COUNT得到连续天数。最后用HAVING过滤出连续天数达标的结果。这个解法巧妙地将连续的日期转换成了一个可分组的值是必须掌握的经典模式。6.2 留存率/漏斗分析问题“计算次日留存率”是数据分析面试的常客。留存率 次日还登录的用户数 / 当日新增用户数。假设有表user_login(user_id, login_date)需要计算某日如‘2023-10-01’的次日留存率。SELECT 2023-10-01 AS calc_date, COUNT(DISTINCT d1.user_id) AS dau, -- 当日活跃用户 COUNT(DISTINCT d2.user_id) AS next_day_retained_users, -- 次日留存用户 CONCAT(ROUND(COUNT(DISTINCT d2.user_id) * 100.0 / NULLIF(COUNT(DISTINCT d1.user_id), 0), 2), %) AS retention_rate FROM (SELECT DISTINCT user_id FROM user_login WHERE login_date 2023-10-01) d1 LEFT JOIN (SELECT DISTINCT user_id FROM user_login WHERE login_date DATE_ADD(2023-10-01, INTERVAL 1 DAY)) d2 ON d1.user_id d2.user_id;思路解析创建两个子查询d1和d2分别获取目标日和第二天的去重用户列表。使用LEFT JOIN以目标日用户为基准关联次日用户。关联上的就是留存用户。计算留存用户数占目标日用户数的比例。这里用了NULLIF函数防止除零错误。 这个查询模式可以扩展到7日留存、30日留存只需调整日期条件即可。理解了这个你就掌握了用户行为分析的一个核心SQL模型。7. 性能篇从“写得出”到“写得好”的优化思维在笔试和面试中能写出正确答案只是第一步。面试官往往更关注你是否有性能意识。针对40题中可能出现的性能问题你需要知道优化方向。7.1 索引让你的查询飞起来SQL优化索引是重中之重。针对40题常见的查询模式等值查询WHERE deptno 10在deptno上建立普通索引B-Tree。范围查询与排序WHERE sal 2000 ORDER BY hiredate考虑建立复合索引(sal, hiredate)。注意顺序等值条件列在前范围查询和排序列在后。JOIN条件ON e.deptno d.deptno确保连接字段deptno在两张表上都建立了索引。聚合分组GROUP BY deptno在分组列上建立索引可以避免昂贵的文件排序Using filesort。重要提醒索引不是越多越好。索引会降低写操作INSERT/UPDATE/DELETE的速度并占用额外空间。需要根据实际查询负载权衡。7.2 执行计划解读找到慢的根源学会看数据库的执行计划EXPLAIN是高级SQL使用者的必备技能。以MySQL为例在查询前加上EXPLAINEXPLAIN SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno d.deptno WHERE e.sal 3000;你需要关注几个关键列type访问类型从好到坏大致是systemconsteq_refrefrangeindexALL。ALL代表全表扫描在数据量大时是性能杀手。key实际使用的索引。如果为NULL说明没用到索引。rows预估需要扫描的行数。这个值越小越好。Extra额外信息。出现Using filesort文件排序或Using temporary使用临时表通常意味着性能瓶颈。对于40题中的复杂查询养成用EXPLAIN分析的习惯思考如何通过调整索引或改写SQL来消除Using filesort和Using temporary。7.3 SQL写法优化小改动大提升用EXISTS替代IN当子查询结果集很大时EXISTS通常比IN性能更好因为EXISTS一旦找到匹配就会停止而IN需要处理整个子查询结果集。-- 使用 IN SELECT * FROM dept WHERE deptno IN (SELECT deptno FROM emp WHERE sal 5000); -- 使用 EXISTS SELECT * FROM dept d WHERE EXISTS (SELECT 1 FROM emp e WHERE e.deptno d.deptno AND e.sal 5000);避免在WHERE子句中对字段进行函数操作这会导致索引失效。例如WHERE YEAR(hiredate) 1981无法使用hiredate上的索引。应改为范围查询WHERE hiredate 1981-01-01 AND hiredate 1982-01-01。只取需要的列避免SELECT *明确列出需要的列减少网络传输和内存开销。合理使用UNION ALLUNION会去重代价高。如果确定结果集没有重复或不需要去重使用UNION ALL。把这40道题刷透意味着你不仅记住了语法更理解了关系型数据库处理数据的核心思想——集合运算。下次面试再遇到SQL问题你不会再慌张地回忆某道题的答案而是会从容地分析“这是一个多表关联过滤问题需要保留主表全部信息所以用LEFT JOIN...这里需要对分组后的结果筛选所以HAVING比WHERE合适...” 这才是“破题”的真正价值。