本文共 1551 字，大约阅读时间需要 5 分钟。

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  | Jim   | 90000  | 1            || 3  | Henry | 80000  | 2            || 4  | Sam   | 60000  | 2            || 5  | 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, your SQL query should return the following rows (order of rows does not matter).+------------+----------+--------+| Department | Employee | Salary |+------------+----------+--------+| IT         | Max      | 90000  || IT         | Jim      | 90000  || Sales      | Henry    | 80000  |+------------+----------+--------+

以前使用IN，都是局限于单个数值使用，从未尝试过多个数据使用IN.

# Write your MySQL query statement belowSELECT Employee.Name AS Employee, Employee.Salary, Department.Name AS Department FROM Employee, Department WHERE     Employee.DepartmentId = Department.Id    AND (Employee.DepartmentId, Employee.Salary)    IN         (SELECT Employee.DepartmentId, max(Employee.Salary)         FROM Employee         GROUP BY Employee.DepartmentId);

转载地址：http://jkuki.baihongyu.com/