数据库一道常见的题目--查找出各部门工资最高的员工信息

题目：有一个员工表和部门表，表结构分别如下图，查找出各部门工资最高的员工信息？

答案：

CREATE TABLE Employee( 
id int,
Name VARCHAR2(45),
Salary FLOAT,
Departmentid int
)

INSERT INTO EMPLOYEE VALUES(1,'joe',70000,1);
INSERT INTO EMPLOYEE VALUES(2,'Henty',80000,2);
INSERT INTO EMPLOYEE VALUES(3,'Sam',60000,2);
INSERT INTO EMPLOYEE VALUES(4,'Max',90000,1);

CREATE TABLE Department(
id int,
Name VARCHAR2(10)
)

INSERT INTO Department VALUES(1,'IT');
INSERT INTO Department VALUES(2,'Sales');

SELECT
 Department.NAME as Department,
 Employee.DEPARTMENTID as DEPARTMENTID,
 Employee.NAME as Employee,
 Employee.SALARY as Salary 
FROM
 Employee
 INNER JOIN Department
ON
 Employee.DepartmentId = Department.Id 
 AND Employee.SALARY IN (SELECT MAX(employee.SALARY) FROM employee GROUP BY employee.DEPARTMENTID)

结果输出：