题目9:MySQL---------Department Top Three Salaries

来源:互联网 时间:1970-01-01

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

create table Employee ( Id int NOT NULL AUTO_INCREMENT, Name char(10) null, Salary int null, DepartmentId int null, primary key (Id));INSERT INTO Employee(Id ,Name,Salary,Departmentid) values(1,"Joe",70000,1);INSERT INTO Employee(Id ,Name,Salary,Departmentid) values(2,"Henry",80000,2);INSERT INTO Employee(Id ,Name,Salary,Departmentid) values(3,"Sam",60000,2);INSERT INTO Employee(Id ,Name,Salary,Departmentid) values(4,"Max",9000,1);INSERT INTO Employee(Id ,Name,Salary,Departmentid) values(5,"Janet",69000,1);INSERT INTO Employee(Id ,Name,Salary,Departmentid) values(6,"Randy",85000,1);create table Department( Id INT NOT NULL auto_increment, Name char(10) NULL, primary key (Id) );insert into Department(Id, Name) values(1,"IT");insert into Department(Id, Name) values(2,"Sales");

答案:

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) in (0, 1, 2) and E.DepartmentId = D.Id order by E.DepartmentId, E.Salary DESC;











相关阅读:
Top