1 题目描述
项目表 Project:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
主键为 (project_id, employee_id)
employee_id 是员工表 `Employee 表的外键.
员工表 Employee:
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| employee_id | int |
| name | varchar |
| experience_years | int |
+------------------+---------+
主键是 employee_id
请写一个 SQL 语句, 查询每一个项目中员工的 平均 工作年限, 精确到小数点后两位
2 测试用例
输入:
Project 表:
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
+-------------+-------------+
Employee 表:
+-------------+--------+------------------+
| employee_id | name | experience_years |
+-------------+--------+------------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 1 |
| 4 | Doe | 2 |
+-------------+--------+------------------+
输出:
+-------------+---------------+
| project_id | average_years |
+-------------+---------------+
| 1 | 2.00 |
| 2 | 2.50 |
+-------------+---------------+
第一个项目中, 员工的平均工作年限是 (3 + 2 + 1) / 3 = 2.00
第二个项目中, 员工的平均工作年限是 (3 + 2) / 2 = 2.50
3 解题思路
- 使用外连接将
Project和Employee表关联, 对project_id分组统计, 计算每个项目员工的平均工作年限, 并保留小数点两位
select p.project_id, round(avg(e.experience_years), 2) as average_years
from Project as p
inner join Employee as e on p.employee_id = e.employee_id
group by p.project_id;
查询结果
+----------+-------------+
|project_id|average_years|
+----------+-------------+
|1 |2.00 |
|2 |2.50 |
+----------+-------------+
