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);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/121218.html
標籤：MySQL
上一篇：資料庫面試時常見的26個問題