Department Top Three Salaries

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

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 (order of rows does not matter).

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

Explanation:

In IT department, Max earns the highest salary, both Randy and Joe earn the second highest salary, and Will earns the third highest salary. There are only two employees in the Sales department, Henry earns the highest salary while Sam earns the second highest salary.

这道题给出了一个员工表,一个部门表,让找出每个不同部门里面前三高的工资的员工,包括员工姓名,所属部门,工资。
首先要求部门名和员工姓名,这两个属性属于不同的表,所以肯定要用到连接,直接按部门号连接两个表就行。
难点在于查询部门里工资前三高的员工,我的方法是用一个嵌套查询,通过这个嵌套查询按部门id自连接两张Employee表,查询出比外层查到的员工工资高的个数<3的工资的个数,即查出至多只有2个人比自己的工资高,也就是查到了每个部门里top3的工资和员工了。

Mycode:

# Write your MySQL query statement below
select d.name Department, e1.name Employee, e1.salary 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 e2.departmentid = e1.departmentid) < 3;

谢谢你的观看!

发布了30 篇原创文章 · 获赞 46 · 访问量 4万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 技术工厂 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览