2021-05-07

最新推荐文章于 2021-07-05 01:14:33 发布

The Great Ant

最新推荐文章于 2021-07-05 01:14:33 发布

阅读量120

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/qq_37698495/article/details/116504786

版权

sql 专栏收录该内容

63 篇文章 0 订阅

订阅专栏

该博客介绍了一段SQL查询，用于从44_Project和44_Employee表中获取每个项目的员工平均工作年限，结果精确到小数点后两位。查询使用LEFT JOIN连接两个表，并通过GROUP BY对项目ID进行分组，最后应用AVG函数计算平均经验年限。

摘要由CSDN通过智能技术生成

项目员工

需求一：查询每一个项目中员工的平均工作年限，精确到小数点后两位。

展示效果：

project_id	average_years
1	2.00
2	2.50

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

最终SQL:

select 
      project_id ,
      round(avg(experience_years),2) as average_years
from 
      44_Project p
left join
      44_Employee e
on 
      p.employee_id = e.employee_id
group by 
      project_id
order by
      project_id;