【SQL】每个部门工资最高的员工

目录

语法

需求

示例

分析

代码

---

语法

子查询（Subquery）是一种强大的特性，允许在另一个查询中嵌套一个或多个SQL查询。子查询可以是任何SELECT、INSERT、UPDATE或DELETE语句，最常见的是SELECT语句。子查询的结果可以作为另一个查询的条件、值或表来使用，从而实现复杂的查询逻辑。

• 变量子查询：那些只返回一个单一值的子查询称之为标量子查询，比如这样： SELECT * FROM t1 WHERE m1 = (SELECT MIN(m2) FROM t2);
• 行子查询：就是返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）。比如这样： SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);
• 列子查询：列子查询自然就是查询出一个列的数据喽，不过这个列的数据需要包含多条记录（只包含一条记录就成了标量子查询了）。比如这样 SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
• 表子查询：SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);   其中的(SELECT m2, n2 FROM t2)就是一个表子查询，这里需要和行子查询对比一下，行子查询中我们用了LIMIT 1来保证子查询的结果只有一条记录，表子查询中不需要这个限制。

需求

表： Employee

+--------------+---------+
| 列名          | 类型    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
在 SQL 中，id是此表的主键。
departmentId 是 Department 表中 id 的外键（在 Pandas 中称为 join key）。
此表的每一行都表示员工的 id、姓名和工资。它还包含他们所在部门的 id。

表： Department

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
在 SQL 中，id 是此表的主键列。
此表的每一行都表示一个部门的 id 及其名称。

查找出每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
查询结果格式如下例所示。

示例

示例 1:

输入：
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         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
| IT         | Max      | 90000  |
+------------+----------+--------+
解释：Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。

分析

查找出每个部门中薪资最高的员工。

查找每个部门中薪资最高的员工，首先需要分组。

对 DepartmentId 字段分组查询最大值，得到不同 DepartmentId 下的最大值

select DepartmentId, max( Salary ) 
        from Employee 
        group by DepartmentId

再根据 DepartmentId 字段连接 Department 表，根据 Salary 和 DepartmentId 查找 Department. Name 字段

where
    Employee.DepartmentId = Department.Id 
    and ( Employee.DepartmentId, Salary ) 
    in (select DepartmentId, max( Salary ) 
        from Employee 
        group by DepartmentId )

代码

select
	Department.NAME as Department,
	Employee.NAME as Employee,
	Salary 
from
	Employee,
	Department 
where
	Employee.DepartmentId = Department.Id 
	and ( Employee.DepartmentId, Salary ) 
    in (select DepartmentId, max( Salary ) 
        from Employee 
        group by DepartmentId )