高级查询与连接

高级查询是区分 SQL 初学者和专家的分水岭。窗口函数、自连接、条件分支等技术能够解决许多看似复杂的问题，将多层嵌套的子查询简化为优雅的解决方案。掌握这些技术，你将能够应对面试中绝大部分中等及以上难度的 SQL 题目。

窗口函数详解

什么是窗口函数？

窗口函数是 MySQL 8.0 引入的强大功能，它允许你在不减少行数的情况下进行聚合计算。与普通聚合函数不同，窗口函数不会将多行合并为一行，而是为每一行都返回一个结果。

窗口函数语法

函数名() OVER (
    PARTITION BY 分组列    -- 可选：将数据分区
    ORDER BY 排序列        -- 可选：在分区内排序
    frame_clause           -- 可选：定义窗口范围
)

语法拆解：

子句	说明	类比
PARTITION BY	将数据分成多个区	类似 GROUP BY
ORDER BY	在分区内排序	类似普通 ORDER BY
frame_clause	定义窗口范围	类似"前 N 行到后 M 行"

窗口函数分类

类别	函数	说明
排名函数	ROW_NUMBER(), RANK(), DENSE_RANK()	为行分配排名
偏移函数	LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()	访问其他行的值
聚合窗口	SUM(), AVG(), COUNT(), MAX(), MIN()	在窗口内聚合
分布函数	NTILE(), PERCENT_RANK(), CUME_DIST()	分布统计

排名函数详解

这是面试中最常考的窗口函数，三种排名函数的区别是重点：

函数	行为	示例（分数：100, 100, 90, 80）
ROW_NUMBER()	连续编号，不处理并列	1, 2, 3, 4
RANK()	并列相同编号，跳过后续	1, 1, 3, 4
DENSE_RANK()	并列相同编号，不跳过	1, 1, 2, 3

-- 示例：员工薪水排名
SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK() OVER (ORDER BY salary DESC) AS rank_num,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

LAG 和 LEAD 函数

这两个函数用于访问"其他行"的数据，常用于：

• 计算同比、环比
• 找出连续登录的用户
• 计算相邻行的差值

-- LAG：获取前 N 行的值
LAG(column, N, default) OVER (...)
-- column: 要获取的列
-- N: 向前偏移量（默认 1）
-- default: 没有前 N 行时的默认值

-- LEAD：获取后 N 行的值
LEAD(column, N, default) OVER (...)

示例：计算日销售额环比增长：

SELECT date, amount,
       LAG(amount, 1) OVER (ORDER BY date) AS prev_amount,
       amount - LAG(amount, 1) OVER (ORDER BY date) AS growth
FROM daily_sales;

---

1731. 每位经理的下属员工数量 (The Number of Employees Which Report to Each Employee)

题目描述

Employees 表记录了公司员工的信息：

• employee_id: 员工 ID，主键。
• name: 员工姓名。
• reports_to: 该员工汇报对象的 ID（即经理 ID）。
• age: 员工年龄。

请编写一个解决方案，查询 经理 的 ID、姓名、直接下属的人数 reports_count、以及直接下属的平均年龄 average_age（四舍五入到最近的整数）。

解题思路

这道题考查 自连接（Self Join）。

自连接的本质：

同一个表以不同的角色出现两次，通过别名区分：

分析过程：

1. 将 Employees 表视为两个表：mgr（经理视角）和 emp（员工视角）
2. 连接条件：mgr.employee_id = emp.reports_to
3. 按经理分组，统计下属数量和平均年龄

SQL 实现 (MySQL)

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

代码解析：

• FROM Employees mgr：经理视角
• JOIN Employees emp：员工视角
• ON mgr.employee_id = emp.reports_to：员工汇报给经理
• COUNT(emp.employee_id)：统计下属数量
• ROUND(AVG(emp.age))：平均年龄四舍五入

---

1789. 员工的直属部门 (Primary Department for Each Employee)

题目描述

Employee 表包含员工与其所属部门的关系：

• employee_id: 员工 ID。
• department_id: 部门 ID。
• primary_flag: 直属部门标志 ('Y' 表示是，'N' 表示否)。

一个员工可以属于多个部门。当员工属于多个部门时，其直属部门为 primary_flag = 'Y' 的记录。如果员工只属于一个部门，那么该部门直接视为直属部门（即便标志位是 'N'）。

解题思路

这道题考查 逻辑并集处理。

两种情况：

1. 显式直属：primary_flag = 'Y'
2. 隐式唯一：员工只属于一个部门

SQL 实现 (MySQL)

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

代码解析：

• primary_flag = 'Y'：显式标记为直属部门
• OR employee_id IN (...)：或者只属于一个部门
• HAVING COUNT(*) = 1：只出现一次的员工

---

610. 判断三角形 (Triangle Judgement)

题目描述

Triangle 表包含三条边的长度 x, y, z。 请编写一个解决方案，对每一组边判断它们是否可以组成一个三角形。

解题思路

这道题考查 数学逻辑 + CASE WHEN。

三角形判定条件：

任意两边之和大于第三边，即：

• $x + y > z$
• $x + z > y$
• $y + z > x$

SQL 实现 (MySQL)

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

-- 或使用 CASE WHEN
SELECT *, 
       CASE 
           WHEN x + y > z AND x + z > y AND y + z > x THEN 'Yes' 
           ELSE 'No' 
       END AS triangle 
FROM Triangle;

---

180. 连续出现的数字 (Consecutive Numbers)

题目描述

Logs 表包含 ID 和数字：

• id: 连续递增的 ID。
• num: 数字。

请编写一个解决方案，找出所有 至少连续出现三次 的数字。

解题思路

这道题考查 自连接找连续 或 窗口函数。

方法一：自连接

将表连接三次，比较相邻三行：

SELECT DISTINCT l1.num 
FROM Logs l1 
JOIN Logs l2 ON l1.id = l2.id - 1 
JOIN Logs l3 ON l1.id = l3.id - 2 
WHERE l1.num = l2.num AND l2.num = l3.num;

方法二：窗口函数

使用 LAG 获取前两行的值：

SELECT DISTINCT num 
FROM (
    SELECT num, 
           LAG(num, 1) OVER (ORDER BY id) AS prev1,
           LAG(num, 2) OVER (ORDER BY id) AS prev2
    FROM Logs
) t
WHERE num = prev1 AND num = prev2;

SQL 实现 (MySQL)

-- 方法：自连接
SELECT DISTINCT l1.num AS ConsecutiveNums 
FROM Logs l1 
JOIN Logs l2 ON l1.id = l2.id - 1 
JOIN Logs l3 ON l1.id = l3.id - 2 
WHERE l1.num = l2.num AND l2.num = l3.num;

---

1164. 指定日期的产品价格 (Product Price at a Given Date)

题目描述

Products 表记录了产品价格的变动历史：

• product_id: 产品 ID。
• new_price: 变动后的新价格。
• change_date: 变动日期。

请编写一个解决方案，查找 2019-08-16 时所有产品的价格。假设所有产品在第一次价格修改前，默认价格都是 10。

解题思路

这道题考查 时序数据 + 最新值查询。

分析过程：

1. 找出每个产品在 2019-08-16 之前（含）最后一次价格变动
2. 如果产品在 2019-08-16 之前没有价格变动，价格为默认值 10

关键步骤：

1. 获取所有产品的 ID（可能有产品从未改过价格）
2. 找出每个产品在目标日期之前的最新价格

SQL 实现 (MySQL)

SELECT DISTINCT product_id, IFNULL(p2.new_price, 10) AS price 
FROM (SELECT DISTINCT product_id FROM Products) p1 
LEFT JOIN Products p2 ON p1.product_id = p2.product_id 
  AND (p2.product_id, p2.change_date) IN (
    SELECT product_id, MAX(change_date) 
    FROM Products 
    WHERE change_date <= '2019-08-16' 
    GROUP BY product_id
);

代码解析：

• (SELECT DISTINCT product_id FROM Products)：所有产品的基表
• 子查询找出每个产品在目标日期之前的最新变动日期
• IFNULL(p2.new_price, 10)：没有变动记录时使用默认值 10

---

1204. 最后一个能进入巴士的人 (Last Person to Fit in the Bus)

题目描述

Queue 表记录了等待上车的人群及其重量：

• person_id, person_name, weight: 个人信息及重量。
• turn: 上车顺序（1 表示第一个，以此类推）。

巴士的最大限重为 1000 公斤。请查询最后一个可以上巴士且不超过重量限制的人名。

解题思路

这道题考查 滚动累计（Running Sum）。

核心步骤：

1. 按上车顺序计算累计重量
2. 找出累计重量 <= 1000 的最后一个人

滚动累计：

SUM(weight) OVER (ORDER BY turn) AS total_weight
-- 计算从第一行到当前行的累计和

SQL 实现 (MySQL)

SELECT person_name 
FROM (
    SELECT person_name, 
           SUM(weight) OVER (ORDER BY turn) AS total 
    FROM Queue
) t 
WHERE total <= 1000 
ORDER BY total DESC 
LIMIT 1;

代码解析：

• SUM(weight) OVER (ORDER BY turn)：按顺序计算累计重量
• WHERE total <= 1000：筛选不超过限制的
• ORDER BY total DESC LIMIT 1：取最后一个符合条件的

---

1907. 按分类统计薪水 (Count Salary Categories)

题目描述

报告每个薪水类别的银行账户数量。类别包括：

• Low Salary: 收入严格小于 20000。
• Average Salary: 收入在 [20000, 50000] 之间（包含边界）。
• High Salary: 收入严格大于 50000。

结果必须包含所有三个类别。如果某个类别没有账户，则该类别对应的计数应为 0。

解题思路

这道题考查 强制返回所有类别。

问题：直接用 GROUP BY 无法返回数据中不存在的类别。

解决方案：使用 UNION 为每个类别单独查询。

SQL 实现 (MySQL)

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

代码解析：

• SUM(条件)：MySQL 中布尔值转为 1/0 后求和，即统计满足条件的行数
• 三个 UNION 分别查询三个类别
• 确保所有类别都出现在结果中

---

知识点总结

窗口函数核心语法

函数名() OVER (
    PARTITION BY 分组列    -- 分区
    ORDER BY 排序列        -- 排序
    ROWS BETWEEN ...       -- 窗口范围
)

常用窗口函数

函数	用途	示例
ROW_NUMBER()	连续编号	排序后分配行号
RANK()	排名（跳号）	比赛排名
DENSE_RANK()	排名（不跳号）	薪资排名
LAG(col, n)	前 n 行的值	计算环比
LEAD(col, n)	后 n 行的值	预测趋势
SUM() OVER	滚动累计	累计销售额

常见模式

-- 1. 分组排名
DENSE_RANK() OVER (PARTITION BY group_col ORDER BY value DESC)

-- 2. 滚动累计
SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)

-- 3. 移动平均
AVG(value) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- 4. 自连接找连续
JOIN table t2 ON t1.id = t2.id - 1

注意事项

1. 窗口函数在 GROUP BY 和 HAVING 之后执行
2. ORDER BY 会影响某些窗口函数的默认 frame
3. 使用 DISTINCT 时注意窗口函数的计算时机
4. 相关子查询在某些情况下可以用窗口函数替代，性能更好