Department Highest Salary

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  |
    +------------+----------+--------+
    題意：求出各部門中工資最高的，如果有多個最高工資則都輸出。
    1、求各部門最高工資
    2、內連接進行判斷哪個員工的工資等於最高工資
    select t.Department,e.name as 'Employee',e.salary 
        from Employee e inner join 
                (select d.id as 'Departmentid',d.name as 'Department',max(e.Salary) as 'max' from Employee e inner join Department d on e.departmentid=d.id group by d.name) t 
            on e.departmentid=t.departmentid and salary=max;