📖 本篇简介：本篇是"面试踩坑精讲"系列中技术味最浓的一篇，聚焦数据岗笔试与白板题的绝对主力——SQL。11 个高频坑覆盖：WHERE 与 HAVING 混用、GROUP BY 漏列、窗口函数不会、LEFT JOIN 误用、子查询与 JOIN 说不清、索引概念缺失、慢查询分析答错、深分页写法错、去重 COUNT 口径错、SQL 与 pandas 对照说不清、NULL 三值逻辑。前置知识：建议先读第 60 章，并掌握基本 SELECT/GROUP BY/JOIN 语法。学完后你能在白板上写出"语义正确 + 口径清楚 + 能讲清性能"的 SQL，并把每条 SQL 翻译成对应的 pandas 写法。
SQL 不是"背语法"，而是"按引擎的执行顺序思考"——90% 的 SQL 面试挂点，都不是不会写，而是写错了时机、漏了分组、忽略了 NULL。
⚠️ 口径说明：本文基于通用面试经验，所有表示例数据均为手写小表（示意数据），不指向任何真实公司或个人；SQL 用 Python 内置 sqlite3 内存库实跑验证，输出即文中注释所示。
目录
1. 坑 1：WHERE 与 HAVING 混用
2. 坑 2：GROUP BY 漏列与错误分组
3. 坑 3：窗口函数不会写
4. 坑 4：LEFT JOIN 误用
5. 坑 5：子查询与 JOIN 说不清
6. 坑 6：索引概念缺失
7. 坑 7：慢查询分析答错
8. 坑 8：深分页写法错
9. 坑 9：去重 COUNT 口径错
10. 坑 10：SQL 与 pandas 对照说不清
11. 坑 11：NULL 三值逻辑坑
12. 本篇速查
13. 练习题
坑 1：WHERE 与 HAVING 混用
🗂️ 全篇统一示意数据（先跑这一段，只跑一次）：本篇后续 SQL 示例共用两张表orders（订单）和users（用户）。下面这段建表 + 插数代码用 Python 内置sqlite3内存库执行一次即可，之后每个 SQL 块都基于这两张表，注释里的实跑输出也都由它复现。（MySQL / PostgreSQL 语法基本一致，把INTEGER PRIMARY KEY换成对应类型即可。）
-- 示意数据：订单表 orders（8 行，amount 故意留 1 个 NULL，用于后面 COUNT/NULL 的演示）
CREATE TABLE orders(
order_id INTEGER PRIMARY KEY,
user_id INTEGER,
city TEXT,
amount REAL,
status TEXT -- paid=已支付，refund=已退款
);
INSERT INTO orders VALUES
(1,1,'北京',100,'paid'),(2,1,'北京',200,'refund'),
(3,2,'上海',300,'paid'),(4,2,'上海',50,'paid'),(5,3,'北京',400,'paid'),(6,4,'广州',150,'paid'),
(7,5,'上海',NULL,'paid'),(8,3,'北京',60,'refund');
-- 口径：已支付 上海300+50=350(均175)/北京400+100=500(均250)/广州150(均150)；SUM(amount)=1260(跳过NULL)；user_id 去重后 5 个
-- 示意数据：用户表 users（user_id=6 王五故意不下单，专用于 LEFT JOIN 演示"保留左表"）
CREATE TABLE users(user_id INTEGER, name TEXT);
INSERT INTO users VALUES (1,'张三'),(2,'李四'),(6,'王五');
场景与错误示范
场景：白板上，面试官给一张订单表 orders(order_id, user_id, city, amount, status)，要求"统计每个城市的客单价，只保留客单价大于 100 的城市，且只统计已支付订单"。
错误示范代码：
-- 错误：把"聚合后的过滤"写进 WHERE，把"行级过滤"写进 HAVING，还想用别名
SELECT city, AVG(amount) AS avg_amt
FROM orders
WHERE avg_amt > 100 -- 错：WHERE 执行时聚合还没发生，别名 avg_amt 还不存在
GROUP BY city
HAVING status = 'paid'; -- 错：HAVING 是过滤"组"的，拿行级字段 status 在这里过滤毫无意义
错误示范对话：
候选人：（写完）跑一下，报错了。
>
面试官：先别改报错。WHERE 和 HAVING 谁先执行？为什么 WHERE avg_amt > 100 不行？status='paid' 你又为什么放 HAVING 里？>
候选人：呃……都是过滤吧，HAVING 就是分组后的 WHERE？（没答到执行顺序上）
为什么是坑
- 本质是"逻辑执行顺序"没建立：SQL 的书写顺序是
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY，但逻辑执行顺序是FROM → WHERE → GROUP BY → 聚合 → HAVING → SELECT → ORDER BY。WHERE 在 GROUP BY 之前执行，此时还没有分组、没有聚合结果，所以 WHERE 里既不能用聚合函数，也不能用 SELECT 里起的别名；HAVING 在 GROUP BY 之后执行，过滤的是"分组后的组"，只有它能用聚合结果。 - 放错位置不只是报错，更可能"不报错但算错"：把行级过滤
status='paid'放 HAVING，在很多宽松引擎里行为诡异；把本该 WHERE 做的过滤挪到 HAVING，会先做无谓的聚合再丢数据，结果还可能对，但性能和语义都说不清。 - 面试官在考什么：他不是考你背关键字，而是考你"是否理解引擎先干嘛后干嘛"——这决定了你后面 GROUP BY、窗口函数、子查询能不能写对。
正确做法
口诀：行级过滤用 WHERE（聚合前），组级过滤用 HAVING（聚合后）；能用 WHERE 提前过滤的，绝不留到 HAVING。
-- 正确：先 WHERE 滤掉未支付订单（聚合前，行数越少越快），再分组，再 HAVING 滤组
SELECT city, AVG(amount) AS avg_amt -- 每个城市的客单价（示意数据）
FROM orders
WHERE status = 'paid' -- 行级过滤：只看已支付，聚合前就缩小数据量
GROUP BY city
HAVING AVG(amount) > 100; -- 组级过滤：客单价 > 100 的城市才保留
-- 实跑输出（sqlite3）：上海 175.0、北京 250.0、广州 150.0（已支付：上海300+50=350/北京400+100=500/广州150）
关于别名：WHERE 里不能用 SELECT 别名（执行顺序决定），HAVING 里直接写 AVG(amount) 最保险；少数引擎（如 MySQL）允许 HAVING/ORDER BY 用别名，但面试统一按标准 SQL 写，到哪都不会错。实跑验证：WHERE s>100 在 sqlite3 直接报 misuse of aggregate: SUM()。
加分点与延伸
- 加分点：主动说"能用 WHERE 提前过滤就提前过滤，减少进入 GROUP BY 的行数"——一句话暴露你有性能直觉。
- 延伸：为什么
ORDER BY里能用别名？因为它是最后执行、此时 SELECT 的别名已经算出来了；同一个"别名可用性"问题，三个位置三种答案，能讲清就很加分。
坑 2：GROUP BY 漏列与错误分组
场景与错误示范
场景：面试官要求"统计每个城市、每种订单状态的订单数"。
错误示范代码：
-- 错误：SELECT 里出现了 status，但 GROUP BY 只写了 city
SELECT city, status, COUNT(*) AS cnt
FROM orders
GROUP BY city; -- 漏了 status：每个 city 对应多种 status，引擎不知道取哪一行的 status
错误示范对话：
候选人：写完了。
>
面试官：你 GROUP BY 只写了 city，但 SELECT 里有 status。上海有 paid 也有 refund，这一列你给我哪个？
>
候选人：（愣住）呃……那我把 status 也 group by 一下？（面试官追问：为什么 MySQL 5.7 之前这种写法"能跑"？能跑就是对的吗？）
为什么是坑
- 核心规则：
SELECT后面凡是没有被聚合函数包起来的列，必须全部出现在 GROUP BY 里。因为分组后每个组只剩一行，组内某列有多个值时，引擎无从选择——要么报错，要么（在宽松引擎下）随机取一行，这是静默错误，比报错更可怕。 - "能跑"不等于"对"：宽松 SQL 模式（如老 MySQL 的
ONLY_FULL_GROUP_BY关闭时）会放你过，但结果是未定义的随机值，报表口径直接崩。现代 MySQL（5.7+ 默认开启）、PostgreSQL、Hive 默认都是严格模式，这类写法直接报错。 - 面试官在考什么：分组粒度意识——你要"按什么切，就 group by 什么"，SELECT 的非聚合列是分组粒度的"声明"，漏一列就是粒度错了。
正确做法
SELECT 的非聚合列与 GROUP BY 列严格对齐：
-- 正确：统计 城市 × 状态 两个维度的订单数
SELECT city, status, COUNT(*) AS cnt
FROM orders
GROUP BY city, status; -- SELECT 里的 city、status 都在 GROUP BY 里
-- 实跑输出（sqlite3，示意数据）：
-- ('上海','paid',3) ('北京','paid',2) ('北京','refund',2) ('广州','paid',1)
自查口诀：写完 GROUP BY，回头逐个数 SELECT 里的列——每一列要么被 COUNT/SUM/AVG/MIN/MAX 包着，要么在 GROUP BY 里，缺一不可。
pandas 对照（呼应第 13 章 groupby）：pandas 的 groupby 天然强制按分组键聚合，不会出现"漏列"问题，这也是它比手写 SQL 更不容易犯这个错的原因。
加分点与延伸
- 加分点：主动提"
ONLY_FULL_GROUP_BY严格模式"这个词，并说明"宽松模式下能跑是坑，不是特性"。 - 延伸：多列分组的粒度是"组合粒度"——
GROUP BY city, status得到的是每个 (城市, 状态) 组合一行，而不是"城市一行 + 状态一行"，这个组合关系要在脑子里过一遍。
坑 3：窗口函数不会写
场景与错误示范
场景：面试官问"取每个城市金额最高的那笔订单"，或者"给每个城市的订单按金额排名"。
错误示范对话：
候选人：我先每个城市求 MAX(amount)，再用这个 MAX 去 JOIN 回原表……
>
面试官：可以，但如果并列第一呢？北京两笔都是 400，你这个 MAX 回连会出来几行？要"每组 Top N"怎么办？
>
候选人：（沉默）……那我用窗口函数？ROW_NUMBER() OVER (...)，PARTITION BY 和 ORDER BY 分别写什么来着？
为什么是坑
- 窗口函数是 SQL 面试的分水岭："取每组 Top N""连续登录问题""环比同比""累计求和"——这些高频真题用
GROUP BY + 自连接能写但又绕又容易错，窗口函数三行搞定。不会窗口函数，基本在这一类题上直接出局。 - 三个排名函数的区别是必考点：
ROW_NUMBER不重复（并列也强行排 1、2）、RANK并列同名次且跳号（1、1、3）、DENSE_RANK并列同名次不跳号（1、1、2）。说错区别 = 没真用过。 - PARTITION BY 是"分组但不合并行"：它和 GROUP BY 的关键区别是——GROUP BY 每组压成一行，窗口函数每组保留每一行，只是在每行旁边算出组内统计值。这个"分组不降行"的语义是理解一切窗口函数的钥匙。
正确做法
先背骨架：`函数() OVER (PARTITION BY 分组列 ORDER BY 排序列)`。
-- 需求：每个城市的已支付订单，按金额从高到低排名，并算出每个城市的支付总额（不降行）
SELECT city, user_id, amount,
ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn, -- 组内行号，不并列
RANK() OVER (PARTITION BY city ORDER BY amount DESC) AS rk, -- 并列同名次且跳号
DENSE_RANK() OVER (PARTITION BY city ORDER BY amount DESC) AS drk, -- 并列同名次不跳号
SUM(amount) OVER (PARTITION BY city) AS city_sum -- 组内合计，每行都带
FROM orders
WHERE status = 'paid' AND amount IS NOT NULL
ORDER BY city, amount DESC;
-- 实跑输出（sqlite3，示意数据，已支付且金额非空）：
-- 上海 300→rn1/cs350、50→rn2/cs350；北京 400→rn1/cs500、100→rn2/cs500；广州 150→rn1/cs150（rk/drk 同 rn，并列时才分叉）
"每组 Top N"标准套路：外层套一层 WHERE rn = 1：
-- 取每个城市金额最高的那笔订单（Top 1）
SELECT * FROM (
SELECT city, order_id, amount,
ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
FROM orders WHERE status = 'paid'
) t
WHERE rn = 1; -- 注意：窗口函数的别名 rn 不能直接写进 WHERE，必须包一层子查询
方言说明：窗口函数 MySQL 8.0+、PostgreSQL、Hive、SQLite 3.25+ 都支持；但 MySQL 5.7 及以下不支持窗口函数，遇到老库只能用变量或自连接迂回，面试时可主动提一句"我们当时用的 MySQL 8，直接开窗；如果是 5.7 就得多套一层自连接"。
加分点与延伸
- 加分点：说完
ROW_NUMBER/RANK/DENSE_RANK的区别后，主动补一句"要去重取一条用 ROW_NUMBER，要保留并列用 DENSE_RANK"，给出选型结论。 - 延伸：
LAG(amount,1) OVER (...)取上一行、LEAD取下一行，是做环比、留存、连续登录题的利器，常和groupby.shift对照考（见坑 10）。
坑 4：LEFT JOIN 误用
场景与错误示范
场景：用户表 users(user_id, name) 有 3 个用户（含一个从没下过单的"王五"），订单表 orders。需求是"列出所有用户，以及他们已支付订单的金额，没下单的用户也要保留"。
错误示范代码：
-- 错误：把右表（订单）的过滤条件写进了 WHERE，LEFT JOIN 当场退化成 INNER JOIN
SELECT u.user_id, o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'paid'; -- 致命：没下过单的用户，o.status 是 NULL，被这一行整行过滤掉了
错误示范对话：
候选人：跑出来 4 行。
>
面试官：不是 3 个用户吗？王五呢？
>
候选人：王五没下单……是不是被 WHERE 筛掉了？那条件放 ON 里？（面试官又追问：一个用户 3 笔订单，LEFT JOIN 后这个用户变几行？金额会被放大吗？）
为什么是坑
- ON vs WHERE 的执行时机不同：ON 是"连接时"就应用条件，决定右表哪些行参与匹配，不匹配的右表行补 NULL、左表行保留；WHERE 是"连接完之后"再过滤结果集。一旦你在 WHERE 里写了右表的非空条件（如
o.status='paid'），那些右表全 NULL 的左表行（没下过单的用户）就会被整行删掉——LEFT JOIN 名存实亡，等价于 INNER JOIN。这是 SQL 面试最高频的"语义正确但结果错"的坑。 - 多对多 JOIN 导致行数/金额膨胀：左表一行对右表多行时，结果行数 = 左表行数 × 匹配的右表行数。这时候你再
SUM，金额会被放大。比如 1 个用户 3 笔订单，SUM(amount) 是 3 行加总没错，但若你同时再 JOIN 一张"优惠券表"（1 对 2），行数就变成 6，金额翻倍——连接前必须先想清两张表的基数关系，能先聚合再 JOIN 就先聚合。 - 面试官在考什么：你是不是真理解"LEFT JOIN 保谁、丢谁、怎么放大行"，而不是背"要保留左表就写 LEFT JOIN"。
正确做法
右表的过滤条件放 ON 里，左表/最终结果的过滤才放 WHERE：
-- 正确：已支付的过滤放 ON，右表没匹配上的行补 NULL，但用户保留
SELECT u.user_id, o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id AND o.status = 'paid'; -- 条件放 ON
-- 实跑输出（sqlite3，示意数据）：user_id=1→100.0，user_id=2→50.0/300.0，user_id=6→NULL（王五保留）；错误写法 WHERE o.status='paid' 会让王五(user_id=6)直接消失
多对多防膨胀口诀：能在子查询里先把右表聚合成"一行对一行"再 JOIN，就绝不拿明细表直接 JOIN 明细表：
-- 正确姿势：先把订单按用户聚成一行，再和用户表 1:1 连接，杜绝行膨胀
SELECT u.user_id, u.name, COALESCE(t.paid_amt, 0) AS paid_amt
FROM users u
LEFT JOIN (
SELECT user_id, SUM(amount) AS paid_amt
FROM orders WHERE status = 'paid'
GROUP BY user_id
) t ON u.user_id = t.user_id;
加分点与延伸
- 加分点：主动说"
COALESCE(t.paid_amt, 0)把 NULL 变成 0，报表更好看"，顺手覆盖 NULL 处理（见坑 11）。 - 延伸：面试追问"LEFT JOIN 和 INNER JOIN 性能差多少"，答"性能差往往不在 JOIN 类型，而在有没有索引、连接顺序；语义正确永远先于性能"——稳。
坑 5：子查询与 JOIN 说不清
场景与错误示范
场景：面试官要求"查出下过单的用户及其订单金额"，然后追问"你用 JOIN 还是子查询？IN 和 EXISTS 有什么区别？"
错误示范对话：
候选人：我用 WHERE user_id IN (SELECT user_id FROM orders)。>
面试官：这和直接 JOIN 结果一样吗？性能呢？什么时候用 IN、什么时候用 EXISTS？
>
候选人：（卡壳）呃……IN 就是子查询，JOIN 就是连接，性能应该 JOIN 快吧？（面试官追问："应该"？为什么？——当场卡壳）
为什么是坑
- 很多场景下优化器会把它们改写成等价计划：现代数据库（MySQL、PostgreSQL、SQLite）的优化器经常把
IN (子查询)自动改写成 JOIN，所以"IN 一定慢 / JOIN 一定快"这种绝对化说法本身就是错的。面试官要的不是结论口号，而是直觉。 - IN 与 EXISTS 的语义差在"空集 vs 相关"：
IN (SELECT ...)是把子查询结果物化成一个列表再做匹配；EXISTS (SELECT 1 FROM 右表 WHERE 右表.id = 左表.id)是相关子查询——对外层每一行，跑去右表"探一下有没有匹配"，找到第一条就停。一般经验：子查询结果集小用 IN，外层小、右表大有索引用 EXISTS；但更关键的坑是 IN 子查询里一旦出现 NULL，结果直接变空（见坑 11），这才是面试爱考的"陷阱点"。 - 面试官在考什么：你是"凭感觉选写法"，还是"知道每种写法的语义边界和失效条件"。
正确做法
- 要右表的列 → 用 JOIN；只是判断"存在/不属于"、不要右表列 → 用 IN / EXISTS。
- 两者结果对比实跑（sqlite3，示意数据）：
-- IN：判断当前订单的用户是否存在于 users 表
SELECT user_id, amount FROM orders o
WHERE user_id IN (SELECT user_id FROM users);
-- EXISTS：相关子查询，语义等价
SELECT user_id, amount FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.user_id = o.user_id);
-- 两条 SQL 实跑输出完全一致：(1,100)(1,200)(2,300)(2,50)
参考话术（被问"怎么选"）：
一般我要取右表字段就用 JOIN；只做存在性判断就用 IN 或 EXISTS。性能上我知道现代优化器经常把 IN 改写成 JOIN，不会死记"谁快"；真正影响性能的是连接列有没有索引，以及——IN 的子查询里如果可能有 NULL，会让结果整个变空，这种情况我会改用 EXISTS 或先滤掉 NULL。
加分点与延伸
- 加分点：主动提"相关子查询（EXISTS）对外层每行执行一次，所以外层结果要小、右表连接列要有索引"，说出执行次数的直觉。
- 延伸：
NOT IN是最危险的——子查询结果含 NULL 时它返回空集，排查这种 bug 往往要半天（详见坑 11）。
坑 6：索引概念缺失
场景与错误示范
场景：面试官问"你写的 SQL 很慢，你怎么优化？"候选人只会说"加个索引吧"，但追问就露馅。
错误示范对话：
面试官：那什么是索引？建在哪些列上？建索引有没有代价？什么叫覆盖索引？EXPLAIN 你看过吗，看什么字段？
>
候选人：（从"让查询变快的东西"一路答到沉默）……代价是占空间？
>
面试官：（低头做标记）这几个概念你能展开哪一个？
为什么是坑
- 数据岗不要求当 DBA，但"看得懂索引、说得清直觉"是硬门槛：取数慢、报表慢是日常，你连"为什么慢、加索引加在哪"都答不出，面试官会怀疑你写的 SQL 在生产上就是灾难。
- 索引的本质是"空间换时间"：类似书的目录——不对全表逐行翻（全表扫描），而是先查目录（B+ 树）定位到目标行的位置。代价是：占额外存储、每次 INSERT/UPDATE/DELETE 都要同步维护索引（写变慢），所以不是越多越好。
- 唯一索引 vs 普通索引 vs 覆盖索引：唯一索引在加速之外还强制列值不重复（也是约束）；覆盖索引指"要查的列全都在索引里，不用回表查原表"，性能最好。面试官连问三个概念，答不出两个基本就过不了这轮。
正确做法
能用一句大白话讲清，再说建在哪、怎么验证：
索引就像书的目录，把常用过滤/排序的列按 B+ 树组织好，查询时顺着目录直接定位，不用逐行扫全表。代价是占空间、拖慢写入。我一般建在频繁出现在 WHERE、JOIN、ORDER BY 的列上，选择性高（区分度大）的列优先；建完用 EXPLAIN 确认是不是真的走到了索引。🧪 坑 6/7/8 专用：先造一张 1 万行的 orders 大表（下面这段用 sqlite3.connect(':memory:') 新建了独立内存库，仅供坑 7/8 的索引与分页演示；坑 9 起会回到坑 1 的 8 行小表，文末已附恢复块）。用 Python 循环插数，结果可复现：import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
# 重建 orders 为 1 万行：order_id 主键 1..10000，user_id 在 0..499 循环，另加 create_time 列（坑 7 演示用）
cur.execute("DROP TABLE IF EXISTS orders")
cur.execute("CREATE TABLE orders(order_id INTEGER PRIMARY KEY, user_id INT, city TEXT, amount REAL, status TEXT, create_time TEXT)")
rows = [(i, i % 500, ['北京','上海','广州'][i % 3], float(i*3 % 97), ['paid','refund'][i % 2],
f"2026-10-{i % 28 + 1:02d}") # 每行一个可复现日期（落在 2026-10，坑 7 范围查询用）
for i in range(1, 10001)]
cur.executemany("INSERT INTO orders VALUES(?,?,?,?,?,?)", rows) # 6 个占位符对应 6 列，一次批量插入 1 万行
con.commit()
# order_id=9991..9995 的 amount 依次为 0,3,6,9,12（坑 8 分页用它）
-- sqlite3 实跑：同一条查询，建索引前后的执行计划对比（示意：1 万行 orders 表）
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 42;
-- 无索引时输出：SCAN orders（全表扫描，一行行扫 1 万行）
CREATE INDEX idx_user ON orders(user_id); -- 在过滤列 user_id 上建普通索引
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 42;
-- 建索引后输出：SEARCH orders USING INDEX idx_user (user_id=?)（走索引，直接定位）
覆盖索引一句话：如果查询只要 user_id, amount，就建 (user_id, amount) 的联合索引，因为要取的列都在索引里，引擎不用再回原表，这就是覆盖索引。
加分点与延伸
- 加分点：主动说"索引不是建得越多越好——每张表索引太多，写入会变慢，要权衡读多还是写多"。
- 延伸：联合索引遵守"最左前缀"原则（
(a,b,c)索引能支持a、a,b、a,b,c的过滤，但支持不了单独b），面试能提一句就是加分。
坑 7：慢查询分析答错
场景与错误示范
场景：面试官拿出一条慢 SQL：SELECT * FROM orders WHERE status='paid' AND DATE(create_time)='2026-10-01'，问"为什么走了全表扫描，怎么改？"
错误示范对话：
候选人：慢的话……把 SELECT * 改成具体列？
>
面试官：对，这是一点。但为什么 WHERE DATE(create_time)='2026-10-01' 建了索引也不走？怎么写才能用上索引？>
候选人：（卡壳）……是不是函数包列的问题？怎么改我还没想好。
为什么是坑
- 对索引列套函数 = 索引失效：
DATE(create_time)这种写法，意味着引擎要先把每一行的create_time算成 DATE 再比较——索引里存的是原始的create_time值，函数包了一层之后没法直接用索引定位，只能全表算一遍。"不要在索引列上做运算/函数包裹"是慢查询优化的第一铁律。 - SELECT * 的两个坏处：一是把不需要的列全读出来，增加 IO 和内存；二是破坏覆盖索引——你明明可以建个覆盖索引只取需要的列，
SELECT *却逼引擎每次都回表。 - 面试官在考什么：你排查慢查询的套路——先
EXPLAIN看是不是全表扫描，再看过滤条件有没有函数包裹、有没有走索引，最后才是改写法。
正确做法
铁律一：把"对列算函数"改写成"对常量算区间"，让列本身保持裸的：
-- 错误：对索引列 create_time 套函数，索引失效，只能全表扫
-- SELECT * FROM orders WHERE DATE(create_time) = '2026-10-01';
-- 正确：改成范围比较，create_time 列保持"裸列"，才能用上索引
SELECT order_id, user_id, amount -- 错误写法里的 SELECT * 也改成只取需要的列
FROM orders
WHERE create_time >= '2026-10-01'
AND create_time < '2026-10-02'; -- 等价于"10 月 1 日当天"，但能走索引
铁律二：杜绝 `SELECT *`，只取需要的列，既省 IO 又可能命中覆盖索引。
慢排查三板斧（可直接照着说）：
第一步 EXPLAIN 看 type 是不是 ALL（全表扫描）；第二步看 WHERE/JOIN/ORDER BY 的列有没有索引、有没有被函数包裹；第三步能加覆盖索引就加，能缩小结果集就缩小。-- sqlite3 实跑验证：函数包列后，执行计划退化为全表扫描
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE CAST(user_id AS TEXT) = '42';
-- 输出：SCAN orders ← 明明 user_id 上有索引，套了 CAST 后照样全表扫
加分点与延伸
- 加分点：主动提"隐式类型转换也会让索引失效"（比如字符串列和数字比较），说明你见过真坑。
- 延伸：慢查询不只看 SQL 写法——数据量、统计信息过期、锁等待都可能是元凶；面试先答"先 EXPLAIN 定位"，再谈改写，体现排查方法论而非死记结论。
坑 8：深分页写法错
场景与错误示范
场景：面试官问"订单表有 1000 万行，你要做后台分页，每页 20 条，用户翻到第 500 页怎么写？"
错误示范代码：
-- 错误：LIMIT 10000, 20 —— 深分页时数据库要先扫描前 10020 行再丢掉前 10000 行
SELECT order_id, amount FROM orders
ORDER BY order_id
LIMIT 20 OFFSET 10000; -- OFFSET 越大越慢，翻到第 5000 页几乎卡死
错误示范对话：
候选人：就 LIMIT 20 OFFSET 10000 呗。>
面试官：这表 1000 万行，用户翻到第 5 万页呢？这条 SQL 为什么会越来越慢？生产上怎么解决深分页？
>
候选人：（卡壳）……因为 offset 要跳过很多行？后面那个问题没答上来。
为什么是坑
- OFFSET 的本质是"扫过去再扔掉"：
LIMIT 20 OFFSET 10000不是"直接跳到第 10001 行"，而是要扫描并排序前 10020 行，再把前 10000 行丢弃，只返回最后 20 行。OFFSET 越大，白扫的行越多，复杂度和偏移量成正比——这就是深分页随页码变深而变慢的根因。 - 面试官在考什么：你知不知道"游标分页（Keyset Pagination）"这个标准解法——不跳行，而是记住上一页最后一条的位置（游标），下一页直接从那里往后取。
- 适用边界：
LIMIT/OFFSET在浅分页（前几页）完全够用，别过度设计；深分页（后台翻几百上千页、feed 流无限下滑）才需要游标方案。
正确做法
浅分页用 OFFSET 没问题；深分页换成"基于游标的 Keyset 分页"：
-- 第 1 页：正常取
SELECT order_id, amount FROM orders ORDER BY order_id LIMIT 5;
-- 假设上一页最后一条 order_id=9990（前端带回当"游标"）；下一页直接从 9990 之后取 5 条
SELECT order_id, amount FROM orders
WHERE order_id > 9990 -- 游标条件：走 order_id 索引，一步定位，不扫前面的行
ORDER BY order_id
LIMIT 5;
-- 实跑输出（sqlite3，示意：1 万行表）：两种写法都拿到 (9991,0.0)(9992,3.0)(9993,6.0)(9994,9.0)(9995,12.0)，结果一样，但后者不随翻页变深而变慢
参考话术：
浅分页LIMIT/OFFSET就行；一旦页码很深或数据量到百万千万级，我会改用游标分页——上一页记住最后一条的排序键（通常是主键或唯一排序字段），下一页用WHERE 排序键 > 上一页最后值 ORDER BY 排序键 LIMIT n，走索引直接定位，性能恒定不随翻页恶化。代价是不支持"直接跳到第 N 页"，只能上一页/下一页，feed 流场景正好是这个交互。
加分点与延伸
- 加分点：主动说"游标分页的排序键必须是唯一且有索引的"，否则翻页会漏行或重行。
- 延伸：
WHERE id > last_id配合"上拉加载更多"是 App feed 流的标准做法，能和业务场景结合着讲更生动。
坑 9：去重 COUNT 口径错
场景与错误示范
场景：报表里"用户数"到底是多少？订单表 8 行，面试官问"这里有多少个下单用户？"
错误示范对话：
候选人：COUNT(*)，8 个。
>
面试官：不对，一个用户可能下多单，这 8 是订单数还是用户数？要"去重用户数"呢？user_id 里要是还有 NULL 呢？
>
候选人：哦——COUNT(DISTINCT user_id)？（那 COUNT(*)、COUNT(列)、COUNT(DISTINCT 列) 三者区别，当时没立刻答全）
为什么是坑
- 三个 COUNT 语义完全不同，混了就是口径事故：
COUNT(*)数行数（含 NULL）；COUNT(列)数该列非 NULL 的行数；COUNT(DISTINCT 列)数该列去重后的非 NULL 值个数。订单 8 行，但同一 user_id 出现多次，去重用户数远小于 8。 - 报表里"用户数"几乎永远是去重口径：DAU、下单用户数、付费用户数，全部默认
COUNT(DISTINCT user_id)。用COUNT(*)报出来的"用户数"是订单数，口径错了报表就废了——这在数据岗是会被业务方当场质疑的硬伤。 - 面试官在考什么：你有没有"口径意识"——每个数字都要能说出"它数的是什么行、去没去重、怎么处理 NULL"。
正确做法
🔁 先回到坑 1 的 8 行小表：坑 6/7/8 用的是新建的 1 万行内存库，这里 DROP 后重建为坑 1 的口径小表（含 1 个 NULL 金额、含 users 表），坑 9/10/11 的 COUNT 与 NULL 演示都基于它：
DROP TABLE orders;
CREATE TABLE orders(order_id INTEGER PRIMARY KEY, user_id INTEGER, city TEXT, amount REAL, status TEXT);
INSERT INTO orders VALUES
(1,1,'北京',100,'paid'),(2,1,'北京',200,'refund'),
(3,2,'上海',300,'paid'),(4,2,'上海',50,'paid'),(5,3,'北京',400,'paid'),(6,4,'广州',150,'paid'),
(7,5,'上海',NULL,'paid'),(8,3,'北京',60,'refund');
CREATE TABLE users(user_id INTEGER, name TEXT);
INSERT INTO users VALUES (1,'张三'),(2,'李四'),(6,'王五');
实跑对比（sqlite3，示意数据：8 行订单，amount 有 1 个 NULL，user_id 去重后 5 个）：
SELECT COUNT(*) FROM orders; -- 8：总行数（含 NULL 行）
SELECT COUNT(amount) FROM orders; -- 7：amount 非 NULL 的行（1 个 NULL 被忽略）
SELECT COUNT(DISTINCT user_id) FROM orders; -- 5：去重下单用户数 ★报表口径
SELECT SUM(amount) FROM orders; -- 1260.0：SUM 同样忽略 NULL 行
口径解释模板（被问"这个数怎么算的"）：
这个"下单用户数"口径是COUNT(DISTINCT user_id)，在已支付订单明细上做的，同一个用户下多单只算一个；金额用SUM(amount)，系统会自动跳过金额为 NULL 的异常单。这样口径和业务方周报里的"付费用户数"是一致的。
加分点与延伸
- 加分点：主动提"
COUNT(*)和COUNT(1)在标准 SQL 里等价，不用纠结；真正要分清的是COUNT(列)和COUNT(*)"。 - 延伸：性能上
COUNT(DISTINCT)在大数据量下较重，有时用"先 GROUP BY 去重再 COUNT"的子查询代替，但口径不变；面试先把口径讲对，再谈优化。
坑 10：SQL 与 pandas 对照说不清
场景与错误示范
场景：面试官换个考法——"你简历写 SQL 和 pandas 都熟，那我问你：SQL 的 GROUP BY 在 pandas 里怎么写？窗口函数 SUM() OVER (PARTITION BY city) 呢？LEFT JOIN 呢？"
错误示范对话：
候选人：GROUP BY 就是 groupby……窗口函数的话，pandas 里好像也有窗口？
>
面试官：SUM() OVER (PARTITION BY city)，要在每行旁边带上它所在城市的总额，怎么写？LAG（取上一行）呢？
>
候选人：（卡住）groupby 之后就只剩一行了……那 transform？（面试官点头）那 LAG/shift 呢？——当场没想起来。为什么是坑
- SQL 和 pandas 是同一套思维的两种语法，对应关系一旦打通，两边都更好记。答不上来 = 你要么 SQL 是背的、要么 pandas 是背的，至少有一边没真懂。
- 三组核心映射必须背熟：
GROUP BY + 聚合↔df.groupby(分组列)[列].聚合()；窗口函数（分组但不降行）↔groupby.transform()（每组计算后回填到每一行）；RANK/DENSE_RANK↔groupby.rank()；LAG/LEAD↔groupby.shift()；JOIN↔df.merge(right, on=键, how='left'/'inner')。- 面试官在考什么：跨工具迁移能力——这正是第 42 章强调的"双引擎"思维。
正确做法
实跑对照（pandas 3.0.6，示意数据）：
import pandas as pd
# 示意数据：城市 × 用户 × 金额
df = pd.DataFrame({
"city": ["北京", "北京", "上海", "上海", "广州"],
"user_id": [1, 1, 2, 2, 4],
"amount": [100, 200, 300, 50, 150],
})
# ① SQL: SELECT city, SUM(amount) FROM orders GROUP BY city → 输出 上海350/北京300/广州150（每组压一行）
print(df.groupby("city")["amount"].sum())
# ② SQL: SUM() OVER (PARTITION BY city) —— 分组但不降行，每行都带城市总额
df["city_sum"] = df.groupby("city")["amount"].transform("sum")
# SQL: DENSE_RANK() OVER (PARTITION BY city ORDER BY amount DESC)
df["city_rank"] = df.groupby("city")["amount"].rank(ascending=False, method="dense")
print(df[["city", "amount", "city_sum", "city_rank"]])
# 北京 200 → city_sum=300, rank=1；北京 100 → city_sum=300, rank=2（每行都带城市总额）
# ③ SQL: LAG(amount) OVER (PARTITION BY city ORDER BY amount) 取组内上一行
df["prev_amt"] = df.sort_values("amount").groupby("city")["amount"].shift(1) # LAG：北京100行prev=NaN，200行prev=100
# ④ SQL: LEFT JOIN users ON user_id
users = pd.DataFrame({"user_id": [1, 2, 6], "name": ["张三", "李四", "王五"]})
print(users.merge(df, on="user_id", how="left")) # how='left' ↔ LEFT JOIN
# 王五(user_id=6)没有订单，merge 后 amount 为 NaN —— 与 SQL LEFT JOIN 补 NULL 完全一致
对照表（面试可直接背）：
| SQL | pandas |
|---|---|
| GROUP BY city SUM(amount) | df.groupby("city")["amount"].sum() |
| SUM() OVER (PARTITION BY city) | df.groupby("city")["amount"].transform("sum") |
| RANK/DENSE_RANK() OVER (...) | df.groupby(..)[col].rank(method="dense") |
| LAG/LEAD | df.groupby(..)[col].shift(1 / -1) |
| LEFT JOIN | left.merge(right, on=.., how="left") |
| WHERE 过滤行 | df[df["col"] > 100] |
加分点与延伸
- 加分点：点出"transform 是窗口函数的 pandas 版，因为它分组后不降行；普通 groupby 聚合是降行的，对应 GROUP BY"——一句话讲透区别。
- 延伸：数据量小用 pandas（内存快、灵活），数据量大先用 SQL 在库里聚合好再取到 pandas，这正是第 33 章的"双引擎"分工。
坑 11：NULL 三值逻辑坑
场景与错误示范
场景：面试官出了一道"陷阱题"：SELECT ... FROM orders WHERE user_id NOT IN (1, NULL)，问返回几行。
错误示范对话：
候选人：user_id 不是 1 的都返回，大概 6 行吧。
>
面试官：（笑）错，是空集，0 行。那 NULL = NULL 是 true 还是 false？>
候选人：……true？（面试官摇头）是 UNKNOWN——这就是 NULL 的三值逻辑。
为什么是坑
- SQL 里不是二值逻辑，是三值逻辑：任何值和 NULL 比较（
=,<>,>,IN）结果都既不是 true 也不是 false，而是 UNKNOWN。NULL = NULL的结果是 UNKNOWN（不是 true！），所以"判断相等"必须用IS NULL/IS NOT NULL。 - `NOT IN` 遇到 NULL 直接归零：
x NOT IN (1, NULL)等价于x <> 1 AND x <> NULL，而x <> NULL是 UNKNOWN，整个 AND 结果不可能为 true，于是一行都不返回——一条看起来人畜无害的 SQL 悄无声息地返回空，排查半天。这是 SQL 面试最高频的"阴间题"。 - 聚合函数自动忽略 NULL：
SUM/AVG/COUNT(列)遇到 NULL 行是跳过的（见坑 9），不是把 NULL 当 0。不了解这点，金额算出来对不上账。
正确做法
实跑验证（sqlite3）：
SELECT NULL = NULL; -- 结果是 NULL（即 UNKNOWN），不是 1(true)
SELECT NULL IS NULL; -- 结果是 1(true)：判断 NULL 必须用 IS NULL
SELECT user_id FROM orders WHERE user_id NOT IN (1, NULL); -- NOT IN 混入 NULL → 实跑返回 [] 空集
保命三条：
1. 判断空值永远用 `IS NULL` / `IS NOT NULL`，绝不写 = NULL、<> NULL；
2. 写 `NOT IN` 之前，先确认子查询/列表里没有 NULL；不确定就改用 NOT EXISTS，或在子查询里 WHERE col IS NOT NULL；
3. 聚合函数会跳过 NULL，要"空值当 0"就先 COALESCE(amount, 0)，别让 SUM 偷偷跳过行。
-- 安全写法：去重用户数，先把可能为 NULL 的 user_id 滤掉，再 NOT IN
SELECT user_id FROM orders
WHERE user_id NOT IN (
SELECT user_id FROM users WHERE user_id IS NOT NULL -- 主动排除 NULL
);
加分点与延伸
- 加分点：主动讲出"UNKNOWN 在 WHERE 里被当作 false 处理，所以未知的行不会被选中"，把三值逻辑落到执行效果上。
- 延伸：建表时关键列设
NOT NULL DEFAULT 0（金额）/NOT NULL DEFAULT ''（状态），从源头减少 NULL 蔓延——这是工程层面的根治方案。
本篇速查
| 坑号 | 坑名 | 一句话要点 | 正确做法关键词 |
|---|---|---|---|
| 坑 1 | WHERE 与 HAVING 混用 | WHERE 聚合前行级过滤，HAVING 聚合后组级过滤 | 行过滤用 WHERE、组过滤用 HAVING、别名别进 WHERE |
| 坑 2 | GROUP BY 漏列与错误分组 | SELECT 非聚合列必须全在 GROUP BY 里 | 逐列自查、组合粒度、别信宽松模式"能跑" |
| 坑 3 | 窗口函数不会写 | 分组不降行，Top N/排名/环比全靠它 | OVER(PARTITION BY ORDER BY)、ROW_NUMBER/RANK/DENSE_RANK |
| 坑 4 | LEFT JOIN 误用 | 右表过滤写 WHERE 会退化成 INNER | 右表条件放 ON、先聚合再 JOIN 防膨胀 |
| 坑 5 | 子查询与 JOIN 说不清 | 要列用 JOIN、判存在用 IN/EXISTS，别死记性能 | 优化器会改写、IN 遇 NULL 归零 |
| 坑 6 | 索引概念缺失 | 索引=目录，空间换时间，建在过滤/连接列上 | WHERE/JOIN/ORDER BY 列、EXPLAIN 验证、覆盖索引 |
| 坑 7 | 慢查询分析答错 | 索引列套函数=索引失效，SELECT \* 增 IO | 列保持裸列、范围代替函数、EXPLAIN 三板斧 |
| 坑 8 | 深分页写法错 | OFFSET 越大越慢（扫过再扔） | 浅分页 LIMIT/OFFSET、深分页 Keyset 游标 |
| 坑 9 | 去重 COUNT 口径错 | 用户数永远是 COUNT(DISTINCT) | COUNT(\*)数行、COUNT(列)跳NULL、DISTINCT 去重 |
| 坑 10 | SQL 与 pandas 对照说不清 | groupby/transform/shift/merge 一一对应 | 分组聚合↔groupby、窗口↔transform/shift、JOIN↔merge |
| 坑 11 | NULL 三值逻辑坑 | NULL=NULL 是 UNKNOWN，NOT IN 遇 NULL 归零 | IS NULL、NOT IN 前滤 NULL、聚合跳过 NULL |
练习题
题目 1：需求是"统计每个城市已支付订单的平均金额，只保留平均金额大于 100 的城市"。请写出完整 SQL，并说明 WHERE 和 HAVING 各放什么、为什么。
参考答案要点：WHERE status='paid' 放行级过滤（聚合前缩小数据），GROUP BY city，HAVING AVG(amount)>100 放组级过滤；不能在 WHERE 里用别名或聚合；行过滤尽量提前到 WHERE 以减少进组行数。
题目 2：一张订单表，要"取每个城市金额最高的前 2 笔订单"，且要求并列时保留并列行。请写出 SQL。
参考答案要点：用窗口函数 DENSE_RANK() OVER (PARTITION BY city ORDER BY amount DESC)，外层 WHERE rn <= 2；强调用 DENSE_RANK 而非 ROW_NUMBER 才能保留并列；窗口函数别名要包一层子查询才能在 WHERE 引用。
题目 3：一条 SQL 运行很慢，SELECT * FROM orders WHERE status='paid' AND DATE(create_time)='2026-10-01'，create_time 上明明建了索引却没走。请解释原因并改写。
参考答案要点：对索引列 create_time 套了 DATE() 函数导致索引失效；改写为范围条件 create_time >= '2026-10-01' AND create_time < '2026-10-02'，列保持裸列；同时把 SELECT * 改为只取需要的列；用 EXPLAIN 确认走到索引。
题目 4：报表要统计"下单用户数"。表里有 8 行订单，同一用户多单，且有 1 行 user_id 为 NULL。应该用 COUNT(*)、COUNT(user_id) 还是 COUNT(DISTINCT user_id)？写出正确写法并解释口径。
参考答案要点：用 COUNT(DISTINCT user_id)——COUNT(*) 得 8（订单数），COUNT(user_id) 会跳过 NULL 但不去重；DISTINCT 自动去重且忽略 NULL，得到真实下单用户数；并说明这是"去重口径"，和业务方"付费用户数"对齐。
下一篇预告：第 65 章 面试踩坑：业务数据分析题篇 —— SQL 取数只是入场券，真正拉开差距的是"给你一个业务异动，你怎么拆、怎么归因、怎么给建议"：把开放业务题答成结构化高分答案。
文章回复
0 条公开回复