Leetcode Database: #184 Department Highest Salary

文章目录

1. 1. 题目
2. 2. 解题思路&一种答案
   1. 2.1. 第一步
   2. 2.2. 第二步
   3. 2.3. 第三步
3. 3. 注意事项
4. 4. 构造测试数据

题目

#184 Department Highest Salary

解题思路&一种答案

第一步

这题比较麻烦，我们慢慢分步解决，首先是找出各组最大的值，很容易想到下面的方法。

SELECT 
    MAX(salary) AS maxSalary, DepartmentId
FROM
    Employee
GROUP BY DepartmentId

其结果为：

maxSalary	DepartmentId
90000	1
80000	2

需要注意的是标准 SQL 中，含有 GROUP BY 的语句中 SELECT 的列只能包含聚合函数（例如 MAX） 和在 GROUP BY 中的列。由于 MySQL 对这个规则进行了扩展不会报错，但例如你再 SELECT Name 列就会得到奇怪的结果，maxSalary 对应的 Name 很可能不对，除非 Name 和 DepartmentId 的组合只有一种。MySQL Handling of GROUP BY 中是这么说的：

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

第二步

接着再在上一步结果中加上部门名称一列，由于涉及到两个表我们使用 INNER JOIN：

SELECT 
    b.Name AS Department, MAX(salary) AS Salary, DepartmentId
FROM
    Employee a
        INNER JOIN
    Department b ON a.DepartmentId = b.Id
GROUP BY DepartmentId

结果是

Department	Salary	DepartmentId
IT	90000	1
Sales	80000	2

第三步

我们还需要对应的人名，那就再和 Employee 表 INNER JOIN 一次，找出符合上一步条件的人。

-- Runtime: 982 ms
SELECT 
    t.Department, e.Name, e.Salary
FROM
    Employee e
        INNER JOIN
    (SELECT 
        b.Name AS Department, MAX(salary) AS Salary, DepartmentId
    FROM
        Employee a
    INNER JOIN Department b ON a.DepartmentId = b.Id
    GROUP BY DepartmentId) t ON e.DepartmentId = t.DepartmentId
        AND e.Salary = t.Salary

最后就是预期的正确结果：

Department	Name	Salary
Sales	Henry	80000
IT	Max	90000
IT	Tom	90000

注意事项

• 最高工资有多个人时，需要把所有人都查询出来
• 对应的 DepartmentId 行不存在

构造测试数据

CREATE TABLE IF NOT EXISTS Employee (
    Id INT,
    Name VARCHAR(20),
    Salary INT,
    DepartmentId INT
);

CREATE TABLE IF NOT EXISTS Department (
    Id INT,
    Name VARCHAR(20)
);

DELETE FROM Employee;
DELETE FROM Department;

INSERT INTO Employee VALUES
(1, 'Joe'  , 70000, 1),
(2, 'Henry', 80000, 2),
(3, 'Sam'  , 60000, 2),
(4, 'Max'  , 90000, 1),
(5, 'Tom'  , 90000, 1);

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

预期结果：

Department	Name	Salary
Sales	Henry	80000
IT	Max	90000
IT	Tom	90000

---

本博客微信公众号