Leecode經典的SQL

Leecode的題目。關於in的應用。感覺很經典，這裡列出解題過程。

題目描述

Employee 表包含所有員工資訊，每個員工有其對應的 Id, salary 和 department Id。

SQL如下：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Salary` int(255) NULL DEFAULT NULL,
  `DepartmentId` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1, 'Joe', 70000, 1);
INSERT INTO `employee` VALUES (2, 'Jim', 90000, 1);
INSERT INTO `employee` VALUES (3, 'Henry', 80000, 2);
INSERT INTO `employee` VALUES (4, 'Sam', 60000, 2);
INSERT INTO `employee` VALUES (5, 'Max', 90000, 1);

SET FOREIGN_KEY_CHECKS = 1;

Department 表包含公司所有部門的資訊。

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES (1, 'IT');
INSERT INTO `department` VALUES (2, 'Sales');

SET FOREIGN_KEY_CHECKS = 1;

解題

方法：使用 JOIN 和 IN 語句
演算法

因為 Employee 表包含 Salary 和 DepartmentId 欄位，我們可以以此在部門內查詢最高工資。

SELECT
    DepartmentId, MAX(Salary)
FROM
    Employee
GROUP BY DepartmentId;

注意：有可能有多個員工同時擁有最高工資，所以最好在這個查詢中不包含僱員名字的資訊。

DepartmentId	MAX(Salary)
1	90000
2	80000

然後，我們可以把表 Employee 和 Department 連線，再在這張臨時表裡用 IN 語句查詢部門名字和工資的關係。

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;