跳到主要内容

连接

连接查询 (Joins) 是 SQL 的灵魂，主要涉及 INNER JOIN, LEFT JOIN, RIGHT JOIN 以及自连接的应用场景。

1378. 使用唯一标识码替换员工ID (Employees Unified ID)​

题目描述​

你有两张表：

  • Employees: 包含 id (主键) 和 name。
  • EmployeeUNI: 包含 id 和 unique_id (两者共同作为主键)。

请编写一个解决方案，展示每位员工的 姓名 及其对应的 唯一标识码 (unique_id)。如果某位员工没有唯一标识码，则展示为 null。

解题思路​

使用 LEFT JOIN。

SQL 实现 (MySQL)​

SELECT unique_id, name 
FROM Employees LEFT JOIN EmployeeUNI USING(id);

1068. 产品销售分析 I (Product Sales Analysis I)​

题目描述​

你有两张表：

  • Sales: 包含 sale_id, product_id, year, quantity, price。
  • Product: 包含 product_id 和 product_name。

请编写一个解决方案，查询 Sales 表中每一笔销售记录对应的 产品名称 (product_name)、年份 (year) 和 价格 (price)。

解题思路​

简单 INNER JOIN 或直接通过 LEFT JOIN 产品表。

SQL 实现 (MySQL)​

SELECT product_name, year, price 
FROM Sales s JOIN Product p USING(product_id);

1581. 进店却未进行过交易的顾客 (Customer Visits with No Transactions)​

题目描述​

你有两张表：

  • Visits: 包含 visit_id 和 customer_id。记录了顾客进店的情况。
  • Transactions: 包含 transaction_id, visit_id, amount。记录了每笔交易及其对应的进店记录。

请编写一个解决方案，找出所有 进店了但没有进行交易 的顾客。你需要展示这些顾客的 customer_id 以及他们 未进行交易的次数。

解题思路​

LEFT JOIN + IS NULL。 将访问表左连交易表，过滤掉交易 ID 为空的部分，然后按顾客分组计数。

SQL 实现 (MySQL)​

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

197. 上升的温度 (Rising Temperature)​

题目描述​

Weather 表记录了每天的温度信息：

  • id: 日志 ID。
  • recordDate: 日期。
  • temperature: 当天的温度。

编写一个解决方案，找出所有 温度比前一天高 的日期的 ID。

解题思路​

自连接 + 时间差函数。

  1. 使用两个别名相同的表 w1 和 w2。
  2. 比较条件：日期相差 1 天 且 温度更高。

SQL 实现 (MySQL)​

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

1661. 每台机器的进程平均运行时间 (Average Time of Process per Machine)​

题目描述​

Activity 表记录了工厂中每台机器处理不同进程的情况：

  • machine_id: 机器 ID。
  • process_id: 进程 ID。
  • activity_type: 枚举 ('start', 'end')。
  • timestamp: 发生时间。

每台机器的每个进程都有配套的 'start' 和 'end' 记录。进程的 运行时间 为 end 时间与其对应的 start 时间之差。 请编写一个解决方案，计算每台机器执行进程的 平均时间，并将结果保留 3 位小数。

解题思路​

按机器分组统计时间。 通过 SUM(IF(type='start', -timestamp, timestamp)) 计算每台机器的总耗时。 最后除以进程数进行平均并保留 3 位小数。

SQL 实现 (MySQL)​

SELECT machine_id, ROUND(SUM(IF(activity_type = 'start', -timestamp, timestamp)) / COUNT(DISTINCT process_id), 3) AS processing_time 
FROM Activity 
GROUP BY machine_id;

577. 员工奖金 (Employee Bonus)​

题目描述​

你有两张表：

  • Employee: 包含员工姓名 name 和 ID empId。
  • Bonus: 包含 empId 和对应的奖金 bonus。

查询所有 奖金少于 1000 的员工姓名及其奖金数额。注意，没有奖金的员工（奖金为 NULL）也应该被包含在结果中。

解题思路​

LEFT JOIN + (IS NULL OR < 1000)。

SQL 实现 (MySQL)​

SELECT name, bonus 
FROM Employee e LEFT JOIN Bonus b USING(empId) 
WHERE bonus < 1000 OR bonus IS NULL;

1280. 学生们参加各科测试的次数 (Students and Examinations)​

题目描述​

你有三张表：

  • Students: 包含 student_id (主键) 和 student_name。
  • Subjects: 包含 subject_name (主键)。
  • Examinations: 记录了学生参加特定科目考试的记录，不含主键，可能存在多条相同记录。

查询出 每个学生 参加 每一门科目 测试的次数。结果需按 student_id 和 subject_name 升序排列。即使某学生没参加某门考试，次数也应显示为 0。

解题思路​

CROSS JOIN + LEFT JOIN。

  1. 笛卡尔积：使用 CROSS JOIN 将 Students 和 Subjects 表连接，得到每个学生与每个科目的所有可能组合。这是为了确保即使某学生没考某科也能出现在结果中。
  2. 左连接：将上述组合 LEFT JOIN 考试表 Examinations。
  3. 统计：GROUP BY 学生 ID 和科目，对考试记录的主键或科目名称进行 COUNT。

SQL 实现 (MySQL)​

SELECT s.student_id, s.student_name, sub.subject_name, COUNT(e.subject_name) AS attended_exams 
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;

570. 至少有5名直接下属的经理 (Managers with at Least 5 Direct Reports)​

题目描述​

在 Employee 表中，每一行包含员工的 ID, 姓名, 所在的部门以及他们的经理 ID (managerId)：

  • id: 员工 ID，主键。
  • name: 姓名。
  • managerId: 该员工经理的 ID。如果为 NULL，则该员工没有经理。

请编写一个解决方案，找出那些 至少有 5 名直接下属 的经理姓名。

解题思路​

分组统计 + 子查询过滤。

  1. 在 Employee 表中统计每个 managerId 出现的次数。
  2. HAVING COUNT(*) >= 5 过滤出目标经理的 ID。
  3. 使用 IN 子查询在外层查询中匹配经理的名字。

SQL 实现 (MySQL)​

SELECT name 
FROM Employee 
WHERE id IN (
    SELECT managerId FROM Employee GROUP BY managerId HAVING COUNT(*) >= 5
);

1934. 确认率 (Confirmation Rate)​

题目描述​

用户的 确认率 是 confirmed 消息的数量除以请求的确认消息的总数。从未请求过确认消息的用户的确认率为 0 。查询每个用户的确认率，并保留两位小数。

解题思路​

LEFT JOIN + 逻辑计数均值。

  1. Signups 表左连 Confirmations 表，保证所有注册用户都在结果中。
  2. 计算 action = 'confirmed' 的均值：AVG(c.action = 'confirmed')。
    • 在 MySQL 中，逻辑表达式返回 1 或 0，AVG 相当于计算 1 出现的频率。
  3. 对于从未发过请求的用户，AVG 结果为 NULL，使用 IFNULL(..., 0) 处理。

SQL 实现 (MySQL)​

SELECT s.user_id, ROUND(IFNULL(AVG(c.action = 'confirmed'), 0), 2) AS confirmation_rate 
FROM Signups s LEFT JOIN Confirmations c USING(user_id) 
GROUP BY s.user_id;