Department Top Three Salaries

/ mysql / 没有评论 / 324浏览

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

大概意思查出每个部门薪水前三的人(隐藏条件:假设其中一个部门薪水为5,5,5,4,4,3,2,1查询的结果位5,5,5,4,4,3)。

我的sql语句:

SELECT d.Name AS Department ,e.Name AS Employee ,e.Salary FROM
(SELECT a.Id,a.Name,a.Salary,a.DepartmentId,
(SELECT COUNT(b.Salary) FROM (SELECT DISTINCT d.Salary,d.DepartmentId FROM employee d GROUP BY d.Salary,d.DepartmentId) b WHERE a.DepartmentId=b.DepartmentId  AND b.Salary>a.Salary)+1 AS rank 
FROM employee a)e,
Department d
WHERE e.DepartmentId=d.Id AND e.rank<=3 ORDER BY d.Id ASC,e.Salary DESC
点击这里给我发消息
嘿!有什么能帮到您的吗?