以前使用 ，都是局限於單個數值使用，從未嘗試過多個數據使用 . 此題涉及兩個表，肯定需要使用 操作. 此外，需要選取每個 的最大數值，那麼肯定涉及 以及 操作. 綜合以上因素，答案如下所示: ...
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.
此題涉及兩個表，肯定需要使用join操作.
此外，需要選取每個Department 的最大數值，那麼肯定涉及max以及group by操作.
綜合以上因素，答案如下所示:
# Write your MySQL query statement below
SELECT 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);
