【sql】部门最高工资 Department Highest Salary

为什么80%的码农都做不了架构师？>>>   

问题：

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  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | 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, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

解决：

① 让两个表内交起来，然后将结果表需要的列都标明，然后就是要找最高的薪水，我们用Max关键字来实现。2299 ms

SELECT d.Name Department,e1.Name Employee,e1.Salary Salary
FROM Employee e1 JOIN Department d ON e1.DepartmentId = d.Id
WHERE e1.Salary IN
(SELECT MAX(Salary) FROM Employee e2 WHERE e1.DepartmentId = e2.DepartmentId);/*不能用GROUP BY 对其进行分组。。。*/

② 不用Join关键字，直接用Where将两表连起来。1526 ms

SELECT d.Name Department,e1.Name Employee,e1.Salary Salary
FROM Employee e1,Department d
WHERE e1.DepartmentId = d.Id 
AND e1.Salary = 
(SELECT MAX(Salary) FROM Employee e2 WHERE e2.DepartmentId = d.Id);/*括号内的条件为e1时，得到的是一组数，为d时，得到的是一个值*/

③ 不用MAX方法而是用>=。 1187 ms

SELECT d.Name AS Department, e.Name AS Employee, e.Salary
FROM Employee e, Department d
WHERE e.DepartmentId = d.Id 
AND e.Salary >= ALL (SELECT Salary FROM Employee e2 WHERE e2.DepartmentId = d.Id);/*使用ALL是因为子查询语句会返回不止一行数据,使用ALL会与返回的每一个数据进行比较*/

SELECT d.Name Department, e1.Name Employee, e1.Salary
FROM Employee e1, Department d
WHERE e1.DepartmentId = d.Id 
AND e1.Salary >= ALL(SELECT Salary FROM Employee e2 WHERE e2.DepartmentId = e1.DepartmentId);

转载于:https://my.oschina.net/liyurong/blog/1570853