关注公众号「船长Talk」获取更多SQL实战技巧、数据分析干货每天分享职场数据技能 · 附完整代码注释 · 学完即用 写在前面SQL 面试有多重要数据分析、后端开发、数据工程……几乎所有跟数据打交道的岗位SQL 都是必考项。但很多人备考的方式不对——死背题目不理解原理换一个表名就不会写了。本文整理了 SQL 面试最高频的 50 类题型每题都有完整代码 详细注释 解题思路覆盖基础查询 / 条件过滤送分题必拿满分分组聚合 / HAVING 过滤中等频率经常出错多表 JOIN高频重点必须熟练子查询 / 相关子查询难点面试必考窗口函数大厂必考加薪神器经典面试场景题连续登录/环比/排名/漏斗等使用建议建议先自己尝试写再对比答案。SQL 是练出来的不是背出来的。 准备工作示例数据表结构本文所有题目基于以下 4 张经典面试用表-- 公众号船长Talk -- 学生表 CREATE TABLE student ( sid VARCHAR(10) PRIMARY KEY, -- 学生ID sname VARCHAR(20), -- 学生姓名 sage INT, -- 年龄 ssex VARCHAR(5) -- 性别 ); -- 课程表 CREATE TABLE course ( cid VARCHAR(10) PRIMARY KEY, -- 课程ID cname VARCHAR(20), -- 课程名称 tid VARCHAR(10) -- 教师ID外键 ); -- 教师表 CREATE TABLE teacher ( tid VARCHAR(10) PRIMARY KEY, -- 教师ID tname VARCHAR(20) -- 教师姓名 ); -- 成绩表核心表大多数题都用它 CREATE TABLE sc ( sid VARCHAR(10), -- 学生ID cid VARCHAR(10), -- 课程ID score DECIMAL(5,2) -- 成绩0-100 );一、基础查询必拿满分题1查询姓李的老师数量-- 公众号船长Talk -- LIKE 模糊匹配李% 代表以李开头的所有名字 -- COUNT(tname) 统计非 NULL 值的行数 SELECT COUNT(tname) AS teacher_count FROM teacher WHERE tname LIKE 李%;考点LIKE 通配符。%匹配任意多字符_匹配单个字符。题2查询没有学全所有课程的学生-- 公众号船长Talk -- 先统计总课程数再找选课数 总数的学生 -- 用子查询获取总课程数避免硬编码 SELECT s.sid, s.sname FROM student s WHERE s.sid NOT IN ( -- 找出选了全部课程的学生ID SELECT sc.sid FROM sc GROUP BY sc.sid HAVING COUNT(DISTINCT sc.cid) (SELECT COUNT(*) FROM course) );题3查询所有学生的选课数和平均分-- 公众号船长Talk -- LEFT JOIN 保留所有学生即使没有选课记录 -- COALESCE 处理 NULL没选课的学生平均分显示0 -- ROUND 保留两位小数 SELECT s.sid, s.sname, COUNT(sc.cid) AS course_count, -- 选课数 ROUND(COALESCE(AVG(sc.score), 0), 2) AS avg_score -- 平均分 FROM student s LEFT JOIN sc ON s.sid sc.sid GROUP BY s.sid, s.sname ORDER BY avg_score DESC;二、分组聚合 HAVING高频考点题4查询平均成绩大于60分的学生-- 公众号船长Talk -- WHERE 过滤的是行HAVING 过滤的是组 -- 先 GROUP BY 分组再用 HAVING 过滤聚合结果 SELECT sid, ROUND(AVG(score), 2) AS avg_score FROM sc GROUP BY sid HAVING AVG(score) 60 ORDER BY avg_score DESC;❌常见错误把 HAVING 换成 WHERE会报错——WHERE 不能用聚合函数。题5查询至少有两门课不及格的学生-- 公众号船长Talk -- 先筛选不及格记录再按学生分组统计 -- HAVING COUNT(*) 2 过滤出不及格门数 2 的学生 SELECT sc.sid, s.sname, COUNT(*) AS fail_count FROM sc JOIN student s ON sc.sid s.sid WHERE sc.score 60 -- 先过滤不及格的行 GROUP BY sc.sid, s.sname HAVING COUNT(*) 2 -- 再过滤不及格门数 ORDER BY fail_count DESC;题6统计每门课的最高分、最低分、平均分、及格率-- 公众号船长Talk -- 及格率 及格人数 / 总人数 -- 用条件聚合SUM(CASE WHEN score 60 THEN 1 ELSE 0 END) SELECT c.cname AS 课程名, MAX(sc.score) AS 最高分, MIN(sc.score) AS 最低分, ROUND(AVG(sc.score), 2) AS 平均分, COUNT(sc.sid) AS 选课人数, -- 及格率及格人数 / 总人数 * 100 ROUND( SUM(CASE WHEN sc.score 60 THEN 1 ELSE 0 END) / COUNT(sc.sid) * 100, 1) AS 及格率 FROM sc JOIN course c ON sc.cid c.cid GROUP BY sc.cid, c.cname ORDER BY 平均分 DESC;三、多表 JOIN必须熟练题7查询所有课程成绩 张三课程成绩的学生-- 公众号船长Talk -- 相关子查询外层查询每行都会执行一次子查询 -- 关键p.sid sc.sid 是相关条件把子查询绑定到外层同一学生 SELECT DISTINCT sc.sid, s.sname FROM sc JOIN student s ON sc.sid s.sid WHERE sc.score ( -- 相关子查询查找同一学生在 cid2 课程的成绩 SELECT p.score FROM sc AS p WHERE p.sid sc.sid -- 关联条件同一个学生 AND p.cid 02 -- 课程02假设张三教课程02 );题8查询学了张三老师所教课程的学生-- 公众号船长Talk -- 多层子查询从 teacher → course → sc → student -- 逐层推导思路清晰 SELECT DISTINCT s.sid, s.sname FROM student s WHERE s.sid IN ( -- 第3层找选了这门课的学生ID SELECT sc.sid FROM sc WHERE sc.cid IN ( -- 第2层找张三教的课程ID SELECT c.cid FROM course c WHERE c.tid ( -- 第1层找张三的教师ID SELECT t.tid FROM teacher t WHERE t.tname 张三 ) ) );题9查询没有学张三老师课程的学生-- 公众号船长Talk -- 取反NOT IN 在子查询结果之外的学生 -- 注意NOT IN NULL 会导致结果为空确保子查询无NULL SELECT s.sid, s.sname FROM student s WHERE s.sid NOT IN ( SELECT sc.sid FROM sc WHERE sc.cid IN ( SELECT c.cid FROM course c WHERE c.tid ( SELECT t.tid FROM teacher t WHERE t.tname 张三 ) ) );⚠️陷阱提示NOT IN遇到子查询有 NULL 值会返回空结果。生产环境建议用NOT EXISTS替代。题10查询两门及以上不及格课程同时展示该学生的平均分-- 公众号船长Talk -- 先找出满足条件的学生ID再 JOIN 获取详细信息 -- 两次聚合用子查询隔开避免 HAVING 和 WHERE 混乱 SELECT s.sname, t.fail_count, ROUND(t.avg_score, 2) AS avg_score FROM ( -- 子查询统计每个学生的不及格数和平均分 SELECT sid, SUM(CASE WHEN score 60 THEN 1 ELSE 0 END) AS fail_count, AVG(score) AS avg_score FROM sc GROUP BY sid HAVING SUM(CASE WHEN score 60 THEN 1 ELSE 0 END) 2 -- 不及格 2 门 ) t JOIN student s ON t.sid s.sid ORDER BY t.fail_count DESC, t.avg_score DESC;四、窗口函数大厂必考窗口函数语法函数名() OVER (PARTITION BY 分组列 ORDER BY 排序列)特点不折叠行区别于 GROUP BY每行都保留同时能拿到分组内的聚合信息。题11对每门课程按成绩排名并列名次不跳号-- 公众号船长Talk -- RANK()并列跳号1,1,3 DENSE_RANK()并列不跳号1,1,2 -- PARTITION BY cid按课程分组排名 -- ORDER BY score DESC高分在前 SELECT sc.sid, s.sname, c.cname, sc.score, RANK() OVER (PARTITION BY sc.cid ORDER BY sc.score DESC) AS rank_with_gap, -- 并列跳号 DENSE_RANK() OVER (PARTITION BY sc.cid ORDER BY sc.score DESC) AS rank_no_gap, -- 并列不跳号 ROW_NUMBER() OVER (PARTITION BY sc.cid ORDER BY sc.score DESC) AS row_num -- 唯一序号 FROM sc JOIN student s ON sc.sid s.sid JOIN course c ON sc.cid c.cid ORDER BY sc.cid, rank_no_gap;题12查询每门课前3名的学生-- 公众号船长Talk -- 先用窗口函数打排名再在外层用 WHERE 过滤 -- 不能直接在窗口函数所在 SELECT 里加 WHERE要套一层子查询 SELECT cname, sname, score, rn AS 排名 FROM ( SELECT c.cname, s.sname, sc.score, -- DENSE_RANK并列不跳号确保每科都有前3名 DENSE_RANK() OVER (PARTITION BY sc.cid ORDER BY sc.score DESC) AS rn FROM sc JOIN student s ON sc.sid s.sid JOIN course c ON sc.cid c.cid ) ranked WHERE rn 3 -- 在外层过滤前3名 ORDER BY cname, rn;题13计算每个学生各科成绩与该科平均分的差值-- 公众号船长Talk -- AVG() OVER (PARTITION BY cid)按课程分组每行保留不折叠 -- 这是窗口函数 vs GROUP BY 最大区别GROUP BY 折叠行窗口函数不折叠 SELECT s.sname, c.cname, sc.score AS 该生成绩, ROUND(AVG(sc.score) OVER (PARTITION BY sc.cid), 2) AS 科目平均分, ROUND(sc.score - AVG(sc.score) OVER (PARTITION BY sc.cid), 2) AS 与平均分差值 FROM sc JOIN student s ON sc.sid s.sid JOIN course c ON sc.cid c.cid ORDER BY c.cname, 与平均分差值 DESC;题14查询每个学生的成绩排名百分比Top N%-- 公众号船长Talk -- PERCENT_RANK()(排名-1)/(总行数-1)结果在 0~1 之间 -- NTILE(4)把数据均分为4桶可用来划分四分位 SELECT s.sname, c.cname, sc.score, ROUND(PERCENT_RANK() OVER (PARTITION BY sc.cid ORDER BY sc.score DESC) * 100, 1) AS 百分比排名, NTILE(4) OVER (PARTITION BY sc.cid ORDER BY sc.score DESC) AS 四分位 FROM sc JOIN student s ON sc.sid s.sid JOIN course c ON sc.cid c.cid ORDER BY sc.cid, 百分比排名;五、经典场景题面试高分必备题15连续3天登录的用户经典字节/美团高频-- 公众号船长Talk -- 核心思路登录日期 - ROW_NUMBER() 常数 → 说明是连续日期 -- 原理如果日期连续日期减去序号的差值不变 -- 准备用户登录日志表 -- CREATE TABLE login_log (user_id INT, login_date DATE); SELECT DISTINCT user_id FROM ( SELECT user_id, login_date, -- 日期 - 序号 一个连续标识符 -- 连续日期序号每增1日期也增1差值不变 DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp_key FROM ( SELECT DISTINCT user_id, login_date FROM login_log -- 去重同天多次登录 ) dedup ) grouped GROUP BY user_id, grp_key HAVING COUNT(*) 3; -- 同一连续标识符出现3次 连续3天面试技巧连续问题的通用思路 日期 - ROW_NUMBER() 常数。这个公式记住所有连续登录/打卡/出现题都能套。题16统计每月的新增用户数和留存率-- 公众号船长Talk -- 新增用户该月首次出现的用户 -- 次月留存新增用户中下个月还有登录记录的比例 -- 第一步找每个用户的首次登录月份 WITH first_login AS ( SELECT user_id, MIN(DATE_FORMAT(login_date, %Y-%m)) AS first_month -- 首次登录月份 FROM login_log GROUP BY user_id ), -- 第二步找每个用户各月的登录记录去重 monthly_active AS ( SELECT DISTINCT user_id, DATE_FORMAT(login_date, %Y-%m) AS active_month FROM login_log ) SELECT f.first_month, COUNT(DISTINCT f.user_id) AS new_users, -- 新增用户数 -- 留存率次月还活跃的用户 / 新增用户 ROUND( COUNT(DISTINCT m.user_id) / COUNT(DISTINCT f.user_id) * 100, 1 ) AS retention_rate_pct FROM first_login f LEFT JOIN monthly_active m ON f.user_id m.user_id AND m.active_month DATE_FORMAT( DATE_ADD(STR_TO_DATE(CONCAT(f.first_month, -01), %Y-%m-%d), INTERVAL 1 MONTH), %Y-%m) -- 次月 GROUP BY f.first_month ORDER BY f.first_month;题17计算每日环比增长率-- 公众号船长Talk -- 环比(今天 - 昨天) / 昨天 * 100% -- LAG(col, 1)取上一行的值窗口函数 -- 假设有订单表orders(order_date DATE, amount DECIMAL) WITH daily_sales AS ( SELECT order_date, SUM(amount) AS daily_amount FROM orders GROUP BY order_date ) SELECT order_date, daily_amount, LAG(daily_amount, 1) OVER (ORDER BY order_date) AS prev_day_amount, -- 昨天金额 ROUND( (daily_amount - LAG(daily_amount, 1) OVER (ORDER BY order_date)) / LAG(daily_amount, 1) OVER (ORDER BY order_date) * 100 , 2) AS mom_growth_pct -- 环比增长率% FROM daily_sales ORDER BY order_date;题18找出每个类目下销量Top1的商品去重处理-- 公众号船长Talk -- 思路窗口函数排名 外层过滤 -- 用 ROW_NUMBER 避免并列第一如需保留并列用 RANK SELECT category, product_name, total_sales FROM ( SELECT category, product_name, SUM(sales_qty) AS total_sales, ROW_NUMBER() OVER ( PARTITION BY category ORDER BY SUM(sales_qty) DESC ) AS rn FROM product_sales GROUP BY category, product_name ) t WHERE rn 1; -- 每个类目只取第1名题19漏斗分析注册→激活→付费转化率-- 公众号船长Talk -- 漏斗各阶段用户数 相邻阶段转化率 -- 用条件聚合SUM CASE WHEN实现多步骤统计 SELECT COUNT(DISTINCT user_id) AS 注册用户数, COUNT(DISTINCT CASE WHEN is_activated 1 THEN user_id END) AS 激活用户数, COUNT(DISTINCT CASE WHEN first_pay_date IS NOT NULL THEN user_id END) AS 付费用户数, -- 激活率 ROUND( COUNT(DISTINCT CASE WHEN is_activated 1 THEN user_id END) / COUNT(DISTINCT user_id) * 100, 1 ) AS 激活率_pct, -- 付费转化率付费/激活 ROUND( COUNT(DISTINCT CASE WHEN first_pay_date IS NOT NULL THEN user_id END) / NULLIF(COUNT(DISTINCT CASE WHEN is_activated 1 THEN user_id END), 0) * 100, 1 ) AS 付费转化率_pct FROM users;题20行列转置PIVOT-- 公众号船长Talk -- MySQL 没有原生 PIVOT用条件聚合模拟 -- 把课程成绩从多行转成多列 SELECT sc.sid, s.sname, MAX(CASE WHEN sc.cid 01 THEN sc.score END) AS 语文, MAX(CASE WHEN sc.cid 02 THEN sc.score END) AS 数学, MAX(CASE WHEN sc.cid 03 THEN sc.score END) AS 英语, ROUND(AVG(sc.score), 2) AS 平均分 FROM sc JOIN student s ON sc.sid s.sid GROUP BY sc.sid, s.sname ORDER BY 平均分 DESC;六、面试高频知识点速查表知识点函数/语法典型场景排名跳号RANK()成绩排名、销量榜排名不跳DENSE_RANK()前N名筛选唯一序号ROW_NUMBER()去重保留一条取上/下N行LAG() / LEAD()环比、同比计算分组累计SUM() OVER()累计销售额移动平均AVG() OVER(ROWS BETWEEN ...)7日均线连续问题日期 - ROW_NUMBER()连续登录、连续打卡行列转置MAX(CASE WHEN ...)宽表展示条件聚合SUM/COUNT(CASE WHEN)漏斗、多维统计去NULL陷阱COALESCE / NULLIF / IS NOT NULLNULL导致计算错误七、3个最容易犯的错误❌ 错误1WHERE 里用聚合函数-- 错误写法WHERE 不能用 AVG SELECT sid FROM sc WHERE AVG(score) 60; -- 正确写法用 HAVING SELECT sid FROM sc GROUP BY sid HAVING AVG(score) 60;❌ 错误2NOT IN 遇到 NULL 值结果为空-- 危险写法子查询有NULL时返回空 SELECT * FROM a WHERE id NOT IN (SELECT id FROM b); -- 安全写法用 NOT EXISTS 或加 IS NOT NULL SELECT * FROM a WHERE NOT EXISTS ( SELECT 1 FROM b WHERE b.id a.id );❌ 错误3窗口函数不能直接在 WHERE 里过滤-- 错误写法窗口函数不能用在 WHERE SELECT *, RANK() OVER (...) AS rn FROM sc WHERE rn 3; -- 正确写法套子查询 SELECT * FROM ( SELECT *, RANK() OVER (...) AS rn FROM sc ) t WHERE rn 3; 总结SQL 面试说难不难核心就这几个能力能写清楚 JOIN知道 LEFT/INNER/FULL 的区别不怕多表能用子查询拆分问题复杂问题分步写先在脑子里拆解会窗口函数RANK、LAG、SUM OVER这三个够用90%场景知道NULL的坑NOT IN NULL 空结果记住这个血泪教训条件聚合会用CASE WHEN GROUP BY 能解决大多数统计题不要死背理解原理多写多练。如果你觉得本文有用点个赞 收藏下次面试前翻出来再过一遍。 更多SQL实战 数据分析干货关注公众号「船长Talk」数据分析 · 职场真相 · 投资洞察 | 每天一篇附完整代码注释