SQL 50 题单手册

怎么用这份手册

1. 先读章节导语，知道这一章要解决什么问题，再开始做题。
2. 每题先看"一句话题面"和"考点"，自己在 LeetCode 上写 15 分钟。写不出来再看手册的"思路拆解"，仍然不要直接抄答案。
3. AC 之后必须做两件事：把手册里的写法敲一遍（不要复制粘贴）；把"易错点"对照你的代码检查一遍。
4. 窗口函数一章（第 5 章）是分水岭。外企 SQL 面试几乎必考 ROW_NUMBER / RANK / SUM OVER，简单题刷得再快也不如这一章吃透。
5. 英文表达逐字读出声。面试里 "I'd start by…" 这类句式是肌肉记忆，不是知识。
6. 进度：点每张卡片右上"已 AC"即可记录，浏览器本地保存，打印时自动隐藏。

五周刷题节奏

合计 50 题。难度分布：简单 32 · 中等 17 · 困难 1（185 部门工资前三高）。窗口函数一章可以提前并行做，因为它反过来能简化第 3、4 章的多道题。

第 1 章 · 过滤与条件判断12 题

本章题号：1757 · 595 · 584 · 1148 · 1683 · 620 · 1667 · 1527 · 1517 · 610 · 1907 · 1789

SELECT product_id
FROM Products
WHERE low_fats = 'Y' AND recyclable = 'Y';

SELECT name, population, area
FROM World
WHERE area >= 3000000 OR population >= 25000000;

SELECT name
FROM Customer
WHERE referee_id IS NULL
   OR referee_id <> 2;

SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id;

SELECT tweet_id
FROM Tweets
WHERE CHAR_LENGTH(content) > 15;

SELECT id, movie, description, rating
FROM cinema
WHERE description <> 'boring'
  AND id % 2 = 1
ORDER BY rating DESC;

SELECT user_id,
       CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))) AS name
FROM Users
ORDER BY user_id;

SELECT patient_id, patient_name, conditions
FROM Patients
WHERE conditions LIKE 'DIAB1%'
   OR conditions LIKE '% DIAB1%';

SELECT *
FROM Users
WHERE mail REGEXP '^[A-Za-z][A-Za-z0-9._-]*@leetcode\\.com$';

SELECT x, y, z,
       IF(x + y > z AND x + z > y AND y + z > x, 'Yes', 'No') AS triangle
FROM Triangle;

SELECT 'Low Salary' AS category, COUNT(*) AS accounts_count
FROM Accounts WHERE income < 20000
UNION ALL
SELECT 'Average Salary', COUNT(*)
FROM Accounts WHERE income BETWEEN 20000 AND 50000
UNION ALL
SELECT 'High Salary', COUNT(*)
FROM Accounts WHERE income > 50000;

SELECT employee_id, department_id
FROM Employee
WHERE primary_flag = 'Y'
UNION
SELECT employee_id, department_id
FROM Employee
GROUP BY employee_id
HAVING COUNT(*) = 1;

第 2 章 · 多表连接11 题

本章题号：1378 · 1068 · 577 · 1581 · 1661 · 197 · 1280 · 570 · 1934 · 1731 · 1978

SELECT u.unique_id, e.name
FROM Employees e
LEFT JOIN EmployeeUNI u ON u.id = e.id;

SELECT p.product_name, s.year, s.price
FROM Sales s
JOIN Product p ON p.product_id = s.product_id;

SELECT e.name, b.bonus
FROM Employee e
LEFT JOIN Bonus b ON b.empId = e.empId
WHERE b.bonus IS NULL OR b.bonus < 1000;

SELECT v.customer_id, COUNT(v.visit_id) AS count_no_trans
FROM Visits v
LEFT JOIN Transactions t ON t.visit_id = v.visit_id
WHERE t.visit_id IS NULL
GROUP BY v.customer_id;

SELECT a.machine_id,
       ROUND(AVG(b.timestamp - a.timestamp), 3) AS processing_time
FROM Activity a
JOIN Activity b
  ON b.machine_id = a.machine_id AND b.process_id = a.process_id
WHERE a.activity_type = 'start' AND b.activity_type = 'end'
GROUP BY a.machine_id;

SELECT a.id
FROM Weather a
JOIN Weather b ON DATEDIFF(a.recordDate, b.recordDate) = 1
WHERE a.temperature > b.temperature;

SELECT s.student_id, s.student_name, sub.subject_name,
       COUNT(e.student_id) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN Examinations e
  ON e.student_id = s.student_id AND e.subject_name = sub.subject_name
GROUP BY s.student_id, s.student_name, sub.subject_name
ORDER BY s.student_id, sub.subject_name;

SELECT m.name
FROM Employee m
JOIN Employee e ON e.managerId = m.id
GROUP BY m.id, m.name
HAVING COUNT(e.id) >= 5;

SELECT s.user_id,
       ROUND(SUM(IF(c.action = 'confirmed', 1, 0)) / COUNT(*), 2) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON c.user_id = s.user_id
GROUP BY s.user_id;

SELECT m.employee_id, m.name,
       COUNT(*) AS reports_count,
       ROUND(AVG(r.age)) AS average_age
FROM Employees m
JOIN Employees r ON r.reports_to = m.employee_id
GROUP BY m.employee_id, m.name
ORDER BY m.employee_id;

SELECT e.employee_id
FROM Employees e
LEFT JOIN Employees m ON m.employee_id = e.manager_id
WHERE e.salary < 30000
  AND e.manager_id IS NOT NULL
  AND m.employee_id IS NULL
ORDER BY e.employee_id;

第 3 章 · 分组、聚合与拼接12 题

本章题号：1251 · 1075 · 1633 · 1211 · 1193 · 2356 · 1141 · 596 · 1729 · 619 · 1484 · 1327

SELECT p.product_id,
       COALESCE(ROUND(SUM(p.price * u.units) / SUM(u.units), 2), 0) AS average_price
FROM Prices p
LEFT JOIN UnitsSold u
  ON  p.product_id = u.product_id
  AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id;

SELECT pr.project_id,
       ROUND(AVG(e.experience_years), 2) AS average_years
FROM Project pr
JOIN Employee e ON e.employee_id = pr.employee_id
GROUP BY pr.project_id;

SELECT contest_id,
       ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM Users), 2) AS percentage
FROM Register
GROUP BY contest_id
ORDER BY percentage DESC, contest_id;

SELECT query_name,
       ROUND(AVG(rating / position), 2) AS quality,
       ROUND(AVG(rating < 3) * 100, 2) AS poor_query_percentage
FROM Queries
GROUP BY query_name;

SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month,
       country,
       COUNT(*)                                        AS trans_count,
       SUM(state = 'approved')                         AS approved_count,
       SUM(amount)                                     AS trans_total_amount,
       SUM(IF(state = 'approved', amount, 0))          AS approved_total_amount
FROM Transactions
GROUP BY month, country;

SELECT teacher_id, COUNT(DISTINCT subject_id) AS cnt
FROM Teacher
GROUP BY teacher_id;

SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users
FROM Activity
WHERE activity_date BETWEEN '2019-06-28' AND '2019-07-27'
GROUP BY activity_date;

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;

SELECT user_id, COUNT(follower_id) AS followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id;

SELECT MAX(num) AS num
FROM (
  SELECT num FROM MyNumbers GROUP BY num HAVING COUNT(*) = 1
) t;

SELECT sell_date,
       COUNT(DISTINCT product) AS num_sold,
       GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date;

SELECT p.product_name, SUM(o.unit) AS unit
FROM Products p
JOIN Orders o ON o.product_id = p.product_id
WHERE o.order_date >= '2020-02-01'
  AND o.order_date <  '2020-03-01'
GROUP BY p.product_id, p.product_name
HAVING SUM(o.unit) >= 100;

第 4 章 · 子查询、集合运算与改数据9 题

本章题号：176 · 1070 · 1045 · 626 · 180 · 1341 · 602 · 585 · 196

SELECT (
  SELECT DISTINCT salary
  FROM Employee
  ORDER BY salary DESC
  LIMIT 1 OFFSET 1
) AS SecondHighestSalary;

SELECT product_id, year AS first_year, quantity, price
FROM Sales
WHERE (product_id, year) IN (
  SELECT product_id, MIN(year) FROM Sales GROUP BY product_id
);

SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(DISTINCT product_key) FROM Product);

UPDATE Seat s
LEFT JOIN Seat t
  ON t.id = IF(s.id % 2 = 1, s.id + 1, s.id - 1)
SET s.student = COALESCE(t.student, s.student);

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM Logs l1
JOIN Logs l2 ON l2.id = l1.id + 1 AND l2.num = l1.num
JOIN Logs l3 ON l3.id = l1.id + 2 AND l3.num = l1.num;

(SELECT u.name AS results
 FROM MovieRating mr JOIN Users u USING (user_id)
 GROUP BY u.user_id, u.name
 ORDER BY COUNT(*) DESC, u.name
 LIMIT 1)
UNION ALL
(SELECT m.title
 FROM MovieRating mr JOIN Movies m USING (movie_id)
 WHERE mr.created_at BETWEEN '2020-02-01' AND '2020-02-29'
 GROUP BY m.movie_id, m.title
 ORDER BY AVG(mr.rating) DESC, m.title
 LIMIT 1);

SELECT id, COUNT(*) AS num
FROM (
  SELECT requester_id AS id FROM RequestAccepted
  UNION ALL
  SELECT accepter_id FROM RequestAccepted
) t
GROUP BY id
ORDER BY num DESC
LIMIT 1;

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM Insurance
WHERE tiv_2015 IN (
  SELECT tiv_2015 FROM Insurance GROUP BY tiv_2015 HAVING COUNT(*) > 1)
  AND (lat, lon) IN (
  SELECT lat, lon FROM Insurance GROUP BY lat, lon HAVING COUNT(*) = 1);

DELETE p1
FROM Person p1
JOIN Person p2 ON p1.email = p2.email AND p1.id > p2.id;

第 5 章 · 窗口函数6 题

本章题号：185 · 1164 · 1204 · 1321 · 550 · 1174

SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary
FROM (
  SELECT name, salary, departmentId,
         DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS dr
  FROM Employee
) e
JOIN Department d ON d.id = e.departmentId
WHERE e.dr <= 3;

SELECT product_id, new_price AS price
FROM (
  SELECT product_id, new_price,
         ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS rn
  FROM Products
  WHERE change_date <= '2019-08-16'
) t
WHERE rn = 1
UNION
SELECT product_id, 10 AS price
FROM Products
GROUP BY product_id
HAVING MIN(change_date) > '2019-08-16';

SELECT person_name
FROM (
  SELECT person_name,
         SUM(weight) OVER (ORDER BY turn
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total
  FROM Queue
) t
WHERE total <= 1000
ORDER BY total DESC
LIMIT 1;

SELECT visited_on, amount, ROUND(amount / 7, 2) AS average_amount
FROM (
  SELECT visited_on,
         SUM(SUM(amount)) OVER (ORDER BY visited_on
                                ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount
  FROM Customer
  GROUP BY visited_on
) t
WHERE DATEDIFF(visited_on, (SELECT MIN(visited_on) FROM Customer)) >= 6
ORDER BY visited_on;

SELECT ROUND(SUM(IF(nd = d + INTERVAL 1 DAY, 1, 0)) / COUNT(*), 2) AS fraction
FROM (
  SELECT event_date AS d,
         MIN(event_date)  OVER (PARTITION BY player_id) AS ins,
         LEAD(event_date) OVER (PARTITION BY player_id ORDER BY event_date) AS nd
  FROM Activity
) t
WHERE d = ins;

SELECT ROUND(AVG(order_date = customer_pref_delivery_date) * 100, 2) AS immediate_percentage
FROM (
  SELECT order_date, customer_pref_delivery_date,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY order_date, delivery_id) AS rn
  FROM Delivery
) t
WHERE rn = 1;

附录 A · MySQL 8 语法速查

A1. SELECT 的真实执行顺序（背下来，能解释 80% 的报错）

书写顺序不等于执行顺序。MySQL 按下面的顺序处理，这解释了为什么 WHERE 里不能用 SELECT 的别名、GROUP BY 之后才能用 HAVING、以及 ORDER BY 里为什么可以用别名。

-- 执行顺序（不是书写顺序）
FROM / JOIN / ON     -- 1. 先确定来源表并连接
WHERE                -- 2. 在连接结果上逐行过滤（此时还没有聚合）
GROUP BY             -- 3. 分组
HAVING               -- 4. 对分组结果过滤
-- 5. SELECT 计算列、别名在这里才生效
DISTINCT             -- 6. 去重
ORDER BY             -- 7. 排序（可以用 SELECT 里的别名）
LIMIT / OFFSET       -- 8. 最后才截断

A2. 连接写法

SELECT a.id, b.name
FROM   a
INNER  JOIN b ON b.id = a.bid      -- 只保留两边都有的
LEFT   JOIN b ON b.id = a.bid      -- 左表全保留，右表缺失补 NULL
RIGHT  JOIN b ON b.id = a.bid      -- 与 LEFT 镜像（少用，改个顺序更清楚）
CROSS  JOIN b                      -- 笛卡尔积，用来造日期序列 / 全组合
LEFT   JOIN b ON ... WHERE b.id IS NULL   -- 反连接：只要 a 中没有匹配 b 的行

A3. 条件与空值

CASE WHEN score >= 90 THEN 'A'
     WHEN score >= 60 THEN 'B'
     ELSE 'C' END AS grade          -- 没有 ELSE 时不匹配会返回 NULL

IF(x > 0, 'yes', 'no')              -- MySQL 专有；PostgreSQL 用 CASE
IFNULL(a, 0)                        -- MySQL 专有；通用写法 COALESCE(a, 0)
NULLIF(a, 0)                        -- a 等于 0 时返回 NULL，专门用来防除零
COALESCE(a, b, c)                   -- 返回第一个非 NULL
WHERE col IS NULL                   -- 永远不要用 = NULL
WHERE col NOT IN (subquery)         -- 子查询结果含 NULL 时整个条件恒为假！
WHERE NOT (col IN (subquery))       -- 更安全：配合 IS NULL 判断，或改用 NOT EXISTS
ROUND(x, 2)                         -- 保留两位
CAST(x AS DECIMAL(10,2))            -- 或 x * 1.0，避免整数除法被截断

A4. 聚合与分组

SELECT user_id,
       COUNT(*)             AS rows_all,      -- 数所有行，含 NULL
       COUNT(reporter_id)   AS rows_nonnull,  -- 只数非 NULL 的
       COUNT(DISTINCT id)   AS uniq
FROM   tasks
GROUP  BY user_id
HAVING COUNT(*) > 1        -- HAVING 只能用聚合函数或分组列
ORDER  BY rows_all DESC, user_id ASC
LIMIT  5 OFFSET 10;
-- MySQL 允许 GROUP BY / ORDER BY user_id, 2 用序号
-- ONLY_FULL_GROUP_BY 模式下，SELECT 里的非聚合列必须出现在 GROUP BY 中

A5. 窗口函数骨架（面试必考）

SELECT name, dept, salary,
       ROW_NUMBER()  OVER w AS rn,   -- 1,2,3,4  绝不并列
       RANK()        OVER w AS rk,   -- 1,2,2,4  并列后跳号
       DENSE_RANK()  OVER w AS drk,  -- 1,2,2,3  并列不跳号
       PERCENT_RANK()OVER w AS pr,
       LAG(salary, 1, 0)  OVER (PARTITION BY dept ORDER BY pay_date)  AS prev_day,
       LEAD(salary)       OVER (PARTITION BY dept ORDER BY pay_date)  AS next_day,
       SUM(salary)   OVER (PARTITION BY dept)                          AS dept_total,
       AVG(salary)   OVER (PARTITION BY dept
                           ORDER BY pay_date
                           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)   AS moving_3
FROM   emp
WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);
-- WINDOW 子句（MySQL 8.0+）可给窗口命名，避免重复写 OVER
-- 取"每组前 N 名"：窗口函数不能直接写在 WHERE 里，必须包一层子查询或 CTE

A6. CTE 与派生表

WITH base AS (
  SELECT user_id, spend, DATE_FORMAT(created_at, '%Y-%m') AS ym FROM orders
), agg AS (
  SELECT ym, AVG(spend) AS avg_spend FROM base GROUP BY ym
)
SELECT b.user_id, b.spend, a.avg_spend
FROM   base b JOIN agg a USING (ym);

-- 派生表必须有别名：FROM (SELECT ...) t
-- 递归 CTE 造日期序列（MySQL 8.0+）：
WITH RECURSIVE d AS (
  SELECT DATE('2024-01-01') AS dt
  UNION ALL
  SELECT dt + INTERVAL 1 DAY FROM d WHERE dt < DATE('2024-01-31')
)
SELECT dt FROM d;

A7. 日期函数

DATEDIFF(d2, d1)                     -- d2 - d1 的天数（整数）；PostgreSQL 用 d2 - d1
DATE_ADD(d, INTERVAL 7 DAY)          -- 加 7 天；等价写法 d + INTERVAL 7 DAY
DATE_SUB(d, INTERVAL 1 MONTH)
SUBDATE(d, INTERVAL 1 DAY)
DATE_FORMAT(d, '%Y-%m-%d')           -- %Y 4位年 %m 月 %d 日 %H 时 %i 分 %s 秒
DATE(d) / CAST(d AS DATE)            -- 截掉时间部分
YEAR(d), MONTH(d), DAYOFWEEK(d)      -- DAYOFWEEK: 1=周日
LAST_DAY(d), DAYOFYEAR(d)
d1 = d2                              -- 日期列带时间时等值会失败，先 DATE() 再比
-- 连续 N 天 / 滑动 N 天平均的经典技巧：
--   ROW_NUMBER() OVER (ORDER BY date) 与 date 相减得到同一个连续段的分组键

A8. 字符串、并集与行列转换

CONCAT(a, '-', b) / CONCAT_WS(', ', a, b)
GROUP_CONCAT(name ORDER BY name SEPARATOR ', ')   -- 多行合成一格（PG: string_agg）
LENGTH / CHAR_LENGTH, LOWER, UPPER, TRIM, REPLACE, SUBSTRING(s, 1, 3), LOCATE('@', s)
col REGEXP '^[0-9]'            -- PG 用 ~
col LIKE 'Sci%'                -- 也能匹配 'sci...'：MySQL 默认排序规则大小写不敏感，要敏感就 LIKE BINARY 或 BINARY col

SELECT ... UNION SELECT ...    -- 去重 + 排序，慢
SELECT ... UNION ALL SELECT ...-- 不去重，快；确定无重复时用这个
-- 宽转长：每个属性一段 SELECT 再 UNION ALL；长转宽：CASE WHEN + MAX 分组透视
SELECT report_date,
       MAX(CASE WHEN type = 'finance'    THEN paid END) AS finance,
       MAX(CASE WHEN type = 'personal'   THEN paid END) AS personal,
       MAX(CASE WHEN type = 'community'  THEN paid END) AS community
FROM   department GROUP BY report_date;   -- 未出现的月份/类型补 NULL，需要 RIGHT JOIN 日历

A9. 与 PostgreSQL / SQL Server 的差异（外企技术栈常混用）

附录 B · 12 个反复踩的坑

1. = NULL 永远不成立。判空只能 IS NULL / IS NOT NULL。聚合结果为空时也是 NULL，不是 0。
2. NOT IN 子查询里只要有一个 NULL，结果就为空集。要么加 WHERE col IS NOT NULL，要么改 NOT EXISTS / LEFT JOIN … IS NULL。
3. LEFT JOIN 之后把右表条件写在 WHERE 里，会退化成 INNER JOIN。右表条件要放 ON。
4. 统计"每个用户的订单数（含 0 单）"时，COUNT(*) 会把 NULL 行算成 1。用 COUNT(o.id) 才对。
5. 整数除法会截断。3/2 = 1（MySQL 里 COUNT(a)/COUNT(b) 得到整数）。乘 1.0 或 CAST(… AS DECIMAL) 再 ROUND(…,2)。
6. WHERE 里不能用 SELECT 定义的别名（执行顺序问题）。GROUP BY/HAVING/ORDER BY 里 MySQL 容忍，PG 里 WHERE 不行 —— 通用做法是包一层 CTE。
7. ONLY_FULL_GROUP_BY 报错（error 1140/1055）：非聚合列必须进 GROUP BY，或者用窗口函数改写。别关 sql_mode，改写。
8. 窗口函数不能直接写在 WHERE / HAVING 里，必须 WITH … SELECT * FROM base WHERE rn <= 3。
9. RANK/DENSE_RANK 的区别只在并列后：1,2,2,4 还是 1,2,2,3。"前三名"若要求并列都算，用 DENSE_RANK。
10. MySQL 的字符串比较默认大小写不敏感。LIKE '%F%' 会匹到小写 f。需要区分时加 BINARY 或换排序规则。
11. 日期列带时间部分时 WHERE d = '2024-01-01' 会漏数据。先 DATE(d)，或用区间 d >= '2024-01-01' AND d < '2024-01-02'（后者能用索引）。
12. LIMIT 不带 ORDER BY 时结果不确定，"取最值之一"这类题会随机失败；先排序再截断。

附录 C · 外企面试英文口述模板

外企 SQL 面试给分点是结构化表达，不是语法完美。下面每段都能直接背。

C1. 拿到题目的前 30 秒（clarify）

面试官听到这句话基本就给分了 —— 它说明你知道 SQL 的坑在哪。

C2. 万能四步框架

C3. 按题型的关键句

C4. 写完之后的收尾话术

C5. 自我介绍一下你的 SQL 水平（60 秒）

C6. 不会写时的正确说法

比硬猜语法好得多：先说清意图，语法可以查。

附录 D · 打卡总表

下面按本手册的章节顺序列出全部 50 题，题号可直接在 LeetCode 搜索；「卡住的地方」建议只写一句话，二刷时先看这句话再动手。

建议二刷间隔 7 天。SQL 题的遗忘速度比算法题快，因为记住的是"套路"而不是"逻辑"。