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;