[SQL智慧航行者] - 各部门前3高工资的员工

话不多说, 先看数据表信息.

数据表信息:

employee 表, 包含所有员工信息, 每个员工有其对应的 id, name, salary 和 departmentid.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+
1
2
3
4
5
6
7
8
9
10

department 表, 包含公司所有部门的信息, id表示部门的编号, name是部门名称.

+----+----------+
| id | name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
1
2
3
4
5
6

话不多说, 再看需求~

需求:

编写一个 SQL 查询，找出每个部门工资前三高的员工。上述表格返回结果如下:

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+
1
2
3
4
5
6
7
8
9

话不多说, 进行拆解~

拆解:

其实这个地方, 主要考察排序类窗口函数. 当然, 要明白三个排序的区别, 这个之前的一个题目和给整理相关内容介绍, 欢迎同学去参考~
我直接来吧, 今天被社会摆了一道, 兴致不太高. 请见谅~
还是老规矩, 创建数据表和插入数据见文章最后部分.

select t.department, t.employee, t.salary 
from (
    select 
        de.name as department,
        em.name as employee, 
        em.salary,  
        row_number() over(partition by de.name order by em.salary desc) as rk 
    from employee as em 
    left join department as de 
    on em.departmentid = de.id 
) as t 
where t.rk <= 3 
;
1
2
3
4
5
6
7
8
9
10
11
12
13

效果如下:

这里可以自己延伸一些其他排序规则的选择, 比如最高的很多个员工, 是只取三个呢还是全部取出来, 都是可以的, 看自己的题目需求即可~

最后给大家介绍一下我这边的创建数据表和插入数据的操作步骤, 想要自己测试的话, 可以参考:

CREATE TABLE employee (
    Id INT,
    Name VARCHAR(50),
    Salary INT,
    DepartmentId INT
);

INSERT INTO employee (Id, Name, Salary, DepartmentId)
VALUES (1, 'Joe', 70000, 1),
       (2, 'Henry', 80000, 2),
       (3, 'Sam', 60000, 2),
       (4, 'Max', 90000, 1),
       (5, 'Janet', 69000, 1),
       (6, 'Randy', 85000, 1);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE TABLE department (
    id INT,
    name VARCHAR(50)
);

INSERT INTO department (id, name)
VALUES (1, 'IT'),
       (2, 'Sales');
1
2
3
4
5
6
7
8