leetcode mysql 184. 部门工资最高的员工

题目

+--------------+---------+
| 列名          | 类型    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id是此表的主键列。
departmentId是Department表中ID的外键。
此表的每一行都表示员工的ID、姓名和工资。它还包含他们所在部门的ID。

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 e.departmentid,max(e.salary) from Employee e  group by e.departmentid

)

注：两表不能用left jion，否则会把其中一张表的每条数据全部打出