1. 为什么自学SQL需要一份专属的难题笔记自学SQL的人大多经历过同一个阶段语法书翻完了SELECT、JOIN、GROUP BY看着都认识一上机做题就开始卡壳。卡在哪往往不是这个词我不会写而是我知道怎么写但结果就是不对。这类卡点如果只是当场搜一下、看一眼答案、改改就交过两周遇到同类题还是原地打转。所以我一直建议自学的朋友建一份SQL难题笔记专门记那些看着会、写出来错、错完还不服气的题目。这份笔记和普通的语法速查完全是两回事。语法速查解决的是怎么写难题笔记解决的是为什么我这么写是错的、出题人想考什么、下次怎么一把过。它更像一名合格开发者在真实项目里积累的坑位清单多表连接为什么行数会翻倍、NULL参与比较为什么永远不相等、窗口函数和聚合函数到底谁先算、慢查询为什么建了索引还是全表扫描。这些问题在SQL Server、MySQL、Oracle、Hive里表现细节不同但底层逻辑是通的。这篇文章适合三类人刚学完基础语法想进阶的初学者、写业务SQL但总被同事review打回的初中级开发、以及准备面试被窗口函数和连续登录题难住的人。我会把笔记怎么建、记什么、怎么复盘讲清楚再挑几类高频难题逐题拆开配合可复现的建表和造数脚本你照着敲一遍就能变成自己的笔记。全程围绕难题两个字不谈虚的。提示笔记的价值不在于记了多少条而在于每一条都搞懂了为什么。十条真正吃透的题顶得上一百条抄下来的答案。2. 先搭好练习环境别让你的笔记悬在半空2.1 环境选择与版本差异的现实影响写难题笔记的第一件事不是记笔记是搭一个可以随便折腾的库。我个人的习惯是用本地跑的MySQL或者SQL Server Express版本够用、免费、装起来不折腾。为什么强调版本因为很多难题其实是版本特性差异造成的假难题。举个例子WITH公用表表达式在MySQL 8.0以前根本不支持你拿一份别人写的递归查询脚本跑在5.7上报语法错误这不是你SQL差是环境不对。窗口函数也是一个道理MySQL 8.0才正式支持ROW_NUMBER()、RANK()这些早期版本里同样的需求只能靠变量模拟写法天差地别。所以笔记里我强制要求每道题标注环境三件套数据库类型、大版本号、字符集。这三样标清楚以后翻笔记就不会出现我明明记过这题怎么跑不起来的尴尬。字符集尤其容易被忽略中文排序、字符串长度计算、GROUP BY对大小写的敏感度都跟它有关用utf8mb4和用latin1跑同一道题结果可能不一样。至于SQL Server、Oracle、Hive之间的差异我一般只在笔记里记这家有什么不一样的部分共通的部分不重复记。比如Oracle里空字符串和NULL几乎等价这在MySQL里是不成立的这就是一条值得单开的笔记。同样一道去重取值题在Hive里因为支持collect_set会有更简洁的写法这也是差异点。把差异集中记比每道题都抄一遍通用语法要省力得多。2.2 造数脚本要故意留脏数据练习数据千万别用那种干净得像教科书的数据。真实业务里数据永远是脏的日期字段有空值、金额字段有负数、用户ID有重复、状态字段有拼写不一致的枚举值。你拿干净数据练永远练不出对NULL和边界情况的敏感度。我常用的造数思路是建三张表一张用户表、一张订单表、一张商品表人为制造几类问题。订单表里故意放几条user_id在用户表里查不到的记录用来练LEFT JOIN和INNER JOIN的差异故意放几条金额为NULL的记录用来练聚合函数怎么忽略NULL故意放几条时间戳重复的记录用来练去重和排名。下面是我笔记里保存的一套最小可用脚本MySQL和SQL Server基本通用只有自增写法略有不同。-- 用户表 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), city VARCHAR(50), reg_date DATE ); -- 订单表故意制造脏数据 CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2), status VARCHAR(20), created_at DATETIME ); INSERT INTO users VALUES (1,张三,北京,2023-01-05), (2,李四,上海,2023-02-11), (3,王五,NULL,2023-02-20), (4,赵六,北京,2023-03-01), (5,钱七,广州,2023-03-15); INSERT INTO orders VALUES (101,1, 199.00,paid, 2023-04-01 10:00:00), (102,1, NULL ,paid, 2023-04-01 10:00:00), (103,2, 88.50,refund, 2023-04-02 09:30:00), (104,2, 88.50,paid, 2023-04-02 09:30:00), (105,4, 320.00,paid, 2023-04-03 14:20:00), (106,9, 50.00,paid, 2023-04-04 08:00:00), -- 用户不存在 (107,3, 120.00,NULL, 2023-04-05 19:10:00); -- 状态为空这六行数据看着少但够你练二十道题了。比如统计每个城市的有效订单总额你要处理北京有两个用户、广州没有订单、有一条订单的用户ID查不到、有一条金额是NULL、有一条状态是NULL——每一个都是独立的坑。注意练习用的库最好单独建一个起名带_lab后缀比如sql_lab。别在生产库或者你正在用的业务库上练手写UPDATE和DELETE我见过太多人一个手滑把测试环境的真实数据改了。3. 难题笔记的字段设计一条笔记该记什么3.1 我用的六字段模板笔记记得乱等于没记。我摸索出一套六字段模板每道难题都必须填满填不满说明这题我还没真正搞懂。这六个字段分别是题目意图、我的初始写法、错误现象、正确写法、根因解释、同类变体。题目意图是让你用一句人话复述需求比如求每个城市里下单金额最高的那个用户而不是照抄题目原文。我的初始写法一定要照实记下当时的错误代码哪怕很蠢也留着因为你的思维定式下次还会犯。错误现象写具体是报错、是行数多了、是结果缺了几行、还是数值对不上。正确写法贴可运行的SQL。根因解释是灵魂必须用人话讲清楚为什么会错不能只写用窗口函数就好了。同类变体是防止你只记住这一道题把它的壳换一下还认不认得。为什么坚持记初始写法因为我在帮别人看SQL的时候发现绝大多数人不是不会正确写法而是不知道自己错的那个写法错在哪个语义上。比如WHERE里写聚合函数报错很多人只知道不能这么写但不知道原因是WHERE在GROUP BY之前执行、聚合结果这时候还没算出来。把根因写下来下次遇到HAVING和WHERE的边界你就不会再犹豫。3.2 记录节奏与复盘间隔记笔记的节奏我的建议是当天记隔天复周末串。当天遇到卡壳的题趁热把六字段填完此时记忆最新、动机最强。隔天再做一遍这次不看答案只在自己笔记的题目意图那一栏起步写代码写出来再对照。周末把这一周记的题按知识点归类串一遍你会发现它们往往落在少数几个母题上。归类这一步特别关键。我个人的经验是五六十道难题归完类无非就是连接语义、NULL逻辑、聚合与窗口、日期区间、去重排名、性能这几个大类。一旦看清这个结构你的笔记就从一堆散题变成了一张地图遇到新题先判断它属于哪一类思路一下就收窄了。提示复盘时如果发现某道题隔天还是写错说明它没进你这周的归类清单单独标红下周继续复。别怕重复SQL的手感就是靠重复磨出来的。4. 高频难题逐个拆解每一类都值得单开一节4.1 多表连接后聚合结果莫名翻倍这是自学者最容易踩、也最不容易自己发现的坑。现象是你只是想统计每个用户的订单总金额代码写完一跑金额比预期大好几倍。看代码好像没错users和orders连接然后SUM(amount)按用户分组逻辑很顺。问题出在连接的粒度上。如果users表和另一张表比如用户标签表本来是一对多你先把三张表全连上再聚合订单就会被标签行数放大每一条订单会被复制成标签数量那么多份SUM自然翻倍。根因是连接会改变行数而聚合对行数敏感。正确做法要么先聚合再连接要么用子查询把明细收成一行再往上拼。-- 错误写法先连三表再聚合订单被标签放大 SELECT u.id, SUM(o.amount) FROM users u JOIN orders o ON o.user_id u.id JOIN user_tags t ON t.user_id u.id GROUP BY u.id; -- 正确写法先把订单聚合成一行再连接 SELECT u.id, o.total FROM users u LEFT JOIN ( SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id ) o ON o.user_id u.id;我在笔记里给这条起的标题是连接先于聚合账就对不上。同类变体是把SUM换成COUNT——COUNT(*)同样会被放大而COUNT(DISTINCT o.id)能救回来但性能差且只对去重计数有效。所以看到总数不对、成倍增长第一反应就是去检查连接关系是不是一对多。4.2 NULL 的三值逻辑为什么它不参与比较NULL是SQL里最反直觉的东西。它代表的不是0也不是空字符串而是未知。凡是跟NULL做比较结果都是未知而WHERE只保留结果为真的行。所以WHERE status NULL永远筛不出任何东西必须写WHERE status IS NULL。这条规则单独记一行就够了但它的连锁反应一大堆。第一个连锁是关于聚合。SUM、AVG、COUNT(列名)都会自动忽略NULL但COUNT(*)不会。这意味着一张表十行、某一列有两行是NULLCOUNT(列名)返回8COUNT(*)返回10很多人对不上数就是栽在这里。第二个连锁是NOT IN遇到NULL的诡异行为如果子查询结果里含NULLNOT IN可能一行都返回不了因为x不等于未知依然是未知。这个坑在真实业务里非常隐蔽我笔记里专门给它留了一页。-- 想筛出状态不是 refund 的订单写法要当心 SELECT * FROM orders WHERE status refund; -- NULL 行被漏掉 SELECT * FROM orders WHERE status IS NULL OR status refund; -- 完整 -- 用 COALESCE 兜底让 NULL 有个默认值 SELECT id, COALESCE(amount, 0) AS amount FROM orders;处理NULL的通用心法就三条比较用IS NULL不用聚合前想清楚NULL该不该算进去NOT IN的子查询里提前把NULL过滤掉或者干脆改写成NOT EXISTS。我实测下来NOT EXISTS在处理这种场景时既安全又不容易写错。4.3 窗口函数和聚合函数到底谁先算窗口函数是进阶路上的一道分水岭。它的迷惑之处在于长得像聚合函数但行为完全不同。SUM(amount) OVER (PARTITION BY user_id)不会把行合并而是在每一行上附加一个该用户的总金额行数不变。而SUM(amount)配合GROUP BY user_id会把同用户的行压成一行。理解这一点求每组里排名第一的题就有了统一的解法。-- 每个城市里下单金额最高的那个用户含并列 SELECT city, user_id, total FROM ( SELECT u.city, o.user_id, SUM(o.amount) AS total, RANK() OVER (PARTITION BY u.city ORDER BY SUM(o.amount) DESC) AS rk FROM users u JOIN orders o ON o.user_id u.id GROUP BY u.city, o.user_id ) t WHERE rk 1;这段代码里藏着两个知识点GROUP BY和窗口函数是可以共存在同一次查询里的窗口函数是在GROUP BY聚合之后才计算的RANK和ROW_NUMBER的区别在于并列时RANK会给相同名次然后跳号ROW_NUMBER则强行编号不并列。选哪个取决于需求是否要保留并列这是面试常问的细节。我笔记里把RANK、DENSE_RANK、ROW_NUMBER三兄弟的差异做成了一张表对比着记不容易混。函数并列时表现后续编号典型场景ROW_NUMBER()强行分出先后连续递增取唯一一条、分页RANK()同名次跳号1,1,3排行榜保留并列DENSE_RANK()同名次不跳号1,1,2等级、档位划分4.4 连续区间的经典套路连续登录N天、连续上涨的股价这类题看着难解法其实是一个固定套路用日期 - 行号造出一个分组键连续的日期减去连续的行号会得到同一个值然后按这个值分组计数。我把它叫差值分组法笔记里当作一个母题来记记住它就能解一大片题。-- 求出每个用户连续登录的最长天数 WITH seq AS ( 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 user_login GROUP BY user_id, login_date ) SELECT user_id, COUNT(*) AS max_days FROM seq GROUP BY user_id, grp ORDER BY max_days DESC;这里的grp就是分组键连续日期的grp相同。注意两个细节一个是用GROUP BY user_id, login_date先对同一天重复登录去重否则行号会被重复日期打乱另一个是不同数据库算日期的函数名不一样MySQL是DATE_SUBSQL Server是DATEADD(DAY, -rn, login_date)Oracle直接日期相减。所以笔记里这道题我会记三份日期语法主体逻辑只写一份。注意连续区间题最容易错在没有先去重。只要同一天有重复记录行号就会偏移分组键立刻失效。这个错误现象是结果里的连续天数普遍偏大或偏小记下这个现象特征排查时一眼就能定位。4.5 行转列与列转行面试和报表都爱考行转列是用CASE WHEN配合聚合把多行压成多列列转行是用UNION ALL把多列摊成多行。这两类题本身不难难在写全写对。行转列的关键是每个要输出的列都得单独写一个CASE WHEN并且外面套一层SUM或MAX把值聚到一起。-- 行转列把每个月的销售额摊成列 SELECT user_id, SUM(CASE WHEN MONTH(created_at) 1 THEN amount ELSE 0 END) AS m1, SUM(CASE WHEN MONTH(created_at) 2 THEN amount ELSE 0 END) AS m2, SUM(CASE WHEN MONTH(created_at) 3 THEN amount ELSE 0 END) AS m3 FROM orders GROUP BY user_id; -- 列转行把多列摊成多行 SELECT user_id, m1 AS mon, m1 AS amount FROM monthly_sales UNION ALL SELECT user_id, m2, m2 FROM monthly_sales UNION ALL SELECT user_id, m3, m3 FROM monthly_sales;真实报表里月份往往是动态的这时静态写法就不够用了得靠动态拼接SQL但那属于另一个话题笔记里单独开一条。我在实操中发现一个细节行转列时ELSE 0和ELSE NULL会导致不同结果用ELSE 0在没有数据时返回0用ELSE NULL返回空报表里要显式0还是显示空白取决于业务所以这两个版本我都记了。SQL Server里还有个PIVOT关键字能直接做行转列语法更简洁但不通用跨库移植时会踩坑。4.6 去重保留最新一条同一用户有多条记录只保留最新一条是日常开发里出现频率极高的需求也是新手最容易写出跑得慢的SQL的地方。思路至少有三种性能差很多我笔记里做成对比表。-- 写法一窗口函数推荐绝大多数数据库通用 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM user_login ) t WHERE rn 1; -- 写法二关联子查询取最大时间 SELECT * FROM user_login a WHERE created_at ( SELECT MAX(created_at) FROM user_login b WHERE b.user_id a.user_id );写法二的问题在于如果同一用户同一时间有两条记录会返回两条去重不彻底而且子查询逐行执行数据量大时慢得明显。写法一用窗口函数一次扫描就能定位是首选。还有一种自连接反查的写法可读性差、性能一般我现在基本不用了只在笔记里留个印象。这条笔记的关键是记下哪种写法在什么数据量下会慢而不只是正确答案是什么。5. 从难题到生产慢SQL与执行计划怎么看5.1 为什么建了索引还是全表扫描学会写对SQL只是及格线写得快才是进阶线。我遇到的最典型的慢查询难题是明明字段上建了索引执行计划里还是全表扫描。这类问题原因很多最常见的有三种我做成速查表方便对照。现象常见原因排查动作索引列上用了函数索引失效把函数挪到条件值一侧隐式类型转换字符串列传了数字检查字段类型与参数类型前导通配符LIKE %x无法走索引改为后缀匹配或全文检索返回列过多回表代价大考虑覆盖索引数据量太小优化器判定全表更快这不算问题别硬调核心心法是索引列要保持原样参与比较。WHERE YEAR(created_at) 2023会失效改成WHERE created_at 2023-01-01 AND created_at 2024-01-01就能用上。这个改写前后差异我在实操里验证过百万级表上从秒级降到毫秒级很常见。至于隐式转换最典型的是字段是VARCHAR你传了个数字数据库把整列转成数字再比索引自然就用不上了。5.2 看执行计划要盯哪几行以MySQL为例EXPLAIN输出里我最关注四个字段type、key、rows、Extra。type从好到坏的顺序大致是system const eq_ref ref range index ALL看到ALL基本就是全表扫描。key显示实际用了哪个索引如果显示NULL说明没走索引。rows是预估扫描行数越大越要警惕。Extra里出现Using filesort表示额外排序Using temporary表示用了临时表这两个都是性能信号。SQL Server里对应的是看执行计划的图形化界面重点看有没有Table Scan、Clustered Index Scan都是扫描以及有没有Key Lookup回表。把这些观察点记进笔记比死记函数名有用得多。我个人的习惯是每优化一个慢查询就把优化前后的执行计划截图存进笔记配上当时的表结构和数据量这样下次遇到相似的表结构翻出来比对就行。提示慢查询优化前一定要先记录优化前的耗时和扫描行数优化后对比。没有基准数据的优化都是玄学你连自己有没有变快都不知道。6. 常见报错与结果异常速查清单6.1 报错类问题怎么对症自学时最常见的报错集中在几类我把它们整理成一张随手可查的表附上根因和动作避免下次又去搜索。报错关键词根因处理方向not in GROUP BY选了非分组、非聚合的列补进GROUP BY或改用聚合WHERE中不能用聚合函数WHERE早于聚合执行改用HAVING语法错误指向WITH数据库版本不支持CTE改子查询或升级版本列名模糊不清多表同名列未加别名统一带表别名前缀连接数超限连接未释放检查连接池与关闭逻辑这里我要单独说WHERE里写聚合函数这条。很多人第一次遇到会以为是写法问题改成HAVING能跑通就完事了。但真正的根因是执行顺序FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。WHERE执行时还没分组哪来的聚合结果把这条顺序背下来以后写SQL你会在脑子里自动过一遍流程很多错误在动手前就能避免。6.2 结果异常类问题怎么定位结果不对但不报错这类问题最难查因为没有提示。我的定位套路是从行数查起先单独跑一遍FROM部分看总行数对不对再逐步往上加JOIN看在哪一步行数开始膨胀或减少最后加WHERE和GROUP BY。这个分层验证法是我笔记里最值钱的一条经验它能快速把问题锁在某个具体环节。-- 分层验证逐层看行数 SELECT COUNT(*) FROM users; -- 基础行数 SELECT COUNT(*) FROM users u JOIN orders o ON o.user_id u.id; -- 看连接后是否膨胀 SELECT COUNT(*) FROM users u LEFT JOIN orders o ON o.user_id u.id; -- 看左连接差异把INNER JOIN和LEFT JOIN的行数一比就能看出有多少用户没订单把连接前后行数一比就能看出是不是一对多。这套方法我用来排查过不少总数翻倍和数据凭空消失的问题比盯着SQL干想有效得多。6.3 顺带聊聊SQL注入这件事的防御视角学SQL到一定程度一定会听说SQL注入这个词很多自学的人只把它当成攻击手段其实站在开发者角度它更应该被理解为一条笔记里的防御红线。核心原则只有一条外部输入永远不要直接拼进SQL字符串必须用参数化查询让数据库把输入当数据而不是当代码来执行。# 反例字符串拼接危险 sql SELECT * FROM users WHERE name name # 正例参数化查询安全 sql SELECT * FROM users WHERE name %s cursor.execute(sql, (name,))我在笔记里给这条的备注是任何拼接都要警惕任何输入都不可信。此外还有两个加固方向值得记一是给数据库账号做最小权限业务账号不需要DROP、TRUNCATE这类权限就别给二是关键输入做长度和格式校验把明显异常的值挡在入口。至于具体的绕过技巧属于攻击知识自学阶段了解防御思路即可不必深挖细节。7. 把笔记真正用起来我的复盘习惯写到这里这套笔记的骨架其实已经完整了环境要脏、模板要全、归类要清、复盘要勤。我最后想分享的是自己坚持几年下来的一点体会。刚开始我也走过弯路把笔记做成答案仓库遇到不会的题就搜一个能跑通的结果贴进去结果半年后翻出来连自己当时为什么那么写都看不懂。真正让笔记产生价值的转折点是我开始逼自己写根因解释那一栏——写不出来就说明没懂回去重学。我个人的经验是SQL这东西的难点从来不在语法数量上语法就那么几十个关键字两周能认全。难点在语义的精确理解和边界情况的处理上而这恰恰是零散刷题刷不出来的。一份扎实的难题笔记本质上是在帮你把散落的经验沉淀成可复用的判断力。等你哪天看到一道新题脑子里能自动浮现这属于连接语义还是NULL逻辑那这份笔记就算真正生效了。如果你现在刚开始建我的建议是从今天遇到的第一道卡壳题开始别等攒够了再建。笔记是长出来的不是规划出来的。每写一条就少一个以后会反复踩的坑。