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;