力扣SQL笔试题100道,点此免费下载!!!

题目描述

(通过次数1,909 | 提交次数2,963,通过率64.43%)

表: Employees
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
| salary      | int     |
+-------------+---------+
employee_id 是这个表的主键.
这个表格的每一行包含雇员ID(employee_id), 雇员姓名(name)和雇员工资(salary)信息.

这家公司想要将工资相同的雇员划分到同一个队伍中。每个队伍需要满足如下要求:
每个队伍需要由至少两个雇员组成。
同一个队伍中的所有雇员的工资相同。
工资相同的所有雇员必须被分到同一个队伍中。
如果某位雇员的工资是独一无二的,那么它不被分配到任何一个队伍中。
队伍ID的设定基于这支队伍的工资相对于其他队伍的工资的排名,即工资最低的队伍满足team_id = 1。注意,排名时不需要考虑没有队伍的雇员的工资。
编写一个 SQL查询来获取每一个被分配到队伍中的雇员的team_id 。

返回的结果表按照team_id升序排列。如果相同,则按照employee_id升序排列。

查询结果格式如下例。
示例 1:
输入:
Employees 表:
+-------------+---------+--------+
| employee_id | name    | salary |
+-------------+---------+--------+
| 2           | Meir    | 3000   |
| 3           | Michael | 3000   |
| 7           | Addilyn | 7400   |
| 8           | Juan    | 6100   |
| 9           | Kannon  | 7400   |
+-------------+---------+--------+
输出:
+-------------+---------+--------+---------+
| employee_id | name    | salary | team_id |
+-------------+---------+--------+---------+
| 2           | Meir    | 3000   | 1       |
| 3           | Michael | 3000   | 1       |
| 7           | Addilyn | 7400   | 2       |
| 9           | Kannon  | 7400   | 2       |
+-------------+---------+--------+---------+
解释:
Meir (employee_id=2) 和 Michael (employee_id=3) 在同一个队伍中,因为他们的工资都是3000。
Addilyn (employee_id=7) 和 Kannon (employee_id=9) 在同一个队伍中,因为他们的工资都是7400。
Juan (employee_id=8) 不在任何一个队伍中,因为他的工资为6100,是独一无二的(即:没有人和他的工资相同)。
队伍ID按照如下方式分配(基于工资排名,较低的排在前面):
- team_id=1: Meir 和 Michael, 工资是3000
- team_id=2: Addilyn 和 Kannon, 工资是7400
Juan的工资(6100)没有被计算在排名中,因为他不属于任何一个队伍。

来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/group-employees-of-the-same-salary
//测试数据
Create table If Not Exists Employees (employee_id int, name varchar(30), salary int);

insert into Employees (employee_id, name, salary) values ('2', 'Meir', '3000');
insert into Employees (employee_id, name, salary) values ('3', 'Michael', '3000');
insert into Employees (employee_id, name, salary) values ('7', 'Addilyn', '7400');
insert into Employees (employee_id, name, salary) values ('8', 'Juan', '6100');
insert into Employees (employee_id, name, salary) values ('9', 'Kannon', '7400');

解题思路

题目描述中,对生成的队伍要求的挺多,其实简单来说,只有以下3点:
第一点:将工资相同的人分到同一队伍,工资不同的人分到不同队伍;
第二点:每个队伍至少有2人;
第三点:按每个队伍的工资高低,从低到高开始编号。编号从1开始;
对于第二点来说,如果某个工资金额只有1个人,那么这个人不应该出现在任何队伍中。可以使用WHERE条件,将工资金额只有1个人的记录过滤掉。
对于第一、三点来说,实际上是一个开窗排名的场景。只是这里是全表开窗,按工资排序,并且排名要求是连续的。根据要求,使用dense_rank开窗排序非常合适。

参考SQL

未特别说明的情况下,参考SQL为基于MySQL8.0实现。
select
    a.employee_id,
    a.name,
    a.salary,
    dense_rank() over(order by a.salary) team_id
from Employees a
where exists(
    select 1 from Employees b
    where a.employee_id <> b.employee_id
    and a.salary = b.salary
)
order by 4,1;

力扣SQL笔试题100道,点此免费下载!!!

picture loss