LeetCode：Department Highest Salary - 部门内最高工资

1、题目名称

Department Highest Salary（部门内最高工资）

2、题目地址

https://leetcode.com/problems/department-highest-salary/

3、题目内容

表Employee包括四列：Id、Name、Salary、DepartmentId

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

表Department表中包括了公司的所有部门

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

现要求写一个SQL，求出某一部门内领取最高工资的员工。

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

4、初始化数据库脚本

在MySQL数据库中建立一个名为LEETCODE的数据库，用MySQL命令行中的source命令执行下面脚本：

-- 执行脚本前必须建立名为LEETCODE的DATABASE
USE LEETCODE;
 
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee (
  Id INT NOT NULL PRIMARY KEY,
  Name VARCHAR(50),
  Salary INT,
  DepartmentId INT
);

INSERT INTO Employee (Id, Name, Salary, DepartmentId) VALUES (1, 'Joe', 70000, 1);
INSERT INTO Employee (Id, Name, Salary, DepartmentId) VALUES (2, 'Henry', 80000, 2);
INSERT INTO Employee (Id, Name, Salary, DepartmentId) VALUES (3, 'Sam', 60000, 2);
INSERT INTO Employee (Id, Name, Salary, DepartmentId) VALUES (4, 'Max', 90000, 1);

DROP TABLE IF EXISTS Department;
CREATE TABLE Department (
  Id INT NOT NULL PRIMARY KEY,
  Name VARCHAR(50)
);

INSERT INTO Department (Id, Name) VALUES (1, 'IT');
INSERT INTO Department (Id, Name) VALUES (2, 'Sales');

5、解题SQL1

一个思路是，先求出各个部门的最高工资，再考察Employee表中，工资数等于所在部门的最高工资的员工，即为所求。

SELECT D.Name AS Department, E.Name AS Employee, MAXS_D.MAXS AS Salary
FROM Employee AS E, Department AS D, (
  SELECT MAX(Salary) AS MAXS, DepartmentId
  FROM Employee
  GROUP BY DepartmentId) AS MAXS_D
WHERE E.Salary = MAXS_D.MAXS AND E.DepartmentId = D.Id AND 
  E.DepartmentId = MAXS_D.DepartmentId;

6、解题SQL2

另一个思路是，在同一部门中，工资大于等于该部门所有员工的员工，即为所求。

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 Salary >= ALL(
  SELECT Salary 
  FROM Employee E_TMP
  WHERE E_TMP.DepartmentId = E.DepartmentId);

END

转载于:https://my.oschina.net/Tsybius2014/blog/493715