题目:有一个员工表和部门表,表结构分别如下图,查找出各部门工资最高的员工信息?
答案:
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');SELECTDepartment.NAME as Department,Employee.DEPARTMENTID as DEPARTMENTID,Employee.NAME as Employee,Employee.SALARY as Salary FROMEmployeeINNER JOIN DepartmentONEmployee.DepartmentId = Department.Id AND Employee.SALARY IN (SELECT MAX(employee.SALARY) FROM employee GROUP BY employee.DEPARTMENTID)
结果输出: