题目
代码部分一(1268ms)
SELECT
D.Name AS Department,E.Name AS Employee,E.Salary
FROM Employee E,Department D
WHERE E.DepartmentId=D.Id AND E.Salary IN(
SELECT MAX(e.Salary) FROM Employee e GROUP BY e.DepartmentId HAVING e.DepartmentId=D.Id
);
代码部分二(987ms)
SELECT
D.Name AS Department,E1.Name AS Employee,E1.Salary
FROM Employee E1
JOIN Department D ON D.Id=E1.DepartmentId
WHERE E1.Salary IN(
SELECT MAX(E2.Salary)
FROM Employee E2
WHERE D.Id=E2.DepartmentId
);
代码部分三(593ms)
SELECT
D.Name AS Department,E1.Name AS Employee,E1.Salary AS Salary
FROM Department D,Employee E1
WHERE D.Id=E1.DepartmentId AND (E1.DepartmentId,E1.Salary) IN(
SELECT E2.DepartmentId,MAX(E2.Salary)
FROM Employee E2
GROUP BY E2.DepartmentId
)
代码部分四(281ms)
SELECT d.Name AS Department,
e.Name AS Employee,
e.Salary AS Salary
FROM employee e, department d
WHERE e.DepartmentId = d.Id
AND (e.DepartmentId, e.Salary) IN (
SELECT
e2.DepartmentId,
max(e2.Salary)
FROM employee e2
GROUP BY e2.DepartmentId
)
代码部分五(263ms)
SELECT d.Name AS Department, e.Name AS Employee, t.Salary FROM
Employee e
INNER JOIN
(SELECT DepartmentId, MAX(Salary) AS Salary FROM Employee GROUP BY DepartmentId) t
USING(DepartmentId, Salary)
INNER JOIN
Department d
ON d.id = t.DepartmentId