LeetCode刷题之工资最高的员工
工资最高的员工(Department Highest Salary)
题目
SQL脚本
-
MySQL
# Write your MySQL query statement below --3. 关联部门表查最高员工所在部门名称 SELECT distinct d.name AS Department, em.name AS Employee, em.salary AS Salary FROM --2. 关联员工表和最高薪资临时表查各部门最高工资员工信息 (SELECT e.name, e.salary, e.departmentId FROM employee e INNER JOIN --1. 查各个部门最高薪资 (SELECT departmentId,MAX(salary) salary FROM employee GROUP BY departmentId) m ON e.salary = m.salary and e.departmentId = m.departmentId) em INNER JOIN department d ON em.departmentId = d.id
-
Oracle
select distinct d.name as Department, e.name as Employee, e.salary as salary from ( select departmentid,id,name,salary,rank() over(partition by departmentid order by salary desc) as pm from employee ) e innner join department d on e.departmentid = d.id where e.pm = 1
官方解法
-
思路分析
-
MySQL
SELECT Department.name AS 'Department', Employee.name AS 'Employee', Salary FROM Employee JOIN Department ON Employee.DepartmentId = Department.Id WHERE (Employee.DepartmentId , Salary) IN ( SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId )
-
Oracle
select b.Name as Department, t.Name as Employee, t.Salary as Salary from Department b left join ( select Name, Salary, DepartmentId, rank() over(partition by DepartmentId order by Salary desc) as rank from Employee ) t on t.DepartmentId = b.Id where t.rank = 1;
其他
in关键字
-
语法
-- 匹配多个字段 select id from table where (num,name) in ((num1,'name1'),(num2,'name2'))
Oracle常用分析函数
rank()
rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,比如:当第2名和第3名的利润相同时,rank的结果是1,2,2,4
dense_rank()
dense_rank则不会跳过这个排名,结果是1,2,2,3
row_number()
row_number哪怕是两个数据完全相同,排名也会不一样,结果是1,2,3,4