Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int)
Create table If Not Exists Department (Id int, Name varchar(255))
Truncate table Employee
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', '90000', '1')
Truncate table Department
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
FROM
Employee E,
(SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId) T,
Department D
WHERE E.DepartmentId = T.DepartmentId
AND E.Salary = T.max
AND E.DepartmentId = D.id
--第二种
SELECT D.Name,A.Name,A.Salary
FROM
Employee A,
Department D
WHERE A.DepartmentId = D.Id
AND NOT EXISTS
(SELECT 1 FROM Employee B WHERE B.Salary > A.Salary AND A.DepartmentId = B.DepartmentId)
--第三种
SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary
from
Employee E,
Department D
WHERE E.DepartmentId = D.id
AND (DepartmentId,Salary) in
(SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId)
