[LeetCode]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 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  |
+------------+----------+--------+

题目大意:

雇员表Employee保存了雇员的Id,姓名,薪水,以及部门Id。部门表Department保存了部门的Id和名称。

编写一个SQL查询找出每一个部门薪水排名前3位的雇员信息(排名可以并列),样例如上。

解题思路:

参考StackOverflow的问答(http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results)

LeetCode OJ将此题的难度标记为Hard,可见题目的确有一定的难度。

解题步骤:

首先将雇员表按照雇员的DepartmentId和Salary分别正序和倒序排列;

然后利用MySQL用户定义变量对雇员标记排名rank值,同一部门的rank值从1开始,按照薪水降序递增(注意薪水相同时rank值不变);

最后筛选rank值不大于3的雇员信息,并与Department表做关联即可。

易错样例:

{"headers": {"Employee": ["Id", "Name", "Salary", "DepartmentId"], "Department": ["Id", "Name"]}, "rows": {"Employee": [[1, "Joe", 60000, 1], [2, "Ralph", 50000, 1], [3, "Joel", 60000, 1], [4, "Tracy", 75000, 1]], "Department": [[1, "IT"]]}}
Output:    {"headers": ["Department", "Employee", "Salary"], "values": [["IT", "Tracy", 75000], ["IT", "Joe", 60000], ["IT", "Joel", 60000]]}
Expected:    {"headers": ["Department", "Employee", "Salary"], "values": [["IT", "Tracy", 75000], ["IT", "Joe", 60000], ["IT", "Joel", 60000], ["IT", "Ralph", 50000]]}

SQL语句:

SELECT d.NAME AS Department, t.NAME AS Employee, Salary FROM (
  SELECT    DepartmentId,
            NAME,
            Salary, 
            @rank := IF(@prevDeptId != DepartmentId, 1, 
                         IF(@prevSalary = Salary, @rank, @rank + 1) ) AS Rank,
            @prevDeptId := DepartmentId AS prevDeptId,
            @prevSalary := Salary AS prevSalary
  FROM      Employee e, (SELECT @rank := 0, @prevDeptId := NULL, @prevSalary := NULL) r
  ORDER BY  DepartmentId ASC, Salary DESC
) t INNER JOIN Department d ON t.DepartmentId = d.ID
WHERE t.rank <= 3

另外,在LeetCode Discuss中看到这样一个短小精悍的解法:

链接:https://oj.leetcode.com/discuss/23002/my-tidy-solution

SELECT D.Name AS Department, E.Name AS Employee, E.Salary AS Salary 
FROM Employee E, Department D
WHERE (SELECT COUNT(DISTINCT(Salary)) FROM Employee 
       WHERE DepartmentId = E.DepartmentId AND Salary > E.Salary) < 3
AND E.DepartmentId = D.Id 
ORDER BY E.DepartmentId, E.Salary DESC;

 

本文链接:http://bookshadow.com/weblog/2015/01/24/leetcode-department-top-three-salaries/
请尊重作者的劳动成果,转载请注明出处!书影博客保留对文章的所有权利。

如果您喜欢这篇博文,欢迎您捐赠书影博客: ,查看支付宝二维码

Pingbacks已关闭。

评论
  1. 郑州SEO优化 郑州SEO优化 发布于 2015年1月27日 09:43 #

    博主博客的百度竞价广告收入如何啊?

  2. yn yn 发布于 2015年3月16日 09:33 #

    不太理解最后一个简单解法。。为什么count distinct salary 可以有不同的值?难道返回的不是就一个总值吗?求解!

  3. 在线疯狂 在线疯狂 发布于 2015年3月16日 16:52 #

    count distinct salary在where子句中,会对Employee E, Department D等值连接产生的每一条记录做筛选。

张贴您的评论