LeetCode-184.部门工资最高的员工

题目：

找出每个部门工资最高的员工

表: Employee

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

表: Department

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

返回如下结果

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

添加表结构和数据

DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `Id` int(11) NOT NULL,
  `Name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `department` VALUES (1, 'IT');
INSERT INTO `department` VALUES (2, 'Sales');
--------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Salary` decimal(10, 2) NULL DEFAULT NULL,
  `DepartmentId` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `employee` VALUES (1, 'Joe', 70000.00, 1);
INSERT INTO `employee` VALUES (2, 'Jim', 90000.00, 1);
INSERT INTO `employee` VALUES (3, 'Henry', 80000.00, 2);
INSERT INTO `employee` VALUES (4, 'Sam', 60000.00, 2);
INSERT INTO `employee` VALUES (5, 'Max', 90000.00, 1);

思路：

查询最高工资，肯定用到 max , group by

解答：

SELECT d.NAME AS 'Department', e.NAME AS 'Employee', e.Salary 
FROM employee e
JOIN department d ON e.departmentId = d.Id 
WHERE
	(e.departmentId,e.Salary) IN ( SELECT departmentId, Max( Salary ) FROM employee GROUP BY departmentId );