MySQL/184. Department Highest Salary 部门最高工资的员工

题目

---

 

 

 

 

代码部分一（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