SQL分组最大值

最新推荐文章于 2023-12-21 15:03:10 发布

原创最新推荐文章于 2023-12-21 15:03:10 发布 · 1.8k 阅读

1 ·

CC 4.0 BY-SA版权

我们不生产知识，我们只是互联网的搬运工

SQL 专栏收录该内容

21 篇文章

订阅专栏

本文介绍了一种SQL查询技巧，用于找出每个部门中拥有最高薪资的员工。通过使用子查询和连接操作，我们能够从Employee和Department表中筛选出所需信息。示例展示了如何在IT和Sales部门找到Max和Henry作为最高薪员工。

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| 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 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

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

题目来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/department-highest-salary
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

采用分组 topN 的做法

select a.Name as Department, b.Name as Employee, b.Salary 
from Department a join
(
    select a.Name, a.Salary, a.DepartmentId
    from Employee a
    where
    (
        select count(*)
        from Employee b
        where a.DepartmentId = b.DepartmentId
        and a.Salary < b.Salary
    ) < 1
) b
on a.Id = b.DepartmentId

方案二

select a.Name as Department, b.Name as Employee, b.Salary 
from Department a join
(
    select c.Name, c.Salary, c.DepartmentId
    from Employee c, 
    (
    	select DepartmentId, max(Salary) as Salary
    	from Employee
    	group by DepartmentId
    ) d
    where c.DepartmentId = d.DepartmentId 
    and c.Salary = d.Salary
) b
on a.Id = b.DepartmentId;