185. 部门工资前三高的所有员工

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

Id	Name	Salary	DepartmentId
1	Joe	85000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1
5	Janet	69000	1
6	Randy	85000	1
7	Will	70000	1

Department 表包含公司所有部门的信息。

Id	Name
1	IT
2	Sales

编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

Department	Employee	Salary
IT	Max	90000
IT	Randy	85000
IT	Joe	85000
IT	Will	70000
Sales	Henry	80000
Sales	Sam	60000

解释：

IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。

题目条件

# MySQL
/*Table structure for table `employee` */

DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee` (
  `Id` int(11) DEFAULT NULL,
  `Name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `Salary` int(20) DEFAULT NULL,
  `DepartmentId` int(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `employee` */

insert  into `employee`(`Id`,`Name`,`Salary`,`DepartmentId`) values (1,'Joe',85000,1),(2,'Henry ',80000,2),(3,'Sam   ',60000,2),(4,'Max   ',90000,1),(5,'Janet ',69000,1),(6,'Randy',85000,1),(7,'Will  ',70000,1);

/*Table structure for table `department` */

DROP TABLE IF EXISTS `department`;

CREATE TABLE `department` (
  `Id` int(3) DEFAULT NULL,
  `Name` varchar(20) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `department` */

insert  into `department`(`Id`,`Name`) values (1,'IT       '),(2,'Sales    ');

方法：使用 JOIN 和子查询

算法

第一步：
由于Employee 表中有DepartmentId字段来标识每个员工对应的部门，故而使用 dense_Rank()排名函数 对Employee表内每个员工按部门分区（DepartmentId字段）进行工资（Salary字段）排序。

# MySQL
SELECT dense_Rank() over(PARTITION BY DepartmentId ORDER BY Salary DESC) AS number,`DepartmentId`,`Name`,`Salary` FROM `employee`;

结果

第二步：
将上一步查出来的结果作为一个临时表a，再使用where限制条件找出每个部门的前三高的所有员工。

# MySQL
SELECT 
  number,`DepartmentId`,`Name`,`Salary` 
FROM
  (SELECT 
    dense_Rank () over (
      PARTITION BY DepartmentId 
  ORDER BY Salary DESC
  ) AS number,`DepartmentId`,`Name`,`Salary` 
  FROM
    `employee`) AS a 
WHERE number <= 3 ;

结果

第三步：
将上一步的结果作为一个临时表b和Department 表连接（join）查出每个部门的前三高的所有员工对应的信息。

# MySQL
SELECT 
  `department`.`Name` Department,b.name Employee,b.Salary 
FROM
  `department` 
  JOIN 
    (SELECT 
      number,`DepartmentId`,`Name`,`Salary` 
    FROM
      (SELECT 
        dense_Rank () over (
          PARTITION BY DepartmentId 
      ORDER BY Salary DESC
      ) AS number,`DepartmentId`,`Name`,`Salary` 
      FROM
        `employee`) AS a 
    WHERE number <= 3) AS b 
    ON `department`.`Id` = b.DepartmentId ;

结果

方法2：使用 JOIN 和子查询

算法

公司里前 3 高的薪水意味着有不超过 3 个工资比这些值大。首先，我们统计了有多少人的工资比 e1.Salary 高；然后，我们需要把表 Employee 和表 Department 连接来获得部门信息。

# MySQL
SELECT 
  d.Name AS 'Department',e1.Name AS 'Employee',e1.Salary 
FROM
  Employee e1 
  JOIN Department d 
    ON e1.DepartmentId = d.Id 
WHERE  
  (SELECT 
    COUNT(DISTINCT e2.Salary) 
  FROM
    Employee e2 
  WHERE e2.Salary > e1.Salary 
    AND e1.DepartmentId = e2.DepartmentId) < 3
ORDER BY d.`Name`,e1.`Salary` DESC;

结果

来源：力扣（LeetCode）

上一题：部门工资最高的员工