[LeetCode][SQL]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.DepartmentId5 order by E2.Salary desc6 ) 7 and E1.DepartmentId = D.Id8 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 D2 where (3 select count(distinct(E2.Salary)) from Employee E24 where E1.DepartmentId = E2.DepartmentId5 and E1.Id <> E2.ID6 and E1.Salary < E2.Salary7 ) < 38 and E1.DepartmentId = D.Id9 order by E1.DepartmentId, E1.Salary desc

[LeetCode][SQL]Department Top Three Salaries

标签：

小编还为您整理了以下内容，可能对您也有帮助：

用sql中级函数不用rank之类的求出部门中员工工资在部门中的排名

用分析函数row_number(),rank(),dense_rank()求每个部门的工资前三名

select *

from(

select deptno,ename,sal,

row_number() over(partition by deptno order by sal desc) rn,

rank() over(partition by deptno order by sal desc) rk,

dense_rank() over(partition by deptno order by sal desc) drk

from emp)

where drk<=3;