跳到主要内容

高级查询和连接

高级查询涉及窗口函数、自连接逻辑、条件分支排序等，是区分 SQL 水平的关键点。

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（四舍五入到最近的整数）。 我们将“经理”定义为至少有一个直接下属的员工。结果按 employee_id 排序。

解题思路​

自连接 (Self Join)。

  1. 角色映射：将 Employees 表视为经理表 (mgr) 和下属表 (emp)。
  2. 关联：mgr.employee_id = emp.reports_to。
  3. 聚合统计：按经理分组，统计下属 ID 的数量 (COUNT) 以及年龄的均值 (AVG)。

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;

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. 隐式唯一：使用 HAVING COUNT(*) = 1 逻辑找出那些只在表中出现过一次的员工。
  3. 合并：使用 OR 或 UNION 将上述两种情况合并。

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
);

610. 判断三角形 (Triangle Judgement)​

题目描述​

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

解题思路​

几何判定逻辑。

  1. 规则：任意两边之和大于第三边（即 x+y>z, x+z>y, y+z>x）。
  2. 分支判断：在 SQL 中使用 IF 函数或 CASE WHEN 表达式输出结果。

SQL 实现 (MySQL)​

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

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

题目描述​

Logs 表包含 ID 和数字：

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

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

解题思路​

错位自连接或窗口函数。

  1. 自连接法：将表与自身连接两次，关联 l1.id = l2.id - 1 和 l2.id = l3.id - 1。
  2. 等值判断：判断 l1.num = l2.num 且 l2.num = l3.num。
  3. 去重：使用 DISTINCT 避免一个数字连续出现 4 次以上导致重复输出。

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 为止，每个产品最后一次改价的记录（即 MAX(change_date)）。
  2. 处理未改价产品：先通过 SELECT DISTINCT product_id 获取所有产品的底表。
  3. 连接与补全：将底表与最新改价记录左连接。若有记录，则取 new_price；若无记录（说明 8-16 前没改过价），则使用 IFNULL(..., 10)。

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
);

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

题目描述​

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

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

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

解题思路​

滚动累计汇总 (Running Sum)。

  1. 累加计算：使用窗口函数 SUM(weight) OVER(ORDER BY turn) 按顺序计算每个时刻巴士内的总重量。
  2. 阈值筛选：过滤出所有累计重量小于等于 1000 的记录。
  3. 定位最后一名：对筛选结果按 turn 降序排列，取第一名 (LIMIT 1)。

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;

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

题目描述​

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

  • Low Salary: 收入严格小于 20000。
  • Average Salary: 收入在 [20000, 50000] 之间（包含边界）。
  • High Salary: 收入严格大于 50000。 Accounts 表包含账户的薪水信息。 | Column Name | Type | |-------------|------| | account_id | int | | income | int | account_id 是该表的主键。

请编写一个解决方案，统计每个薪水类别的账户数量。薪水类别定义如下：

  • Low Salary: 薪水严格小于 $20000。
  • Average Salary: 薪水在 20000到20000 到 50000 之间（包含边界）。
  • High Salary: 薪水严格大于 $50000。 结果必须包含所有三个类别。如果某个类别没有账户，则该类别对应的计数应为 0。

解题思路​

多分支结果集拼接。

  1. 局限性：直接使用 GROUP BY 配合 CASE WHEN 无法查询到原本就不存在的类别（即计数为 0 的情况）。
  2. 强制结构：分别为 "Low", "Average", "High" 编写三个查询。
  3. 合并：使用 UNION 将它们合并。每个查询内部单独统计符合条件的 account_id 数量。

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 的简洁统计写法，亦可使用 COUNT(IF(...)) )。 (注：SUM(条件) 在 MySQL 中会将布尔值转为数字 1/0 进行求和)。