题目8:MySQL---------Department Highest Salary

来源:转载

The Employee table holds all employees. Every employee has an Id, a salary, 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 |+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+| Id | Name |+----+----------+| 1 | IT || 2 | Sales |+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+| Department | Employee | Salary |+------------+----------+--------+| IT | Max | 90000 || Sales | Henry | 80000 |+------------+----------+--------+

创建表:

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",7000,1);INSERT INTO Employee(Id ,Name,Salary,Departmentid) values(2,"Henry",8000,2);INSERT INTO Employee(Id ,Name,Salary,Departmentid) values(3,"Sam",6000,2);INSERT INTO Employee(Id ,Name,Salary,Departmentid) values(4,"Max",9000,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 Department1, E.name as Employee, E.Salaryfrom Employee E, Department D, (select DepartmentId ,max(Salary) as max from Employee group by DepartmentId) Twhere E.DepartmentId = T.DepartmentId and E.Salary = T.max and E.DepartmentId = D.id;












分享给朋友:
您可能感兴趣的文章:
随机阅读: