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 JOIN

• FULL 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开始编号。取rn=1就是每个部门最高薪。如果考虑并列，可以用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 WHEN：SQL中的条件逻辑

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，否则返回0，SUM函数将这些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; -- 连续天数阈值

思路解析：

1. 子查询中，ROW_NUMBER()为每个用户按登录日期生成连续编号（1,2,3...）。
2. 用登录日期减去这个编号（的天数）。如果日期是连续的，那么login_date - row_number会得到一个相同的固定日期。这个固定日期grp就成为了连续日期序列的“组标识”。
3. 外层按user_id和这个grp分组，MIN和MAX得到该连续序列的起止日期，COUNT得到连续天数。
4. 最后用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;

思路解析：

1. 创建两个子查询d1和d2，分别获取目标日和第二天的去重用户列表。
2. 使用LEFT JOIN，以目标日用户为基准，关联次日用户。关联上的就是留存用户。
3. 计算留存用户数占目标日用户数的比例。这里用了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为例，在查询前加上EXPLAIN：

EXPLAIN SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE e.sal > 3000;

你需要关注几个关键列：

• type：访问类型，从好到坏大致是：system>const>eq_ref>ref>range>index>ALL。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 ALL：UNION会去重，代价高。如果确定结果集没有重复，或不需要去重，使用UNION ALL。

把这40道题刷透，意味着你不仅记住了语法，更理解了关系型数据库处理数据的核心思想——集合运算。下次面试再遇到SQL问题，你不会再慌张地回忆某道题的答案，而是会从容地分析：“这是一个多表关联过滤问题，需要保留主表全部信息，所以用LEFT JOIN...这里需要对分组后的结果筛选，所以HAVING比WHERE合适...” 这才是“破题”的真正价值。