刷SQLZOO这件事我一年里带着团队干了三次。每次有新同学入职我都会把SQLZOO中文版甩过去让他们先把题过一遍。原因很简单这套题能覆盖从SELECT基础到JOIN、聚合、子查询、窗口函数的完整链路而且浏览器打开就能跑不需要在本地装数据库。今天这篇就把各章节的答案和解题思路整理到一起顺带把我在刷题和带新人过程中遇到的高频坑也写明白。无论你是刚学SQL三天还是已经在业务里写过几百条取数SQL只要还没系统刷过这套题我都建议按下面的章节顺序来一遍。1. 先读懂SQLZOO这套题到底在训练什么能力1.1 中文版怎么用和直接搜答案的区别在哪SQLZOO本身是英文网站中文版把题目和提示翻译成了中文但表结构、数据、判题逻辑和英文版完全一致。我推荐中文版的理由是它能让你把精力集中在SQL本身而不是被“Show the name and population...”这种英文题目绊住。但要注意翻译后的题目偶尔会有术语偏差真遇到对不上的时候切到英文原版对着看就行。关于“直接搜答案”这件事我的态度很明确答案可以看但不能只看答案。SQLZOO每道题都要求你在右侧输入框里写SQL并执行系统直接比对返回结果。这种即时反馈意味着你不需要装SQL Server也不需要配MySQL浏览器开一个标签页就能连续练几个小时。我带新人时一般会定个规矩每题必须自己执行通过才算过不许把答案抄一遍就交差。抄答案十分钟后就会忘自己敲一遍的东西才能真正留在手上。1.2 全章节知识点地图一张表看清考点分布SQLZOO中文版目前主线章节大致如下我按练习顺序整理了一张表方便你对照自己的进度章节核心表主要考点SELECT basicsworldWHERE、IN、BETWEENSELECT from WORLDworldLIKE、算术运算、ROUND、字符串函数SELECT from Nobelnobel多条件、IN、LIKE、排序、转义SELECT within SELECTworld标量子查询、相关子查询、ALL/ANYSUM and COUNTworldGROUP BY、HAVING、聚合函数JOINgame / goal / eteamINNER JOIN、多表关联、CASE统计More JOINmovie / actor / casting演员-电影多对多、ord角色Using Nullteacher / deptLEFT/RIGHT JOIN、COALESCE、CASESelf joinstops / route自连接、换乘查询Window functionsloan 等RANK、OVER、PARTITION BY看到这张表你应该能感受到SQLZOO不是让你背语法而是把SQL最常接触的几类查询场景串了一遍。前五章是地基中间三章是JOIN的重头戏后面两章是面试经常考的复杂查询。业务里的取数需求再怎么变拆开看基本就是这些知识点的组合。2. 基础章答案速查SELECT basics / WORLD / Nobel2.1 SELECT basics三次课打牢查询的地基题目1查询国家Germany的人口。SELECT population FROM world WHERE name Germany;这条考的是最简单的SELECT列、FROM表、WHERE过滤。注意题目只要求返回population就不要顺手把name也带出来SQLZOO判题是按结果集合匹配的多出来的列会导致不一致。题目2查询瑞典、挪威、丹麦的人口。SELECT name, population FROM world WHERE name IN (Sweden, Norway, Denmark);考点是IN后面接一个字符串列表。等价写法是name Sweden OR name Norway OR name Denmark。实际开发中如果列表来自程序参数用IN在可读性和维护性上都更好。题目3查询面积在20万到25万平方公里之间的国家名称和面积。SELECT name, area FROM world WHERE area BETWEEN 200000 AND 250000;BETWEEN是闭区间等价于area 200000 AND area 250000。有个小坑某些数据库对BETWEEN的边界处理在索引优化上会有差异但在SQLZOO这种在线环境里基本不用管。2.2 SELECT from WORLD算术、字符串与ROUND的考点这个章节大概有十来道题几乎把SQL基础函数过了一遍。人口过2亿的国家SELECT name FROM world WHERE population 200000000;人口过2亿国家的人均GDPSELECT name, gdp / population AS per_capita_gdp FROM world WHERE population 200000000;这里直接用gdp / population做除法就行。SQLZOO的表里没有人口为0的国家所以不需要处理除零问题。真要部署到生产环境记得考虑除零保护。南美洲各国的人口单位百万SELECT name, population / 1000000 AS population_in_millions FROM world WHERE continent South America;名字包含“United”的国家SELECT name FROM world WHERE name LIKE %United%;%在LIKE里是任意长度通配符_才是单个字符。很多人一开始分不清写LIKE United%会漏掉“United Arab Emirates”这种而%United%是包含关系更符合题意。面积或人口至少一项超过阈值的国家SELECT name, population, area FROM world WHERE area 3000000 OR population 250000000;面积和人口只有一个超过阈值XOR逻辑SELECT name, population, area FROM world WHERE (area 3000000 AND population 250000000) OR (area 3000000 AND population 250000000);SQL里没有XOR关键字所以要把“仅A成立”和“仅B成立”两个分支显式写出来。这道题很容易写成OR那就和上一题没区别了判题必挂。南美洲人口百万和GDP十亿保留2位小数SELECT name, ROUND(population / 1000000, 2) AS pop_millions, ROUND(gdp / 1000000000, 2) AS gdp_billions FROM world WHERE continent South America;ROUND的第二个参数是保留的小数位。如果传负数意思是往小数点左边四舍五入下一题就会用到。万亿GDP国家的人均GDP四舍五入到千位SELECT name, ROUND(gdp / population, -3) AS per_capita_gdp FROM world WHERE gdp 1000000000000;国家名和首都名字长度一样SELECT name, capital FROM world WHERE LENGTH(name) LENGTH(capital);国家名和首都首字母相同但国家名不等于首都名SELECT name, capital FROM world WHERE LEFT(name, 1) LEFT(capital, 1) AND name capital;名字包含全部五个元音字母且没有空格SELECT name FROM world WHERE name LIKE %a% AND name LIKE %e% AND name LIKE %i% AND name LIKE %o% AND name LIKE %u% AND name NOT LIKE % %;这道题容易漏掉NOT LIKE % %导致混入带空格的国家名。SQLZOO判题很严格少了这个条件就一定过不了。2.3 SELECT from Nobel排序、转义和表达式条件Nobel这张表存的是诺贝尔奖数据字段包括yr年份、subject领域、winner获奖者。这章的题开始有“陷阱”了。1950年诺贝尔奖得主SELECT winner FROM nobel WHERE yr 1950;1962年的文学奖得主SELECT winner FROM nobel WHERE yr 1962 AND subject Literature;爱因斯坦获奖的年份和领域SELECT yr, subject FROM nobel WHERE winner Albert Einstein;2000年以后的和平奖得主SELECT winner FROM nobel WHERE yr 2000 AND subject Peace;1980到1989年的文学奖返回所有字段SELECT * FROM nobel WHERE subject Literature AND yr BETWEEN 1980 AND 1989;显示所有美国总统获奖者SELECT * FROM nobel WHERE winner IN (Theodore Roosevelt, Woodrow Wilson, Jimmy Carter, Barack Obama);名字以John开头的获奖者SELECT winner FROM nobel WHERE winner LIKE John %;注意这里用的是John %而不是John%。SQLZOO这道题要求的是英文名里的“名空格姓”格式John %强制要求John后面有一个空格再接姓氏如果写John%像Johnston这种名字也会被匹配出来结果就错了。这是一个很经典的LIKE边界题。物理奖1980年或化学奖1984年的所有记录SELECT * FROM nobel WHERE (subject Physics AND yr 1980) OR (subject Chemistry AND yr 1984);1980年获奖者但不要化学和医学SELECT * FROM nobel WHERE yr 1980 AND subject NOT IN (Chemistry, Medicine);1910年前的医学奖或2004年后的文学奖SELECT * FROM nobel WHERE (subject Medicine AND yr 1910) OR (subject Literature AND yr 2004);包含单引号的姓名查询ONeillSELECT * FROM nobel WHERE winner Eugene ONeill;在标准SQL字符串里单引号用两个单引号转义。不要用反斜杠\在MySQL默认配置下反而会报错或得到错误结果。名字以Sir开头的获奖者按年份降序、姓名升序SELECT winner, yr, subject FROM nobel WHERE winner LIKE Sir % ORDER BY yr DESC, winner;1984年获奖者按物理、化学优先排序SELECT winner, subject FROM nobel WHERE yr 1984 ORDER BY subject IN (Physics, Chemistry), subject, winner;这题很隐蔽。subject IN (Physics, Chemistry)返回的是布尔值在ORDER BY里会被当成0/1参与排序。要这两个类别排在前面按布尔值升序时1在前面0在后面。这个技巧在业务里做自定义优先级排序时非常实用。3. 中阶章核心答案JOIN、聚合与子查询中阶章开始需要真正理解表间关系很多人就是在这里开始卡住。我的经验是不要急着背SQL先把表结构画出来搞清楚主键和外键再动笔写。3.1 JOIN 与 More JOIN多表连接不要只看答案要看连接类型JOIN章节的数据是2012年欧洲杯进球数据三张表game、goal、eteam。先记一下关联关系game表和goal表通过game.id goal.matchid关联goal表和eteam表通过goal.teamid eteam.id关联game表里的team1和team2也指向eteam表德国队进球的matchid和球员SELECT matchid, player FROM goal WHERE teamid GER;这道题只需要goal表还算不上JOIN。查询比赛1012的球场、球队和日期SELECT id, stadium, team1, team2 FROM game WHERE id 1012;德国队进球对应的球员、球队、球场和日期SELECT player, teamid, stadium, mdate FROM game JOIN goal ON game.id goal.matchid WHERE teamid GER;这里是INNER JOIN只有两边都匹配的行才会返回。如果某个进球匹配不到比赛这一行就会被丢掉。球员名字以Mario开头的进球记录SELECT team1, team2, player FROM game JOIN goal ON game.id goal.matchid WHERE player LIKE Mario%;前10分钟进球对应的球员、队伍、教练和进球时间SELECT player, teamid, coach, gtime FROM goal JOIN eteam ON goal.teamid eteam.id WHERE gtime 10;主教练是Fernando Santos的比赛日期和队伍名SELECT mdate, teamname FROM game JOIN eteam ON game.team1 eteam.id WHERE coach Fernando Santos;华沙国家体育场进球的球员SELECT player FROM game JOIN goal ON game.id goal.matchid WHERE stadium National Stadium, Warsaw;每支球队的进球数SELECT teamname, COUNT(*) AS goals FROM eteam JOIN goal ON eteam.id goal.teamid GROUP BY teamname;每个球场的进球数SELECT stadium, COUNT(*) AS goals FROM game JOIN goal ON game.id goal.matchid GROUP BY stadium;波兰参与的比赛和进球数SELECT matchid, mdate, COUNT(*) AS goals FROM game JOIN goal ON game.id goal.matchid WHERE team1 POL OR team2 POL GROUP BY matchid, mdate;德国在每场比赛中的进球数包括没有进球的比赛这类题如果要求把没进球的比赛也保留下来就必须用LEFT JOINSELECT game.id, mdate, COUNT(goal.teamid) AS goals FROM game LEFT JOIN goal ON game.id goal.matchid WHERE team1 GER OR team2 GER GROUP BY game.id, mdate;注意这里用COUNT(goal.teamid)因为COUNT(*)会把LEFT JOIN产生的NULL行也数进去。最终比分统计这是JOIN章节的压轴题要在一行里显示两队比分SELECT mdate, team1, SUM(CASE WHEN teamid team1 THEN 1 ELSE 0 END) AS score1, team2, SUM(CASE WHEN teamid team2 THEN 1 ELSE 0 END) AS score2 FROM game LEFT JOIN goal ON game.id goal.matchid GROUP BY mdate, team1, team2 ORDER BY mdate, matchid;用SUM(CASE WHEN...)做横向统计是标准套路。LEFT JOIN是为了保留0比0的比赛否则没有进球的比赛会直接消失。More JOIN章节的电影数据库更复杂movie、actor、casting三张表casting表里的ord字段表示第几主演。这个章节的核心就一句话从电影找演员和从演员找电影之间的反复切换。1962年的电影SELECT id, title FROM movie WHERE yr 1962;《公民凯恩》的年份SELECT yr FROM movie WHERE title Citizen Kane;所有Star Trek系列电影SELECT id, title, yr FROM movie WHERE title LIKE %Star Trek% ORDER BY yr;Glenn Close的actor idSELECT id FROM actor WHERE name Glenn Close;《卡萨布兰卡》的movie idSELECT id FROM movie WHERE title Casablanca;《卡萨布兰卡》的演员名单SELECT name FROM actor JOIN casting ON casting.actorid actor.id WHERE movieid (SELECT id FROM movie WHERE title Casablanca);《异形》Alien的演员名单SELECT name FROM actor JOIN casting ON casting.actorid actor.id JOIN movie ON casting.movieid movie.id WHERE title Alien;哈里森·福特出演的电影SELECT title FROM movie JOIN casting ON casting.movieid movie.id JOIN actor ON casting.actorid actor.id WHERE name Harrison Ford;哈里森·福特非第一主演的电影SELECT title FROM movie JOIN casting ON casting.movieid movie.id JOIN actor ON casting.actorid actor.id WHERE name Harrison Ford AND ord 1;1962年电影的第一主演SELECT title, name FROM movie JOIN casting ON casting.movieid movie.id JOIN actor ON casting.actorid actor.id WHERE yr 1962 AND ord 1;Rock Hudson每年主演超过2部电影的年份SELECT yr, COUNT(title) AS movies FROM movie JOIN casting ON casting.movieid movie.id JOIN actor ON casting.actorid actor.id WHERE name Rock Hudson GROUP BY yr HAVING COUNT(title) 2;Julie Andrews主演电影里的第一主演SELECT title, name FROM movie JOIN casting ON casting.movieid movie.id JOIN actor ON casting.actorid actor.id WHERE movieid IN ( SELECT movieid FROM casting WHERE actorid (SELECT id FROM actor WHERE name Julie Andrews) ) AND ord 1;这道题用了两层子查询是More JOIN章节的分水岭。先找出Julie参演的电影集合再在这些电影里筛第一主演。出演过15部以上电影第一主角的演员SELECT name FROM actor JOIN casting ON casting.actorid actor.id WHERE ord 1 GROUP BY name HAVING COUNT(*) 15 ORDER BY name;1978年电影主演数量排名SELECT title, COUNT(*) AS actors FROM movie JOIN casting ON casting.movieid movie.id WHERE yr 1978 GROUP BY title ORDER BY COUNT(*) DESC, title;和Art Garfunkel合作过的演员SELECT DISTINCT name FROM actor JOIN casting ON casting.actorid actor.id WHERE movieid IN ( SELECT movieid FROM casting JOIN actor ON casting.actorid actor.id WHERE name Art Garfunkel ) AND name Art Garfunkel;3.2 SUM and COUNTGROUP BY / HAVING 的组合逻辑这一章是聚合查询入门难度不大但必须形成肌肉记忆。重点区分WHERE和HAVING的过滤时机。全球总人口SELECT SUM(population) FROM world;所有不重复的大洲SELECT DISTINCT continent FROM world;非洲GDP总和SELECT SUM(gdp) FROM world WHERE continent Africa;面积至少100万平方公里的国家数量SELECT COUNT(name) FROM world WHERE area 1000000;波罗的海三国人口总和SELECT SUM(population) FROM world WHERE name IN (Estonia, Latvia, Lithuania);每个大洲的国家数量SELECT continent, COUNT(name) FROM world GROUP BY continent;人口至少一千万的每个大洲的国家数量SELECT continent, COUNT(name) FROM world WHERE population 10000000 GROUP BY continent;每个大洲的总人口SELECT continent, SUM(population) FROM world GROUP BY continent;3.3 SELECT within SELECT三种子查询模式的实战拆解子查询是很多人的分水岭SQLZOO这章设计得非常好覆盖了三种常用模式标量子查询、IN子查询、相关子查询。比俄罗斯人口多的国家SELECT name FROM world WHERE population (SELECT population FROM world WHERE name Russia);这是标量子查询子查询只返回一个值然后参与外层比较。比英国人均GDP高的欧洲国家SELECT name FROM world WHERE continent Europe AND gdp / population (SELECT gdp / population FROM world WHERE name United Kingdom);和阿根廷或澳大利亚同属一个大洲的国家SELECT name, continent FROM world WHERE continent IN (SELECT continent FROM world WHERE name IN (Argentina, Australia)) ORDER BY name;这是IN子查询子查询返回一列值外层用IN去匹配。比德国人口更多的欧洲国家SELECT name FROM world WHERE continent Europe AND population (SELECT population FROM world WHERE name Germany);GDP高于所有欧洲国家的国家SELECT name FROM world WHERE gdp (SELECT MAX(gdp) FROM world WHERE continent Europe);这题也可以用gdp ALL (SELECT gdp FROM world WHERE continent Europe)但MAX写法更直白、更好理解。每个大洲中面积最大的国家SELECT continent, name, area FROM world x WHERE area ALL (SELECT area FROM world y WHERE y.continent x.continent);这里就是相关子查询。外层表用别名x内层查询引用x.continent每行外层记录都会执行一次子查询。这种写法是SQLZOO的精髓面试也经常考。每个大洲按字母排序第一个国家SELECT continent, name FROM world x WHERE name ALL (SELECT name FROM world y WHERE y.continent x.continent);掌握ALL的写法之后这类“分组取最值/取第一个”的题就是一个模板。4. 高阶章答案与思路Using Null、Self Join、窗口函数到了高阶章题目不再只是“能写出来”而是“得理解为什么这样写”。4.1 Using NullLEFT / RIGHT JOIN 里的空值陷阱Using Null章节用的是teacher和dept两张表有些老师没有分配系部所以dept是NULL。没有系部的老师SELECT name FROM teacher WHERE dept IS NULL;注意不能用dept NULLNULL不等于任何值包括它自己。所有老师和对应系部INNER JOINSELECT teacher.name, dept.name FROM teacher INNER JOIN dept ON teacher.dept dept.id;INNER JOIN会把dept为NULL的老师丢掉这正好是题干要求的效果。LEFT JOIN保留所有老师SELECT teacher.name, dept.name FROM teacher LEFT JOIN dept ON teacher.dept dept.id;RIGHT JOIN保留所有系部SELECT teacher.name, dept.name FROM teacher RIGHT JOIN dept ON teacher.dept dept.id;COALESCE填充手机号SELECT name, COALESCE(mobile, 07986 444 2266) FROM teacher;COALESCE返回第一个非NULL参数业务里经常用来给展示值兜底。用COALESCE显示系部名没有就显示NoneSELECT teacher.name, COALESCE(dept.name, None) FROM teacher LEFT JOIN dept ON teacher.dept dept.id;统计老师和手机数量SELECT COUNT(name), COUNT(mobile) FROM teacher;COUNT(列名)不会统计NULL值而COUNT(*)会统计所有行。这里题目就是想让你对比有名字和没手机号的人数。每个系部的老师数量SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON teacher.dept dept.id GROUP BY dept.name;这里用RIGHT JOIN保留没有老师的系部COUNT(teacher.name)对空系部返回0。CASE分类SELECT name, CASE WHEN dept IN (1, 2) THEN Sci ELSE Art END FROM teacher;三分类CASESELECT name, CASE WHEN dept IN (1, 2) THEN Sci WHEN dept 3 THEN Art ELSE None END FROM teacher;Using Null这章本身不难但它是理解复杂业务报表的底座。几乎任何真实报表里都会碰到NULL处理。4.2 Self Join公交线路题的标准解法Self join章节用的是爱丁堡公交线路数据stops是站点表route是线路站点关系表。这里最大的挑战是“同一张表既要当起点表用又要当终点表用”。站点总数SELECT COUNT(*) FROM stops;Craiglockhart站点的idSELECT id FROM stops WHERE name Craiglockhart;4路车经过的站点含站名SELECT id, name FROM stops JOIN route ON stops.id route.stop WHERE num 4 AND company LRT;从站149到站53有哪些直达线路SELECT company, num, COUNT(*) FROM route WHERE stop IN (149, 53) GROUP BY company, num HAVING COUNT(*) 2;这题的关键是找同一条线路同时包含这两个站。用GROUP BY加HAVING COUNT(*)2比自连接更简洁。直接自连接找出跨站线路SELECT a.company, a.num, a.stop AS stop_a, b.stop AS stop_b FROM route a JOIN route b ON a.company b.company AND a.num b.num WHERE a.stop 53 AND b.stop 149;加入站点名显示从Craiglockhart到London Road的线路SELECT a.company, a.num, stopa.name, stopb.name FROM route a JOIN route b ON a.company b.company AND a.num b.num JOIN stops stopa ON a.stop stopa.id JOIN stops stopb ON b.stop stopb.id WHERE stopa.name Craiglockhart AND stopb.name London Road;自连接加两次JOIN stops的套路要记清楚一次给起点站取名字一次给终点站取名字。很多人漏了第二个JOIN stops结果只能拿到站点id。能从Craiglockhart直达Tollcross的线路SELECT DISTINCT a.company, a.num FROM route a JOIN route b ON a.company b.company AND a.num b.num JOIN stops stopa ON a.stop stopa.id JOIN stops stopb ON b.stop stopb.id WHERE stopa.name Craiglockhart AND stopb.name Tollcross;从Craiglockhart到Lochend的线路SELECT a.company, a.num FROM route a JOIN route b ON a.company b.company AND a.num b.num JOIN route c ON c.company b.company AND c.num b.num JOIN stops stopa ON a.stop stopa.id JOIN stops stopb ON b.stop stopb.id WHERE stopa.name Craiglockhart AND stopb.name Lochend;最后一题涉及换乘方案逻辑更复杂。核心思路是先把从起点出发的线路找出来再找经过换乘站的另一条线路判断它能否到达终点。这类“两段自连接”其实是在模拟图论里的两跳路径SQLZOO不要求最优写法结果对就行。4.3 窗口函数从函数式思维理解 OVER()窗口函数是近几年SQL面试的高频点。SQLZOO新版的window functions章节比较少见因为大多数练习平台还停留在GROUP BY范畴。传统GROUP BY会把多行压成一行窗口函数则保留所有明细行只是在每一行旁边附加计算结果。一个典型例子按amount排序给贷款编号SELECT custid, amount, RANK() OVER (ORDER BY amount DESC) AS rnk FROM loan;如果要按客户分组排名SELECT custid, amount, RANK() OVER (PARTITION BY custid ORDER BY amount DESC) AS rnk FROM loan;PARTITION BY相当于分组ORDER BY决定组内排序。窗口函数题目里最常考RANK、DENSE_RANK、ROW_NUMBER三者的差异函数行为典型结果RANK()并列名次占用后续名次1, 1, 3DENSE_RANK()并列名次不占用后续名次1, 1, 2ROW_NUMBER()忽略并列强制编号1, 2, 3碰到SQLZOO窗口函数题时先确定窗口范围和排序键再去选具体函数。如果要在业务里用我建议专练SUM(...) OVER (ORDER BY ...)这种累计求和写法做同期累计、移动平均时非常实用。5. 把SQLZOO的答案变成自己的SQL能力写到这里主要章节的答案和思路已经覆盖得差不多了。但我觉得如果只看答案价值其实不够。真正拉开差距的是下一步。5.1 刷题流畅之后你必须补的性能优化课SQLZOO的题库偏重正确性数据量只有几百到几千行索引、执行计划这些瓶颈完全感知不到。等到了真实业务里一张表几千万行同样的SQL写法可能就从毫秒级变成分钟级。几个必须掌握的点EXPLAINMySQL里在SQL前加EXPLAIN可以看到有没有走索引、扫描了多少行。这是排查慢SQL的第一步。避免在WHERE条件的列上做函数运算WHERE YEAR(birthday) 1990会放弃索引应该改成WHERE birthday 1990-01-01 AND birthday 1991-01-01。大表JOIN前先过滤减少参与关联的行数。ORDER BY、GROUP BY尽量走索引。SQLZOO没教这些但如果你能写出所有答案说明你读SQL和理解表关系的能力已经过关这时候补性能优化是最划算的。5.2 从练习题到真实业务的三个转变第一数据来源更脏。练习数据是加工过的真实业务经常有NULL、重复值、类型不一致。我在SQLZOO里做Using Null时觉得轻松但真到了数据仓库里每条NULL都可能意味着上游某个管道漏数。第二字段命名不友好。题目里的表名、字段名都很规范业务库可能叫t_cust_info、usr_nm这种缩写需要你先花时间搞懂数据字典否则连表都找不到。第三需求是模糊的。SQLZOO的题目描述很明确业务需求往往是一句“帮我看一下这个月的留存”你得自己拆解成SQL逻辑。我的习惯是先写注释列出步骤再一步步翻译成SQL能少走一半弯路。5.3 推荐继续深入的方向和资源如果你把SQLZOO所有章节都过了一遍可以按照下面的方向继续LeetCode数据库题库偏面试题目更贴近业务比如连续登录、排名、留存。DataLemur这类真实业务SQL场景平台适合有业务背景的读者。官方文档MySQL、PostgreSQL的窗口函数和公共表表达式CTE部分。实际项目找一个公开数据集自己建表、导数据、写报表SQL。我个人建议是刷完SQLZOO后不要急着背下一套题而是找一份本地数据集把在SQLZOO里学的JOIN、聚合、窗口函数都用一遍。比如拿一个订单表按月统计销售额、算环比、求品类累计占比。这些做完SQL基本就真正上手了。最后分享一个我自己的习惯不要在一个地方连续刷超过两个小时。SQLZOO越到后面越需要脑子清醒地看表结构。卡在某道题上超过二十分钟果断跳过第二天再回来看大概率一眼就能想通。刷题是为了形成条件反射不是和自己较劲。 SQLZOO章节答案详解：从基础查询到窗口函数
刷SQLZOO这件事我一年里带着团队干了三次。每次有新同学入职我都会把SQLZOO中文版甩过去让他们先把题过一遍。原因很简单这套题能覆盖从SELECT基础到JOIN、聚合、子查询、窗口函数的完整链路而且浏览器打开就能跑不需要在本地装数据库。今天这篇就把各章节的答案和解题思路整理到一起顺带把我在刷题和带新人过程中遇到的高频坑也写明白。无论你是刚学SQL三天还是已经在业务里写过几百条取数SQL只要还没系统刷过这套题我都建议按下面的章节顺序来一遍。1. 先读懂SQLZOO这套题到底在训练什么能力1.1 中文版怎么用和直接搜答案的区别在哪SQLZOO本身是英文网站中文版把题目和提示翻译成了中文但表结构、数据、判题逻辑和英文版完全一致。我推荐中文版的理由是它能让你把精力集中在SQL本身而不是被“Show the name and population...”这种英文题目绊住。但要注意翻译后的题目偶尔会有术语偏差真遇到对不上的时候切到英文原版对着看就行。关于“直接搜答案”这件事我的态度很明确答案可以看但不能只看答案。SQLZOO每道题都要求你在右侧输入框里写SQL并执行系统直接比对返回结果。这种即时反馈意味着你不需要装SQL Server也不需要配MySQL浏览器开一个标签页就能连续练几个小时。我带新人时一般会定个规矩每题必须自己执行通过才算过不许把答案抄一遍就交差。抄答案十分钟后就会忘自己敲一遍的东西才能真正留在手上。1.2 全章节知识点地图一张表看清考点分布SQLZOO中文版目前主线章节大致如下我按练习顺序整理了一张表方便你对照自己的进度章节核心表主要考点SELECT basicsworldWHERE、IN、BETWEENSELECT from WORLDworldLIKE、算术运算、ROUND、字符串函数SELECT from Nobelnobel多条件、IN、LIKE、排序、转义SELECT within SELECTworld标量子查询、相关子查询、ALL/ANYSUM and COUNTworldGROUP BY、HAVING、聚合函数JOINgame / goal / eteamINNER JOIN、多表关联、CASE统计More JOINmovie / actor / casting演员-电影多对多、ord角色Using Nullteacher / deptLEFT/RIGHT JOIN、COALESCE、CASESelf joinstops / route自连接、换乘查询Window functionsloan 等RANK、OVER、PARTITION BY看到这张表你应该能感受到SQLZOO不是让你背语法而是把SQL最常接触的几类查询场景串了一遍。前五章是地基中间三章是JOIN的重头戏后面两章是面试经常考的复杂查询。业务里的取数需求再怎么变拆开看基本就是这些知识点的组合。2. 基础章答案速查SELECT basics / WORLD / Nobel2.1 SELECT basics三次课打牢查询的地基题目1查询国家Germany的人口。SELECT population FROM world WHERE name Germany;这条考的是最简单的SELECT列、FROM表、WHERE过滤。注意题目只要求返回population就不要顺手把name也带出来SQLZOO判题是按结果集合匹配的多出来的列会导致不一致。题目2查询瑞典、挪威、丹麦的人口。SELECT name, population FROM world WHERE name IN (Sweden, Norway, Denmark);考点是IN后面接一个字符串列表。等价写法是name Sweden OR name Norway OR name Denmark。实际开发中如果列表来自程序参数用IN在可读性和维护性上都更好。题目3查询面积在20万到25万平方公里之间的国家名称和面积。SELECT name, area FROM world WHERE area BETWEEN 200000 AND 250000;BETWEEN是闭区间等价于area 200000 AND area 250000。有个小坑某些数据库对BETWEEN的边界处理在索引优化上会有差异但在SQLZOO这种在线环境里基本不用管。2.2 SELECT from WORLD算术、字符串与ROUND的考点这个章节大概有十来道题几乎把SQL基础函数过了一遍。人口过2亿的国家SELECT name FROM world WHERE population 200000000;人口过2亿国家的人均GDPSELECT name, gdp / population AS per_capita_gdp FROM world WHERE population 200000000;这里直接用gdp / population做除法就行。SQLZOO的表里没有人口为0的国家所以不需要处理除零问题。真要部署到生产环境记得考虑除零保护。南美洲各国的人口单位百万SELECT name, population / 1000000 AS population_in_millions FROM world WHERE continent South America;名字包含“United”的国家SELECT name FROM world WHERE name LIKE %United%;%在LIKE里是任意长度通配符_才是单个字符。很多人一开始分不清写LIKE United%会漏掉“United Arab Emirates”这种而%United%是包含关系更符合题意。面积或人口至少一项超过阈值的国家SELECT name, population, area FROM world WHERE area 3000000 OR population 250000000;面积和人口只有一个超过阈值XOR逻辑SELECT name, population, area FROM world WHERE (area 3000000 AND population 250000000) OR (area 3000000 AND population 250000000);SQL里没有XOR关键字所以要把“仅A成立”和“仅B成立”两个分支显式写出来。这道题很容易写成OR那就和上一题没区别了判题必挂。南美洲人口百万和GDP十亿保留2位小数SELECT name, ROUND(population / 1000000, 2) AS pop_millions, ROUND(gdp / 1000000000, 2) AS gdp_billions FROM world WHERE continent South America;ROUND的第二个参数是保留的小数位。如果传负数意思是往小数点左边四舍五入下一题就会用到。万亿GDP国家的人均GDP四舍五入到千位SELECT name, ROUND(gdp / population, -3) AS per_capita_gdp FROM world WHERE gdp 1000000000000;国家名和首都名字长度一样SELECT name, capital FROM world WHERE LENGTH(name) LENGTH(capital);国家名和首都首字母相同但国家名不等于首都名SELECT name, capital FROM world WHERE LEFT(name, 1) LEFT(capital, 1) AND name capital;名字包含全部五个元音字母且没有空格SELECT name FROM world WHERE name LIKE %a% AND name LIKE %e% AND name LIKE %i% AND name LIKE %o% AND name LIKE %u% AND name NOT LIKE % %;这道题容易漏掉NOT LIKE % %导致混入带空格的国家名。SQLZOO判题很严格少了这个条件就一定过不了。2.3 SELECT from Nobel排序、转义和表达式条件Nobel这张表存的是诺贝尔奖数据字段包括yr年份、subject领域、winner获奖者。这章的题开始有“陷阱”了。1950年诺贝尔奖得主SELECT winner FROM nobel WHERE yr 1950;1962年的文学奖得主SELECT winner FROM nobel WHERE yr 1962 AND subject Literature;爱因斯坦获奖的年份和领域SELECT yr, subject FROM nobel WHERE winner Albert Einstein;2000年以后的和平奖得主SELECT winner FROM nobel WHERE yr 2000 AND subject Peace;1980到1989年的文学奖返回所有字段SELECT * FROM nobel WHERE subject Literature AND yr BETWEEN 1980 AND 1989;显示所有美国总统获奖者SELECT * FROM nobel WHERE winner IN (Theodore Roosevelt, Woodrow Wilson, Jimmy Carter, Barack Obama);名字以John开头的获奖者SELECT winner FROM nobel WHERE winner LIKE John %;注意这里用的是John %而不是John%。SQLZOO这道题要求的是英文名里的“名空格姓”格式John %强制要求John后面有一个空格再接姓氏如果写John%像Johnston这种名字也会被匹配出来结果就错了。这是一个很经典的LIKE边界题。物理奖1980年或化学奖1984年的所有记录SELECT * FROM nobel WHERE (subject Physics AND yr 1980) OR (subject Chemistry AND yr 1984);1980年获奖者但不要化学和医学SELECT * FROM nobel WHERE yr 1980 AND subject NOT IN (Chemistry, Medicine);1910年前的医学奖或2004年后的文学奖SELECT * FROM nobel WHERE (subject Medicine AND yr 1910) OR (subject Literature AND yr 2004);包含单引号的姓名查询ONeillSELECT * FROM nobel WHERE winner Eugene ONeill;在标准SQL字符串里单引号用两个单引号转义。不要用反斜杠\在MySQL默认配置下反而会报错或得到错误结果。名字以Sir开头的获奖者按年份降序、姓名升序SELECT winner, yr, subject FROM nobel WHERE winner LIKE Sir % ORDER BY yr DESC, winner;1984年获奖者按物理、化学优先排序SELECT winner, subject FROM nobel WHERE yr 1984 ORDER BY subject IN (Physics, Chemistry), subject, winner;这题很隐蔽。subject IN (Physics, Chemistry)返回的是布尔值在ORDER BY里会被当成0/1参与排序。要这两个类别排在前面按布尔值升序时1在前面0在后面。这个技巧在业务里做自定义优先级排序时非常实用。3. 中阶章核心答案JOIN、聚合与子查询中阶章开始需要真正理解表间关系很多人就是在这里开始卡住。我的经验是不要急着背SQL先把表结构画出来搞清楚主键和外键再动笔写。3.1 JOIN 与 More JOIN多表连接不要只看答案要看连接类型JOIN章节的数据是2012年欧洲杯进球数据三张表game、goal、eteam。先记一下关联关系game表和goal表通过game.id goal.matchid关联goal表和eteam表通过goal.teamid eteam.id关联game表里的team1和team2也指向eteam表德国队进球的matchid和球员SELECT matchid, player FROM goal WHERE teamid GER;这道题只需要goal表还算不上JOIN。查询比赛1012的球场、球队和日期SELECT id, stadium, team1, team2 FROM game WHERE id 1012;德国队进球对应的球员、球队、球场和日期SELECT player, teamid, stadium, mdate FROM game JOIN goal ON game.id goal.matchid WHERE teamid GER;这里是INNER JOIN只有两边都匹配的行才会返回。如果某个进球匹配不到比赛这一行就会被丢掉。球员名字以Mario开头的进球记录SELECT team1, team2, player FROM game JOIN goal ON game.id goal.matchid WHERE player LIKE Mario%;前10分钟进球对应的球员、队伍、教练和进球时间SELECT player, teamid, coach, gtime FROM goal JOIN eteam ON goal.teamid eteam.id WHERE gtime 10;主教练是Fernando Santos的比赛日期和队伍名SELECT mdate, teamname FROM game JOIN eteam ON game.team1 eteam.id WHERE coach Fernando Santos;华沙国家体育场进球的球员SELECT player FROM game JOIN goal ON game.id goal.matchid WHERE stadium National Stadium, Warsaw;每支球队的进球数SELECT teamname, COUNT(*) AS goals FROM eteam JOIN goal ON eteam.id goal.teamid GROUP BY teamname;每个球场的进球数SELECT stadium, COUNT(*) AS goals FROM game JOIN goal ON game.id goal.matchid GROUP BY stadium;波兰参与的比赛和进球数SELECT matchid, mdate, COUNT(*) AS goals FROM game JOIN goal ON game.id goal.matchid WHERE team1 POL OR team2 POL GROUP BY matchid, mdate;德国在每场比赛中的进球数包括没有进球的比赛这类题如果要求把没进球的比赛也保留下来就必须用LEFT JOINSELECT game.id, mdate, COUNT(goal.teamid) AS goals FROM game LEFT JOIN goal ON game.id goal.matchid WHERE team1 GER OR team2 GER GROUP BY game.id, mdate;注意这里用COUNT(goal.teamid)因为COUNT(*)会把LEFT JOIN产生的NULL行也数进去。最终比分统计这是JOIN章节的压轴题要在一行里显示两队比分SELECT mdate, team1, SUM(CASE WHEN teamid team1 THEN 1 ELSE 0 END) AS score1, team2, SUM(CASE WHEN teamid team2 THEN 1 ELSE 0 END) AS score2 FROM game LEFT JOIN goal ON game.id goal.matchid GROUP BY mdate, team1, team2 ORDER BY mdate, matchid;用SUM(CASE WHEN...)做横向统计是标准套路。LEFT JOIN是为了保留0比0的比赛否则没有进球的比赛会直接消失。More JOIN章节的电影数据库更复杂movie、actor、casting三张表casting表里的ord字段表示第几主演。这个章节的核心就一句话从电影找演员和从演员找电影之间的反复切换。1962年的电影SELECT id, title FROM movie WHERE yr 1962;《公民凯恩》的年份SELECT yr FROM movie WHERE title Citizen Kane;所有Star Trek系列电影SELECT id, title, yr FROM movie WHERE title LIKE %Star Trek% ORDER BY yr;Glenn Close的actor idSELECT id FROM actor WHERE name Glenn Close;《卡萨布兰卡》的movie idSELECT id FROM movie WHERE title Casablanca;《卡萨布兰卡》的演员名单SELECT name FROM actor JOIN casting ON casting.actorid actor.id WHERE movieid (SELECT id FROM movie WHERE title Casablanca);《异形》Alien的演员名单SELECT name FROM actor JOIN casting ON casting.actorid actor.id JOIN movie ON casting.movieid movie.id WHERE title Alien;哈里森·福特出演的电影SELECT title FROM movie JOIN casting ON casting.movieid movie.id JOIN actor ON casting.actorid actor.id WHERE name Harrison Ford;哈里森·福特非第一主演的电影SELECT title FROM movie JOIN casting ON casting.movieid movie.id JOIN actor ON casting.actorid actor.id WHERE name Harrison Ford AND ord 1;1962年电影的第一主演SELECT title, name FROM movie JOIN casting ON casting.movieid movie.id JOIN actor ON casting.actorid actor.id WHERE yr 1962 AND ord 1;Rock Hudson每年主演超过2部电影的年份SELECT yr, COUNT(title) AS movies FROM movie JOIN casting ON casting.movieid movie.id JOIN actor ON casting.actorid actor.id WHERE name Rock Hudson GROUP BY yr HAVING COUNT(title) 2;Julie Andrews主演电影里的第一主演SELECT title, name FROM movie JOIN casting ON casting.movieid movie.id JOIN actor ON casting.actorid actor.id WHERE movieid IN ( SELECT movieid FROM casting WHERE actorid (SELECT id FROM actor WHERE name Julie Andrews) ) AND ord 1;这道题用了两层子查询是More JOIN章节的分水岭。先找出Julie参演的电影集合再在这些电影里筛第一主演。出演过15部以上电影第一主角的演员SELECT name FROM actor JOIN casting ON casting.actorid actor.id WHERE ord 1 GROUP BY name HAVING COUNT(*) 15 ORDER BY name;1978年电影主演数量排名SELECT title, COUNT(*) AS actors FROM movie JOIN casting ON casting.movieid movie.id WHERE yr 1978 GROUP BY title ORDER BY COUNT(*) DESC, title;和Art Garfunkel合作过的演员SELECT DISTINCT name FROM actor JOIN casting ON casting.actorid actor.id WHERE movieid IN ( SELECT movieid FROM casting JOIN actor ON casting.actorid actor.id WHERE name Art Garfunkel ) AND name Art Garfunkel;3.2 SUM and COUNTGROUP BY / HAVING 的组合逻辑这一章是聚合查询入门难度不大但必须形成肌肉记忆。重点区分WHERE和HAVING的过滤时机。全球总人口SELECT SUM(population) FROM world;所有不重复的大洲SELECT DISTINCT continent FROM world;非洲GDP总和SELECT SUM(gdp) FROM world WHERE continent Africa;面积至少100万平方公里的国家数量SELECT COUNT(name) FROM world WHERE area 1000000;波罗的海三国人口总和SELECT SUM(population) FROM world WHERE name IN (Estonia, Latvia, Lithuania);每个大洲的国家数量SELECT continent, COUNT(name) FROM world GROUP BY continent;人口至少一千万的每个大洲的国家数量SELECT continent, COUNT(name) FROM world WHERE population 10000000 GROUP BY continent;每个大洲的总人口SELECT continent, SUM(population) FROM world GROUP BY continent;3.3 SELECT within SELECT三种子查询模式的实战拆解子查询是很多人的分水岭SQLZOO这章设计得非常好覆盖了三种常用模式标量子查询、IN子查询、相关子查询。比俄罗斯人口多的国家SELECT name FROM world WHERE population (SELECT population FROM world WHERE name Russia);这是标量子查询子查询只返回一个值然后参与外层比较。比英国人均GDP高的欧洲国家SELECT name FROM world WHERE continent Europe AND gdp / population (SELECT gdp / population FROM world WHERE name United Kingdom);和阿根廷或澳大利亚同属一个大洲的国家SELECT name, continent FROM world WHERE continent IN (SELECT continent FROM world WHERE name IN (Argentina, Australia)) ORDER BY name;这是IN子查询子查询返回一列值外层用IN去匹配。比德国人口更多的欧洲国家SELECT name FROM world WHERE continent Europe AND population (SELECT population FROM world WHERE name Germany);GDP高于所有欧洲国家的国家SELECT name FROM world WHERE gdp (SELECT MAX(gdp) FROM world WHERE continent Europe);这题也可以用gdp ALL (SELECT gdp FROM world WHERE continent Europe)但MAX写法更直白、更好理解。每个大洲中面积最大的国家SELECT continent, name, area FROM world x WHERE area ALL (SELECT area FROM world y WHERE y.continent x.continent);这里就是相关子查询。外层表用别名x内层查询引用x.continent每行外层记录都会执行一次子查询。这种写法是SQLZOO的精髓面试也经常考。每个大洲按字母排序第一个国家SELECT continent, name FROM world x WHERE name ALL (SELECT name FROM world y WHERE y.continent x.continent);掌握ALL的写法之后这类“分组取最值/取第一个”的题就是一个模板。4. 高阶章答案与思路Using Null、Self Join、窗口函数到了高阶章题目不再只是“能写出来”而是“得理解为什么这样写”。4.1 Using NullLEFT / RIGHT JOIN 里的空值陷阱Using Null章节用的是teacher和dept两张表有些老师没有分配系部所以dept是NULL。没有系部的老师SELECT name FROM teacher WHERE dept IS NULL;注意不能用dept NULLNULL不等于任何值包括它自己。所有老师和对应系部INNER JOINSELECT teacher.name, dept.name FROM teacher INNER JOIN dept ON teacher.dept dept.id;INNER JOIN会把dept为NULL的老师丢掉这正好是题干要求的效果。LEFT JOIN保留所有老师SELECT teacher.name, dept.name FROM teacher LEFT JOIN dept ON teacher.dept dept.id;RIGHT JOIN保留所有系部SELECT teacher.name, dept.name FROM teacher RIGHT JOIN dept ON teacher.dept dept.id;COALESCE填充手机号SELECT name, COALESCE(mobile, 07986 444 2266) FROM teacher;COALESCE返回第一个非NULL参数业务里经常用来给展示值兜底。用COALESCE显示系部名没有就显示NoneSELECT teacher.name, COALESCE(dept.name, None) FROM teacher LEFT JOIN dept ON teacher.dept dept.id;统计老师和手机数量SELECT COUNT(name), COUNT(mobile) FROM teacher;COUNT(列名)不会统计NULL值而COUNT(*)会统计所有行。这里题目就是想让你对比有名字和没手机号的人数。每个系部的老师数量SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON teacher.dept dept.id GROUP BY dept.name;这里用RIGHT JOIN保留没有老师的系部COUNT(teacher.name)对空系部返回0。CASE分类SELECT name, CASE WHEN dept IN (1, 2) THEN Sci ELSE Art END FROM teacher;三分类CASESELECT name, CASE WHEN dept IN (1, 2) THEN Sci WHEN dept 3 THEN Art ELSE None END FROM teacher;Using Null这章本身不难但它是理解复杂业务报表的底座。几乎任何真实报表里都会碰到NULL处理。4.2 Self Join公交线路题的标准解法Self join章节用的是爱丁堡公交线路数据stops是站点表route是线路站点关系表。这里最大的挑战是“同一张表既要当起点表用又要当终点表用”。站点总数SELECT COUNT(*) FROM stops;Craiglockhart站点的idSELECT id FROM stops WHERE name Craiglockhart;4路车经过的站点含站名SELECT id, name FROM stops JOIN route ON stops.id route.stop WHERE num 4 AND company LRT;从站149到站53有哪些直达线路SELECT company, num, COUNT(*) FROM route WHERE stop IN (149, 53) GROUP BY company, num HAVING COUNT(*) 2;这题的关键是找同一条线路同时包含这两个站。用GROUP BY加HAVING COUNT(*)2比自连接更简洁。直接自连接找出跨站线路SELECT a.company, a.num, a.stop AS stop_a, b.stop AS stop_b FROM route a JOIN route b ON a.company b.company AND a.num b.num WHERE a.stop 53 AND b.stop 149;加入站点名显示从Craiglockhart到London Road的线路SELECT a.company, a.num, stopa.name, stopb.name FROM route a JOIN route b ON a.company b.company AND a.num b.num JOIN stops stopa ON a.stop stopa.id JOIN stops stopb ON b.stop stopb.id WHERE stopa.name Craiglockhart AND stopb.name London Road;自连接加两次JOIN stops的套路要记清楚一次给起点站取名字一次给终点站取名字。很多人漏了第二个JOIN stops结果只能拿到站点id。能从Craiglockhart直达Tollcross的线路SELECT DISTINCT a.company, a.num FROM route a JOIN route b ON a.company b.company AND a.num b.num JOIN stops stopa ON a.stop stopa.id JOIN stops stopb ON b.stop stopb.id WHERE stopa.name Craiglockhart AND stopb.name Tollcross;从Craiglockhart到Lochend的线路SELECT a.company, a.num FROM route a JOIN route b ON a.company b.company AND a.num b.num JOIN route c ON c.company b.company AND c.num b.num JOIN stops stopa ON a.stop stopa.id JOIN stops stopb ON b.stop stopb.id WHERE stopa.name Craiglockhart AND stopb.name Lochend;最后一题涉及换乘方案逻辑更复杂。核心思路是先把从起点出发的线路找出来再找经过换乘站的另一条线路判断它能否到达终点。这类“两段自连接”其实是在模拟图论里的两跳路径SQLZOO不要求最优写法结果对就行。4.3 窗口函数从函数式思维理解 OVER()窗口函数是近几年SQL面试的高频点。SQLZOO新版的window functions章节比较少见因为大多数练习平台还停留在GROUP BY范畴。传统GROUP BY会把多行压成一行窗口函数则保留所有明细行只是在每一行旁边附加计算结果。一个典型例子按amount排序给贷款编号SELECT custid, amount, RANK() OVER (ORDER BY amount DESC) AS rnk FROM loan;如果要按客户分组排名SELECT custid, amount, RANK() OVER (PARTITION BY custid ORDER BY amount DESC) AS rnk FROM loan;PARTITION BY相当于分组ORDER BY决定组内排序。窗口函数题目里最常考RANK、DENSE_RANK、ROW_NUMBER三者的差异函数行为典型结果RANK()并列名次占用后续名次1, 1, 3DENSE_RANK()并列名次不占用后续名次1, 1, 2ROW_NUMBER()忽略并列强制编号1, 2, 3碰到SQLZOO窗口函数题时先确定窗口范围和排序键再去选具体函数。如果要在业务里用我建议专练SUM(...) OVER (ORDER BY ...)这种累计求和写法做同期累计、移动平均时非常实用。5. 把SQLZOO的答案变成自己的SQL能力写到这里主要章节的答案和思路已经覆盖得差不多了。但我觉得如果只看答案价值其实不够。真正拉开差距的是下一步。5.1 刷题流畅之后你必须补的性能优化课SQLZOO的题库偏重正确性数据量只有几百到几千行索引、执行计划这些瓶颈完全感知不到。等到了真实业务里一张表几千万行同样的SQL写法可能就从毫秒级变成分钟级。几个必须掌握的点EXPLAINMySQL里在SQL前加EXPLAIN可以看到有没有走索引、扫描了多少行。这是排查慢SQL的第一步。避免在WHERE条件的列上做函数运算WHERE YEAR(birthday) 1990会放弃索引应该改成WHERE birthday 1990-01-01 AND birthday 1991-01-01。大表JOIN前先过滤减少参与关联的行数。ORDER BY、GROUP BY尽量走索引。SQLZOO没教这些但如果你能写出所有答案说明你读SQL和理解表关系的能力已经过关这时候补性能优化是最划算的。5.2 从练习题到真实业务的三个转变第一数据来源更脏。练习数据是加工过的真实业务经常有NULL、重复值、类型不一致。我在SQLZOO里做Using Null时觉得轻松但真到了数据仓库里每条NULL都可能意味着上游某个管道漏数。第二字段命名不友好。题目里的表名、字段名都很规范业务库可能叫t_cust_info、usr_nm这种缩写需要你先花时间搞懂数据字典否则连表都找不到。第三需求是模糊的。SQLZOO的题目描述很明确业务需求往往是一句“帮我看一下这个月的留存”你得自己拆解成SQL逻辑。我的习惯是先写注释列出步骤再一步步翻译成SQL能少走一半弯路。5.3 推荐继续深入的方向和资源如果你把SQLZOO所有章节都过了一遍可以按照下面的方向继续LeetCode数据库题库偏面试题目更贴近业务比如连续登录、排名、留存。DataLemur这类真实业务SQL场景平台适合有业务背景的读者。官方文档MySQL、PostgreSQL的窗口函数和公共表表达式CTE部分。实际项目找一个公开数据集自己建表、导数据、写报表SQL。我个人建议是刷完SQLZOO后不要急着背下一套题而是找一份本地数据集把在SQLZOO里学的JOIN、聚合、窗口函数都用一遍。比如拿一个订单表按月统计销售额、算环比、求品类累计占比。这些做完SQL基本就真正上手了。最后分享一个我自己的习惯不要在一个地方连续刷超过两个小时。SQLZOO越到后面越需要脑子清醒地看表结构。卡在某道题上超过二十分钟果断跳过第二天再回来看大概率一眼就能想通。刷题是为了形成条件反射不是和自己较劲。