力扣1075(MySQL)-项目员工Ⅰ（简单）

题目：

项目表 Project： 

 员工表 Employee：

请写一个 SQL 语句，查询每一个项目中员工的 平均 工作年限，精确到小数点后两位。

查询结果的格式如下：

 

 

 解题思路：

建表语句：

Create table If Not Exists Project_1075 (project_id int, employee_id int);
Create table If Not Exists Employee_1075 (employee_id int, name varchar(10), experience_years int);
Truncate table Project_1075;
insert into Project_1075 (project_id, employee_id) values ('1', '1'), ('1', '2'), ('1', '3'),('2', '1'), ('2', '4');
Truncate table Employee_1075;
insert into Employee_1075 (employee_id, name, experience_years) values ('1', 'Khaled', '3'),('2', 'Ali', '2'),('3', 'John', '1'),('4', 'Doe', '2');

通过表联结获取各项目的工作年限，然后以项目id(project_id)分组，聚合函数计算平均年限，用round()来保留小数位数。

select project_id,round(avg(experience_years),2) as average_years
from project_1075 a
join employee_1075 b
on a.employee_id = b.employee_id
group by project_id;