LeetCode刷题笔记 - 185.Department Top Three Salary

2018-10-30

185.Department Top Three Salaries

一、Description：

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 to find 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  |
+------------+----------+--------+

二、Solution:

解法一：

#Approach: Using JOIN and sub-query [Accepted]
Algorithm

#A top 3 salary in this company means there is no more than 3 salary bigger than itself in the company.

select e1.Name as 'Employee', e1.Salary
from Employee e1
where 3 >
(
    select count(distinct e2.Salary)
    from Employee e2
    where e2.Salary > e1.Salary
)
;
#In this code, we count the salary number of which is bigger than e1.Salary. So the output is as below for the sample data.

| Employee | Salary |
|----------|--------|
| Henry    | 80000  |
| Max      | 90000  |
| Randy    | 85000  |
#Then, we need to join the Employee table with Department in order to retrieve the department information.

#MySQL:找出比当前薪水高的最多只能有两个，那么前三高的都能被取出来了

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        );

解法二：

#下面这种方法将上面方法中的 <3 换成了IN (0, 1, 2)，是一样的效果：
SELECT d.Name AS Department, 
       e.Name AS Employee, 
       e.Salary 
FROM Employee e, Department d
WHERE (SELECT COUNT(DISTINCT Salary) FROM Employee WHERE Salary > e.Salary
AND DepartmentId = d.Id) IN (0, 1, 2) AND e.DepartmentId = d.Id 
ORDER BY d.Name, e.Salary DESC;

解法三：

#或者我们也可以使用Group by Having Count(Distinct ..) 关键字来做：
SELECT     
        d.name      as "Department"                     
        ,e1.Name as "Employee"
        , e1.Salary as "Salary"       
    FROM 
        Employee e1 
        JOIN Employee e2  
        JOIN Department d                       
    WHERE 
        e1.DepartmentId = e2.DepartmentId 
        AND e1.Salary <= e2.Salary  AND d.id = e2.DepartmentId                       
    GROUP BY d.name,e1.id
    HAVING COUNT(DISTINCT(e2.Salary)) <= 3
     order by d.name , salary desc 

解法四：

下面这种方法略微复杂一些，用到了变量，跟Consecutive Numbers中的解法三使用的方法一样，目的是为了给每个人都按照薪水的高低增加一个rank，最后返回rank值小于等于3的项即可，参见代码如下：

SELECT d.Name AS Department, 
       e.Name AS Employee, 
			 e.Salary 
FROM (
       SELECT Name, 
			  Salary, 
			  DepartmentId,
              @rank := IF(@pre_d = DepartmentId, @rank + (@pre_s <> Salary), 1) AS rank,
              @pre_d := DepartmentId, @pre_s := Salary 
        FROM Employee, 
			(SELECT @pre_d := -1, @pre_s := -1, @rank := 1) AS init
        ORDER BY DepartmentId, Salary DESC) e 
JOIN Department d ON e.DepartmentId = d.Id
WHERE e.rank <= 3 
ORDER BY d.Name, e.Salary DESC;

三、总结知识点:

1、ALTER、UPDATE语法

之前的习题中已有employee表，只有Id、Name、Salary 这三列，没有DepartmentId，现在给原有的表里加一列进去，并赋值，语法如下：

alter table employee add DepartmentId int;
update employee set DepartmentId=1 where id=1 or id=4;
update employee set DepartmentId=2 where id=2;
update employee set DepartmentId=2 where id=3;
-- update employee set DepartmentId=1 where id=4;