184. Department Highest Salary

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+| Id | Name  | Salary | DepartmentId |+----+-------+--------+--------------+| 1  | Joe   | 70000  | 1            || 2  | Henry | 80000  | 2            || 3  | Sam   | 60000  | 2            || 4  | Max   | 90000  | 1            |+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+| Id | Name     |+----+----------+| 1  | IT       || 2  | Sales    |+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+| Department | Employee | Salary |+------------+----------+--------+| IT         | Max      | 90000  || Sales      | Henry    | 80000  |+------------+----------+--------+

SELECT    Department.name AS 'Department',    Employee.name AS 'Employee',    SalaryFROM    Employee        JOIN    Department ON Employee.DepartmentId = Department.IdGROUP BY Employee.DepartmentIdHAVING MAX(Salary)

开始学着用join 235ms

SELECT    Department.name AS 'Department',    Employee.name AS 'Employee',    SalaryFROM Employee,Department WHERE Employee.DepartmentId = Department.IdGROUP BY Employee.DepartmentIdHAVING MAX(Salary)

还是用这种习惯啊 179ms

SELECT    Department.name AS 'Department',    Employee.name AS 'Employee',    SalaryFROM    Employee        JOIN    Department ON Employee.DepartmentId = Department.IdWHERE    (Employee.DepartmentId , Salary) IN    (   SELECT            DepartmentId, MAX(Salary)        FROM            Employee        GROUP BY DepartmentId    );

使用嵌套 155s