文章目录
- 力扣高频SQL 50题(基础版)第十七题
- 1075. 项目员工 I
- 题目说明
- 思路分析
- 实现过程
- 准备数据
- 实现方式
- 结果截图
力扣高频SQL 50题(基础版)第十七题
1075. 项目员工 I
题目说明
项目表 Project
:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
主键为 (project_id, employee_id)。
employee_id 是员工表 Employee 表的外键。
这张表的每一行表示 employee_id 的员工正在 project_id 的项目上工作。
员工表 Employee
:
±-----------------±--------+
| Column Name | Type |
±-----------------±--------+
| employee_id | int |
| name | varchar |
| experience_years | int |
±-----------------±--------+
主键是 employee_id。数据保证 experience_years 非空。
这张表的每一行包含一个员工的信息。
请写一个 SQL 语句,查询每一个项目中员工的 平均 工作年限,精确到小数点后两位。
以 任意 顺序返回结果表。
思路分析
实现过程
准备数据
Create table If Not Exists Project (project_id int, employee_id int)
Create table If Not Exists Employee (employee_id int, name varchar(10), experience_years int)
Truncate table Project
insert into Project (project_id, employee_id) values ('1', '1')
insert into Project (project_id, employee_id) values ('1', '2')
insert into Project (project_id, employee_id) values ('1', '3')
insert into Project (project_id, employee_id) values ('2', '1')
insert into Project (project_id, employee_id) values ('2', '4')
Truncate table Employee
insert into Employee (employee_id, name, experience_years) values ('1', 'Khaled', '3')
insert into Employee (employee_id, name, experience_years) values ('2', 'Ali', '2')
insert into Employee (employee_id, name, experience_years) values ('3', 'John', '1')
insert into Employee (employee_id, name, experience_years) values ('4', 'Doe', '2')
实现方式
with t1 as (select e.employee_id,e.experience_years,p.project_id from Employee e left join Project p
on p.employee_id=e.employee_id)
select project_id,round(avg(experience_years),2) average_years from t1 where project_id is not null
group by project_id;