- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
目录
一,原题力扣链接
二,题干
员工表:
Employee
+---------------+---------+ | Column Name | Type | +---------------+---------+ | employee_id | int | | team_id | int | +---------------+---------+ employee_id 字段是这张表的主键(具有唯一值的列) 表中的每一行都包含每个员工的 ID 和他们所属的团队。编写解决方案以求得每个员工所在团队的总人数。
返回结果表 无顺序要求 。
返回结果格式示例如下:
示例 1:
输入: Employee Table: +-------------+------------+ | employee_id | team_id | +-------------+------------+ | 1 | 8 | | 2 | 8 | | 3 | 8 | | 4 | 7 | | 5 | 9 | | 6 | 9 | +-------------+------------+ 输出: +-------------+------------+ | employee_id | team_size | +-------------+------------+ | 1 | 3 | | 2 | 3 | | 3 | 3 | | 4 | 1 | | 5 | 2 | | 6 | 2 | +-------------+------------+ 解释: ID 为 1、2、3 的员工是 team_id 为 8 的团队的成员, ID 为 4 的员工是 team_id 为 7 的团队的成员, ID 为 5、6 的员工是 team_id 为 9 的团队的成员。
三,建表语句
Create table If Not Exists Employee (employee_id int, team_id int);
Truncate table Employee;
insert into Employee (employee_id, team_id) values ('1', '8');
insert into Employee (employee_id, team_id) values ('2', '8');;
insert into Employee (employee_id, team_id) values ('3', '8');
insert into Employee (employee_id, team_id) values ('4', '7');
insert into Employee (employee_id, team_id) values ('5', '9');
insert into Employee (employee_id, team_id) values ('6', '9');
四,分析
题解:
表: 员工表
字段:员工id,员工团队id
求每个员工所在的团队总数
第一步开窗 求出每个团队的总人数:
select
employee_id,team_id,
count(employee_id) over(partition by team_id) rn
from employee order by employee_id
第二步,去对应的2列,然后改名即可
五,SQL解答
with t1 as (
select
employee_id,team_id,
count(employee_id) over(partition by team_id) rn
from employee order by employee_id
)
select employee_id,rn as team_size from t1;
六,验证
七,知识点总结
- 开窗函数的运用 count()over()
- 子查询的运用
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用