第 36 章 高频手写SQL真题精讲

📖 本篇简介：手写 SQL 是面试硬功夫——给你表结构，当场写出查询。本篇精选 20 道公开高频手写题，覆盖第 N 高薪水、连续登录、并列排名、删除重复、分数排名、部门 Top3、首日留存、行转列、LAG 环比等。每题给出建表、样例数据和带中文注释的可运行 SQL。所有 SQL 按 MySQL 8.0 语法书写；前置知识：第 9、10、11、12 章。

目录

• 1 环境与公共建表
• 题目 1：查询第N高的薪水
• 题目 2：删除重复邮箱保留最小id
• 题目 3：部门工资最高的员工
• 题目 4：分数排名并列名次
• 题目 5：超过5名学生的课
• 题目 6：上升的温度
• 题目 7：组合两个表左连接
• 题目 8：部门前N高工资
• 题目 9：树节点类型判断
• 题目 10：连续登录N天的用户
• 题目 11：大的国家
• 题目 12：兑换席位相邻互换
• 题目 13：各部门薪水第二高
• 题目 14：取消率统计
• 题目 15：首日留存率
• 题目 16：行转列每月交易透视
• 题目 17：报告的条件状态聚合
• 题目 18：每位学生各科考试次数
• 题目 19：行程人数环比变化LAG
• 题目 20：查找重复的电子邮箱
• 速查速记
• 常见坑与注意事项
• 复习清单
• 练习题

1 环境与公共建表

本示例基于 MySQL 8.0。窗口函数（ROW_NUMBER/RANK/LAG 等）是 8.0 重点考察对象。下面统一建两张常用表，后续题目直接引用。

-- 员工表：本示例基于 MySQL 8.0
CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(20),
  salary INT,
  departmentId INT
);
-- 部门表
CREATE TABLE department (
  id INT PRIMARY KEY,
  name VARCHAR(20)
);
-- 分数表
CREATE TABLE score (
  student VARCHAR(20),
  subject VARCHAR(20),
  score INT
);

题目 1：查询第N高的薪水

考点

• 去重；ORDER BY + LIMIT；窗口函数。

参考答案

1. 去重后排序：DISTINCT salary 再排序；

2. LIMIT 1 OFFSET N-1：取第 N 个；

3. 不足 N 个返回 NULL：用封装或左连接兜底；

4. 窗口写法：DENSE_RANK() 也可。

-- 第 N=2 高的薪水（去重后），本示例基于 MySQL 8.0
SELECT DISTINCT salary AS SecondHighestSalary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;  -- OFFSET=1 跳过第1名，取第2名
-- 若不足2个，上面返回空集；要返回NULL可包一层：
-- SELECT (SELECT DISTINCT salary ... LIMIT 1 OFFSET 1) AS SecondHighestSalary;

追问

• 问：N 很大怎么办？答：用 DENSE_RANK() 或子查询去重更清晰。
• 问：并列薪水算第几？答：题目要"第 N 高"通常指去重后第几档。

题目 2：删除重复邮箱保留最小id

考点

• 自连接删除；GROUP BY。

参考答案

1. 找出每组最小 id：保留，其余删；

2. 自连接：a.email=b.email AND a.id>b.id 删 a；

3. 注意：删除时别把保留行也删了。

-- 删除重复邮箱，保留最小id，本示例基于 MySQL 8.0
DELETE a
FROM person a
JOIN person b ON a.email = b.email AND a.id > b.id;  -- a的id更大，删除a

追问

• 问：为什么是 a.id>b.id？答：每组里 id 小的保留，id 大的删。
• 问：大数据量慢？答：分批删，或建临时表保留最小 id 再重建。

题目 3：部门工资最高的员工

考点

• 分组最大值；JOIN。

参考答案

1. 各部门最高工资：GROUP BY departmentId MAX(salary)；

2. 回连员工表：找出拿最高工资的人；

3. 可能并列：同部门多人同最高都要列出。

-- 部门工资最高的员工，本示例基于 MySQL 8.0
SELECT d.name AS Department, e.name AS Employee, e.salary
FROM employee e
JOIN department d ON e.departmentId = d.id
WHERE (e.departmentId, e.salary) IN (
  SELECT departmentId, MAX(salary) FROM employee GROUP BY departmentId
);

追问

• 问：为什么用元组 IN？答：按"部门+最高薪"组合匹配，并列的人都能查出。
• 问：窗口函数写法？答：RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC)=1。

题目 4：分数排名并列名次

考点

• RANK vs DENSE_RANK vs ROW_NUMBER。

参考答案

1. 并列同名次：用 DENSE_RANK()（连续名次）；

2. 跳名次：用 RANK()（并列后跳号）；

3. 不并列：ROW_NUMBER()。

-- 分数排名，并列同名次且连续，本示例基于 MySQL 8.0
SELECT score,
       DENSE_RANK() OVER (ORDER BY score DESC) AS `rank`
FROM score;
-- 90,90,80 => 1,1,2（DENSE_RANK连续）；用RANK则是1,1,3（跳号）

追问

• 问：三个排名函数区别？答：ROW_NUMBER 不并列；RANK 并列跳号；DENSE_RANK 并列连续。
• 问：rank 是保留字？答：加反引号。

题目 5：超过5名学生的课

考点

• GROUP BY + HAVING。

参考答案

1. 按课程分组 count；

2. HAVING 过滤 count>5；

3. 注意去重学生：同一学生一课多次签到要 COUNT(DISTINCT student)。

-- 选了超过5名学生的课，本示例基于 MySQL 8.0
SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;  -- 去重学生，防重复签到

追问

• 问：COUNT 不加 DISTINCT 行不？答：同一学生多行签到会被多算，必须去重。
• 问：HAVING 能用 WHERE 替？答：不行，聚合条件只能 HAVING。

题目 6：上升的温度

考点

• 自连接；日期比较；DATEDIFF。

参考答案

1. 自己跟自己比：今天比昨天温度高；

2. 日期相邻：DATEDIFF(a.recordDate, b.recordDate)=1；

3. 温度条件：a.temperature > b.temperature。

-- 找比前一天温度高的日期id，本示例基于 MySQL 8.0
SELECT a.id
FROM weather a
JOIN weather b
  ON DATEDIFF(a.recordDate, b.recordDate) = 1  -- a比b晚一天
 AND a.temperature > b.temperature;             -- 且温度更高

追问

• 问：为什么不用 DATE_ADD？答：DATEDIFF 更直观表达"相邻一天"。
• 问：日期类型要注意？答：确保是 DATE/DATETIME，别用字符串比。

题目 7：组合两个表左连接

考点

• LEFT JOIN；空值处理。

参考答案

1. 不管有没有订单都要列出人：用左连接；

2. 没人对应字段为 NULL；

3. 注意 ON 条件 vs WHERE。

-- 列出每个人及其订单，没订单也保留，本示例基于 MySQL 8.0
SELECT p.name, o.order_no, o.amount
FROM person p
LEFT JOIN orders o ON p.id = o.person_id;  -- 左连接，无订单者字段为NULL

追问

• 问：WHERE 里写右表条件会怎样？答：会把左连接退化成内连接，无订单行被滤掉。
• 问：NULL 怎么显示？答：COALESCE(o.amount,0)。

题目 8：部门前N高工资

考点

• 分区排名；并列处理。

参考答案

1. 按部门分区排名：PARTITION BY departmentId；

2. 取前 N：DENSE_RANK()<=N；

3. 并列都要：用 DENSE_RANK。

-- 每个部门工资前3高（并列都算），本示例基于 MySQL 8.0
SELECT Department, Employee, Salary
FROM (
  SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary,
         DENSE_RANK() OVER (PARTITION BY e.departmentId ORDER BY e.salary DESC) AS rk
  FROM employee e JOIN department d ON e.departmentId = d.id
) t
WHERE rk <= 3;  -- 取每个部门前3档

追问

• 问：用 ROW_NUMBER 行不行？答：并列会被随机排掉，题目要并列就用 DENSE_RANK。
• 问：性能？答：窗口函数要排序，数据量大注意索引。

题目 9：树节点类型判断

考点

• 分类 CASE；自连接。

参考答案

1. 根节点：p_id IS NULL；

2. 叶子节点：不是根，且没人以它为父；

3. 内部节点：非根且有子节点。

-- 判断树节点类型，本示例基于 MySQL 8.0
SELECT id,
  CASE
    WHEN p_id IS NULL THEN 'Root'                       -- 根
    WHEN id IN (SELECT p_id FROM tree WHERE p_id IS NOT NULL) THEN 'Inner'  -- 有孩子
    ELSE 'Leaf'                                         -- 叶子
  END AS Type
FROM tree;

追问

• 问：能不能一次 GROUP BY？答：分类判断更适合 CASE。
• 问：空树？答：正常处理空集。

题目 10：连续登录N天的用户

考点

• 日期差分技巧；ROW_NUMBER。

参考答案

1. 经典技巧：登录日期减去一个"行号"得到分组标志；

2. 按用户 + 分组标志 count：>=N 即连续 N 天；

3. 去重日期：同一用户一天多登先 DISTINCT。

-- 连续登录>=3天的用户，本示例基于 MySQL 8.0
SELECT user_id 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 login) d
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;  -- 同一基准日下有3天=连续登录3天

追问

• 问：为什么减行号能分组？答：连续日期每天减递增的 1,2,3…，结果都相同，形成一组。
• 问：N=2 同理？答：改 HAVING COUNT(*)>=2。

题目 11：大的国家

考点

• 多条件 OR；筛选。

参考答案

1. 定义：面积大 OR 人口多；

2. 筛字段：name、population、area；

3. OR 条件。

-- 面积>300万或人口>2500万的国家，本示例基于 MySQL 8.0
SELECT name, population, area
FROM world
WHERE area > 3000000 OR population > 25000000;

追问

• 问：OR 能用 UNION？答：可以，但这里 OR 更简单。
• 问：字段类型？答：数值比较，别用字符串。

题目 12：兑换席位相邻互换

考点

• 自连接；奇偶相邻。

参考答案

1. 座位按 id 排好；

2. 相邻奇偶互换：id 奇数与后一个换，偶数与前一个换；

3. 用 LAG/LEAD 或自连接。

-- 相邻座位互换，本示例基于 MySQL 8.0
SELECT
  CASE
    WHEN id % 2 = 1 AND id + 1 IN (SELECT id FROM seat) THEN id + 1  -- 奇数换下一个
    WHEN id % 2 = 0 THEN id - 1                                      -- 偶数换前一个
    ELSE id                                                          -- 最后一个奇数不变
  END AS new_id, student
FROM seat
ORDER BY new_id;

追问

• 问：最后一个没伴怎么办？答：ELSE id 保持原位。
• 问：窗口写法？答：LEAD/LAG 取相邻。

题目 13：各部门薪水第二高

考点

• 分区取第二；NULL 兜底。

参考答案

1. 按部门分区排名；

2. 取 rk=2；没有第二高的部门要显示 NULL；

3. 所有部门都要列出：从部门表出发。

-- 各部门第二高薪水，没有则NULL，本示例基于 MySQL 8.0
SELECT d.name AS Department,
       (SELECT e.salary FROM employee e
        WHERE e.departmentId = d.id
        ORDER BY e.salary DESC LIMIT 1 OFFSET 1) AS SecondHighestSalary
FROM department d;  -- 子查询无结果时返回NULL

追问

• 问：并列第二怎么办？答：按题意去重；要并列用 DENSE_RANK。
• 问：相关子查询性能？答：部门多时可改窗口函数一次算完。

题目 14：取消率统计

考点

• 条件聚合；比率计算。

参考答案

1. 筛选有效订单；

2. SUM(CASE WHEN 取消) 算取消数；

3. 取消率 = 取消/总数，ROUND 保留两位。

-- 每天订单取消率，本示例基于 MySQL 8.0
SELECT day,
       ROUND(
         SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)
         / COUNT(*), 2) AS cancel_rate
FROM orders
GROUP BY day;

追问

• 问：COUNT(*) 能换 COUNT(1)？答：等价。
• 问：分母为 0？答：数据上保证当天有单，或 NULLIF 防除零。

题目 15：首日留存率

考点

• 自连接；日期差分；比率。

参考答案

1. 首日：每个用户最早登录日；

2. 次日留存：首日第二天也登录；

3. 留存率 = 留存数/新增数。

-- 次日留存率，本示例基于 MySQL 8.0
SELECT
  ROUND(
    COUNT(DISTINCT b.user_id) / COUNT(DISTINCT a.user_id), 2) AS retention
FROM (
  SELECT user_id, MIN(login_date) AS first_day FROM login GROUP BY user_id
) a
LEFT JOIN login b
  ON a.user_id = b.user_id
 AND b.login_date = DATE_ADD(a.first_day, INTERVAL 1 DAY);  -- 次日也登录

追问

• 问：为什么 LEFT JOIN？答：没留存的用户也要计入分母。
• 问：要按天出留存曲线？答：按 first_day 分组 GROUP BY。

题目 16：行转列每月交易透视

考点

• CASE 聚合行转列。

参考答案

1. 按月份分组；

2. SUM(CASE type WHEN 'approved') 转列；

3. 行转列：每种状态一列。

-- 每月每种状态的金额汇总（行转列），本示例基于 MySQL 8.0
SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month, country,
  SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total,
  SUM(CASE WHEN state = 'declined'  THEN amount ELSE 0 END) AS declined_total
FROM transactions
GROUP BY month, country;

追问

• 问：行转列核心？答：CASE WHEN 把行条件变成列，再聚合。
• 问：MySQL 有没有 pivot？答：没有专门 PIVOT，用 CASE 模拟。

题目 17：报告的条件状态聚合

考点

• 多条件分组；筛选。

参考答案

1. 按日期分组；

2. 条件 sum：成功/失败分别统计；

3. 过滤：可加 HAVING。

-- 每天成功/失败的请求数，本示例基于 MySQL 8.0
SELECT event_day AS day,
  SUM(CASE WHEN success = 1 THEN 1 ELSE 0 END) AS ok_cnt,
  SUM(CASE WHEN success = 0 THEN 1 ELSE 0 END) AS fail_cnt
FROM request_log
GROUP BY event_day;

追问

• 问：COUNT(IF(...))？答：MySQL 里 SUM(cond) 更通用。
• 问：多维度？答：GROUP BY 加维度列。

题目 18：每位学生各科考试次数

考点

• 主表 LEFT JOIN；补零。

参考答案

1. 学生表 × 科目表：笛卡尔基表；

2. 左连考试成绩：没考的科目显示 0；

3. COUNT 成绩列：NULL 不计。

-- 每位学生参加每科的次数（没考为0），本示例基于 MySQL 8.0
SELECT s.student_id, s.student_name, sub.subject_name,
       COUNT(e.subject_name) AS attended_exams  -- NULL不计
FROM students s
CROSS JOIN subjects sub                       -- 所有学生×所有科目
LEFT JOIN examinations e
  ON s.student_id = e.student_id AND sub.subject_name = e.subject_name
GROUP BY s.student_id, sub.subject_name
ORDER BY s.student_id, sub.subject_name;

追问

• 问：为什么 CROSS JOIN？答：保证每个学生每科都出一行，哪怕没考。
• 问：COUNT(*) vs COUNT(列)？答：COUNT(列) 不计 NULL，正好表达"没考为0"。

题目 19：行程人数环比变化LAG

考点

• LAG 窗口函数；环比。

参考答案

1. 按天聚合人数；

2. LAG 取前一天；

3. 相减得变化。

-- 每天乘车人数及相对前一天的变化，本示例基于 MySQL 8.0
SELECT day, ppl,
       ppl - LAG(ppl, 1) OVER (ORDER BY day) AS diff  -- 与前一天相减
FROM (SELECT day, COUNT(*) AS ppl FROM trips GROUP BY day) t;

追问

• 问：LAG 取不到前一行？答：第一天为 NULL，可用 COALESCE 给默认。
• 问：环比百分比？答：(ppl-LAG)/LAG。

题目 20：查找重复的电子邮箱

考点

• GROUP BY + HAVING。

参考答案

1. 按邮箱分组；

2. HAVING COUNT>1；

3. 列出重复邮箱。

-- 查找重复邮箱，本示例基于 MySQL 8.0
SELECT email
FROM person
GROUP BY email
HAVING COUNT(*) > 1;

追问

• 问：要列出人？答：再 JOIN 回去取明细。
• 问：性能？答：email 建索引，分组走索引。

速查速记

| 题型 | 核心套路 | 关键字 |

|---|---|---|

| 第N高 | 去重排序OFFSET | DISTINCT+LIMIT |

| 并列排名 | 窗口函数 | DENSE_RANK |

| 删重复 | 自连接保留最小 | a.id>b.id |

| 部门TopN | 分区排名 | PARTITION BY |

| 连续登录 | 日期-行号分组 | ROW_NUMBER |

| 首日留存 | 首日+次日自连 | DATE_ADD |

| 行转列 | CASE聚合 | SUM(CASE) |

| 环比 | 取前一行 | LAG |

常见坑与注意事项

• 坑 1：排名函数混用——并列题用 ROW_NUMBER 会把并列拆成不同名次，要分清 RANK/DENSE_RANK；
• 坑 2：GROUP BY 后用 WHERE 过滤聚合——聚合条件必须写 HAVING；
• 坑 3：LEFT JOIN 右表条件写 WHERE——会退化成内连接，右表条件写 ON；
• 坑 4：删除重复时自连接写错方向——保留最小 id 要删 id 大的；
• 坑 5：除零——比率分母可能为 0，用 NULLIF 保护；
• 注意：所有 SQL 按 MySQL 8.0 语法，rank 等保留字加反引号。

复习清单

| 题目 | 套路 | 易错点 |

|---|---|---|

| 第N高 | DISTINCT+OFFSET | 空结果要NULL |

| 删重复 | 自连接a.id>b.id | 方向别反 |

| 最高工资 | 元组IN | 并列要列出 |

| 排名 | DENSE_RANK | 保留字反引号 |

| 连续登录 | 日期-行号 | 先DISTINCT日期 |

| 留存 | 首日+次日自连 | LEFT JOIN保分母 |

| 行转列 | CASE聚合 | MySQL无PIVOT |

| 环比 | LAG | 首日NULL |

练习题

练 1：写 SQL 查询每个部门工资第二高的薪水（并列算同一档，没有则显示 NULL）。

<details>

<summary>参考答案</summary>

SELECT Department, MAX(Salary) AS SecondHighestSalary
FROM (
  SELECT d.name AS Department, e.salary AS Salary,
         DENSE_RANK() OVER (PARTITION BY e.departmentId ORDER BY e.salary DESC) AS rk
  FROM employee e JOIN department d ON e.departmentId = d.id
) t
WHERE rk = 2
GROUP BY Department;

DENSE_RANK() 把并列薪水算同一档；WHERE rk=2 取第二档；没有第二高的部门自然不出现，如需 NULL 可左连部门表补。

</details>

练 2：写 SQL 找出连续 3 天下单的用户。

<details>

<summary>参考答案</summary>

SELECT user_id FROM (
  SELECT user_id, order_date,
         DATE_SUB(order_date, INTERVAL ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY order_date) DAY) AS grp
  FROM (SELECT DISTINCT user_id, order_date FROM orders) d
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

连续日期减去递增行号得到相同基准日（grp），同一 grp 下有 3 天即连续下单 3 天。

</details>

下一篇预告：第 37 章 高频原理简答真题精讲