The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.
1 2 3 4 5 6 7 8
+----+-------+--------+--------------+ | 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.
1 2 3 4 5 6
+----+----------+ | 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.
1 2 3 4 5 6
+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+
分析
找出每个部门里工资最高的员工
解决方案(MySQL)
看到题目,我心想,不就是一道典型的作业题嘛,于是写了下面的 SQL 语句:
1 2 3 4 5
# Write your MySQL query statement below SELECT d.Name as Department, e.Name as Employee, MAX(Salary) as Salary FROM Employee e, Department d WHERE e.DepartmentId=d.Id GROUP BY e.DepartmentId
# Write your MySQL query statement below SELECT d.Name AS Department, e.Name AS Employee, e.Salary AS Salary FROM Employee AS e, Department AS d WHERE e.DepartmentId=d.Id AND e.Salary >= ALL( SELECT tmp.Salary FROM Employee AS tmp WHERE tmp.DepartmentId=e.DepartmentId )