MySQL多场景查询实现咨询:含单表多比较、多表关联四类需求
MySQL多表查询需求实现方案
以下实现基于给出的office、staff两张表结构,兼容MySQL 8.0及以上版本,特殊场景兼容方案会单独标注。
a) 查询所有薪资高于其直属主管的员工
逻辑:对staff表做自连接,将员工记录与对应直属主管的记录关联,筛选员工薪资大于主管薪资的条目即可。
SELECT s.id AS staff_id, s.name AS staff_name, s.wage AS staff_wage, m.name AS chief_name, m.wage AS chief_wage FROM staff s INNER JOIN staff m ON s.chief_id = m.id WHERE s.wage > m.wage;
b) 查询所有办事处列表,以及各办事处内薪资最高的员工(无员工的办事处也输出,最高薪资多人并列则全部展示)
逻辑:先用窗口函数DENSE_RANK()对每个办事处的员工按薪资降序排名,取排名为1的即为当前办事处最高薪资员工;再通过office表左连接上述结果,保证无员工的办事处也会被输出。
WITH staff_rank AS ( SELECT *, DENSE_RANK() OVER(PARTITION BY office_id ORDER BY wage DESC) AS wage_rank FROM staff ) SELECT o.id AS office_id, o.name AS office_name, sr.id AS staff_id, sr.name AS staff_name, sr.wage AS max_wage FROM office o LEFT JOIN staff_rank sr ON o.id = sr.office_id AND sr.wage_rank = 1;
如果使用不支持CTE和窗口函数的MySQL 5.7版本,可以用子查询实现:
SELECT o.id AS office_id, o.name AS office_name, s.id AS staff_id, s.name AS staff_name, s.wage AS max_wage FROM office o LEFT JOIN staff s ON o.id = s.office_id LEFT JOIN ( SELECT office_id, MAX(wage) AS max_wage FROM staff GROUP BY office_id ) t ON s.office_id = t.office_id AND s.wage = t.max_wage WHERE s.id IS NULL OR s.wage = t.max_wage;
c) 查询所有仅有1名直属下属的主管
逻辑:先统计每个chief_id对应的下属数量,筛选出数量为1的chief_id,再关联staff表取出主管的详细信息即可。
SELECT m.id AS chief_id, m.name AS chief_name, COUNT(s.id) AS subordinate_count FROM staff m INNER JOIN staff s ON m.id = s.chief_id GROUP BY m.id, m.name HAVING COUNT(s.id) = 1;
d) 查询所有办事处按其下属员工总薪资降序排序的结果
逻辑:office表左连接staff表,按办事处维度分组统计总薪资,无员工的办事处总薪资为0,最后按总薪资降序排序即可。
SELECT o.id AS office_id, o.name AS office_name, IFNULL(SUM(s.wage), 0) AS total_wage FROM office o LEFT JOIN staff s ON o.id = s.office_id GROUP BY o.id, o.name ORDER BY total_wage DESC;
内容的提问来源于stack exchange,提问作者strangedenial
相关产品推荐
相关产品推荐

