题目:
表: Employee
表: Department
编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
查询结果格式如下例所示。
解题思路:
①先将Employee表按部门id分组,然后再按薪资进行降序排序。
②将临时表与department通过部门id连起来,然后再筛选出排名为1的相关数据。
1 # Write your MySQL query statement below 2 SELECT 3 b.name AS Department, 4 a.name AS Employee, 5 salary as Salary 6 FROM 7 ( SELECT 8 name, 9 departmentId, 10 salary, 11 dense_rank ( ) over ( 12 PARTITION BY departmentId ORDER BY salary desc ) AS rnk 13 FROM Employee 14 ) AS a 15 JOIN Department b ON a.departmentid = b.id 16 WHERE 17 a.rnk = 1;
小知识:
①窗口函数:dense_rank ( ) over ( PARTITION BY ... ORDER BY ... desc ) :相同的序号一样,下一个不同的连续数字(1,2,2,3...)
其实这里用rank()也可以,相同的序号一样,下一个不同的跳跃。(1,2,2,4...)
②sql中的内连接【交集】:inner join...on ...(简写为join...on...)
标签:...,name,rank,力扣,Department,184,MySQL,Employee From: https://www.cnblogs.com/liu-myu/p/17217879.html