高频 SQL 50 题 之 子查询篇 1978. 上级经理已离职的公司员工 626. 换座位 1341. 电影评分 1321. 餐馆营业额变化增长 602 585 185. 部门工资前三高的所有员工

1978. 上级经理已离职的公司员工626. 换座位1341. 电影评分1321. 餐馆营业额变化增长602. 好友申请 II 谁有最多的好友585. 2016年的投资185. 部门工资前三高的所有员工1978. 上级经理已离职的公司员工SELECT e1.employee_id FROM Employees e1 WHERE e1.salary 30000 AND e1.manager_id NOT IN (SELECT employee_id FROM Employees) ORDER BY e1.employee_id;题目要求找出工资少于 30000 且上级经理已离职的员工核心逻辑筛选出工资salary 30000的员工。这些员工的manager_id不在employee_id列表中即经理已离职。代码解析WHERE e1.salary 30000筛选出工资少于 30000 的员工。AND e1.manager_id NOT IN (SELECT employee_id FROM Employees)子查询SELECT employee_id FROM Employees获取所有在职员工的 ID 列表。NOT IN确保员工的上级经理 ID 不在在职列表中即经理已离职。ORDER BY e1.employee_id按员工 ID 升序排列结果符合题目要求。626. 换座位SELECT id, IF(MOD(id, 2) 1, IFNULL((SELECT student FROM Seat WHERE id s.id 1), s.student), IFNULL((SELECT student FROM Seat WHERE id s.id - 1), s.student)) AS student FROM Seat s ORDER BY id;题目要求两两交换学生座位规则奇数号与相邻偶数号交换如 1↔23↔4。若总人数为奇数最后一个座位最大 id保持不变。代码解析核心逻辑奇数 id1, 3, 5...与 id1 的学生交换。偶数 id2, 4...与 id-1 的学生交换。边界处理使用IFNULL确保最后一个学生奇数个时不交换。逐行拆解IF(MOD(id, 2) 1, ..., ...)判断当前 id 是奇数还是偶数。奇数分支IFNULL((SELECT student FROM Seat WHERE id s.id 1), s.student)尝试取 id1 的学生。如果 id1 不存在即总人数为奇数则保持原学生不变。偶数分支IFNULL((SELECT student FROM Seat WHERE id s.id - 1), s.student)取 id-1 的学生。偶数 id 一定有前一个所以IFNULL主要是为了结构统一逻辑上不会触发。ORDER BY id按 id 升序返回结果。1341. 电影评分( SELECT u.name AS results FROM Users u JOIN MovieRating mr ON u.user_id mr.user_id GROUP BY u.user_id, u.name ORDER BY COUNT(mr.movie_id) DESC, u.name ASC LIMIT 1 ) UNION ALL ( SELECT m.title AS results FROM Movies m JOIN MovieRating mr ON m.movie_id mr.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 ASC LIMIT 1 );评论数最多的用户按用户分组统计评论数取评论数最多、字典序最小的用户名。2020 年 2 月平均分最高的电影筛选 2020-02 的评分记录按电影分组计算平均分取平均分最高、字典序最小的电影名。代码解析第一部分评论数最多的用户SELECT u.name AS results FROM Users u JOIN MovieRating mr ON u.user_id mr.user_id GROUP BY u.user_id, u.name ORDER BY COUNT(mr.movie_id) DESC, u.name ASCLIMIT 1JOIN关联用户和评分表。GROUP BY按用户分组统计每个用户的评论数。ORDER BY COUNT(...) DESC, u.name ASC先按评论数降序再按用户名字典序升序取第一条。第二部分2020 年 2 月平均分最高的电影SELECT m.title AS results FROM Movies m JOIN MovieRating mr ON m.movie_id mr.movie_id WHERE mr.created_at BETWEEN 2020-02-01 AND 2020-02-29GROUP BY m.movie_id, m.title ORDER BY AVG(mr.rating) DESC, m.title ASCLIMIT 1WHERE筛选 2020 年 2 月的评分记录。GROUP BY按电影分组计算平均分。ORDER BY AVG(...) DESC, m.title ASC先按平均分降序再按电影名字典序升序取第一条。合并结果UNION ALL直接将两个查询结果合并符合题目输出格式要求。1321. 餐馆营业额变化增长SELECT DISTINCT visited_on, sum_amount AS amount, ROUND(sum_amount/7, 2) AS average_amount -- 以上是并计算平均值少用一次窗口函数提高运行速度 FROM ( SELECT visited_on, SUM(amount) OVER ( ORDER BY visited_on ROWS 6 PRECEDING ) AS sum_amount -- 以下是计算每天的金额总量 FROM ( SELECT visited_on, SUM(amount) AS amount FROM Customer GROUP BY visited_on ) TT ) LL -- 最后手动只要覆盖完整7天的数据 WHERE DATEDIFF(visited_on, (SELECT MIN(visited_on) FROM Customer)) 6代码拆解第一层最内层按日期汇总每日营业额SELECT visited_on, SUM(amount) AS amount FROM Customer GROUP BY visited_on作用先把同一天所有顾客的消费金额求和得到每日总营业额。解决问题题目没保证 “每天一条记录”所以必须先按天汇总。第二层中间层计算连续 7 天的滚动总营业额SELECT visited_on, SUM(amount) OVER (ORDER BY visited_on ROWS 6 PRECEDING) AS sum_amount FROM (...) TTSUM(amount) OVER (ORDER BY visited_on ROWS 6 PRECEDING)窗口按日期排序窗口范围当前行 前 6 行共 7 行得到的是连续 7 天的总营业额这里用SUM窗口函数比直接算平均更高效。第三层最外层筛选、计算平均并去重SELECT DISTINCT visited_on, sum_amount AS amount, ROUND(sum_amount/7, 2) AS average_amount FROM (...) LL WHERE DATEDIFF(visited_on, (SELECT MIN(visited_on) FROM Customer)) 6ORDER BY visited_on;WHERE DATEDIFF(...) 6只保留从第 7 天开始的记录确保窗口是完整的 7 天ROUND(sum_amount/7, 2)把 7 天的总营业额除以 7得到平均值并保留两位小数DISTINCT保证同一日期只返回一条结果ORDER BY visited_on按日期升序排列符合题目要求602. 好友申请 II 谁有最多的好友WITH AllFriends AS ( SELECT requester_id AS id FROM RequestAccepted UNION ALL SELECT accepter_id AS id FROM RequestAccepted ), FriendCounts AS ( SELECT id, COUNT(*) AS num FROM AllFriends GROUP BY id ) SELECT id, num FROM FriendCounts WHERE num (SELECT MAX(num) FROM FriendCounts);代码拆解AllFriendsCTE把所有好友关系拆出来SELECT requester_id AS id FROM RequestAccepted UNION ALLSELECT accepter_id AS id FROM RequestAcceptedFriendCountsCTE统计每个用户的好友数SELECT id, COUNT(*) AS num FROM AllFriends GROUP BY id按用户分组统计每个用户出现的次数就是他的好友总数。主查询选出好友数最多的用户SELECT id, num FROM FriendCounts WHERE num (SELECT MAX(num) FROM FriendCounts);找出好友数等于最大值的所有用户既满足题目 “只有一个人” 的情况也能处理进阶题的 “多个人平局”。585. 2016年的投资SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016 FROM Insurance WHERE -- 条件1tiv_2015 出现次数 ≥ 2 tiv_2015 IN ( SELECT tiv_2015 FROM Insurance GROUP BY tiv_2015 HAVING COUNT(*) 2 ) AND -- 条件2(lat, lon) 组合唯一 (lat, lon) IN ( SELECT lat, lon FROM Insurance GROUP BY lat, lon HAVING COUNT(*) 1 );代码拆解筛选重复的tiv_2015SELECT tiv_2015 FROM Insurance GROUP BY tiv_2015 HAVING COUNT(*) 2先按tiv_2015分组筛选出出现次数≥2 的投保金额确保该金额至少被两个人持有。筛选唯一的(lat, lon)组合SELECT lat, lon FROM Insurance GROUP BY lat, lon HAVING COUNT(*) 1按(lat, lon)组合分组筛选出只出现一次的组合确保该城市没有其他投保人。汇总符合条件的tiv_2016SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016 FROM Insurance WHERE tiv_2015 IN (...) AND (lat, lon) IN (...)同时满足两个条件的记录对tiv_2016求和并保留两位小数。185. 部门工资前三高的所有员工WITH RankedSalaries AS ( SELECT departmentId, name AS Employee, salary, DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS rnk FROM Employee ) SELECT d.name AS Department, rs.Employee, rs.salary AS Salary FROM RankedSalaries rs JOIN Department d ON rs.departmentId d.id WHERE rs.rnk 3;题目要求找出每个部门中** 工资排名前三含并列** 的员工。核心步骤对每个部门内的员工按工资去重后排序确定排名。筛选出部门内排名前三的员工。关联部门表输出部门名、员工名和工资。题目要求找出每个部门中** 工资排名前三含并列** 的员工。核心步骤对每个部门内的员工按工资去重后排序确定排名。筛选出部门内排名前三的员工。关联部门表输出部门名、员工名和工资。代码解析CTERankedSalaries计算部门内工资排名SELECT departmentId, name AS Employee, salary, DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS rnk FROM Employee主查询筛选并关联部门信息SELECT d.name AS Department, rs.Employee, rs.salary AS Salary FROM RankedSalaries rs JOIN Department d ON rs.departmentId d.id WHERE rs.rnk 3总结本文汇总了6道SQL查询题的解法涵盖多种场景1. 员工管理1978题通过子查询筛选工资低于3万且经理离职的员工。2. 座位交换626题使用条件判断实现奇数偶数ID学生互换。3. 电影评分分析1341题联合查询找出评论最多用户和评分最高电影。4. 营业额统计1321题运用窗口函数计算7天滚动营业额和平均值。5. 好友关系分析602题通过UNION ALL合并请求方和接收方统计最多好友。6. 投资数据查询585题组合条件筛选满足特定要求的投资记录。7. 部门工资排名185题使用D