[LeetCode][SQL]Department Top Three Salaries
Department Top Three Salaries
The Employee
table holds all employees. Every employee has an Id, and there is also a column for the department Id.
+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | +----+-------+--------+--------------+
The Department
table holds all departments of the company.
+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+
Write a SQL query tofind employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.
+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Randy | 85000 | | IT | Joe | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+
https://leetcode.com/problems/department-top-three-salaries/
不愧是hard,特别费劲。
一开始想得很简单,就像一个循环一样,外层塞一个DepartmentId给内层,内层去找这个部门下的top3的,轻松愉快。
问题是MySQL不支持这语法Orz.
'This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
1 select D.Name, E1.Name, E1.Salary from Employee E1, Department D where E1.Id in 2 ( 3 select E2.Id from Employee E2 4 where E1.DepartmentId = E2.DepartmentId 5 order by E2.Salary desc 6 ) 7 and E1.DepartmentId = D.Id 8 order by E1.DepartmentId, E1.Salary desc
然后换了个曲线救国的写法,还是外层塞DepartmentId进去,里层数有几个。
搞了很久都挂在这个case上:
1 insert into Employee values('1', 'Joe', '70000', '1'); 2 insert into Employee values('2', 'Henry', '80000', '1'); 3 insert into Employee values('3', 'Sam', '80000', '1'); 4 insert into Employee values('4', 'Max', '90000', '1'); 5 6 insert into Department values('1', 'IT');
Case期望4条都选出来....
本来是count(*),最后改成count(distinct(E2.Salary)) 就过了
1 select D.Name, E1.Name, E1.Salary from Employee E1, Department D 2 where ( 3 select count(distinct(E2.Salary)) from Employee E2 4 where E1.DepartmentId = E2.DepartmentId 5 and E1.Id <> E2.ID 6 and E1.Salary < E2.Salary 7 ) < 3 8 and E1.DepartmentId = D.Id 9 order by E1.DepartmentId, E1.Salary desc