导入数据:
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');
项目表 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 语句,查询每一个项目中员工的 平均 工作年限,精确到小数点后两位。
查询结果的格式如下:
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 |
+-----------------+----------+------------------------+Result 表:
+--------------+---------------------+
| project_id | average_years |
+--------------+---------------------+
| 1 | 2.00 |
| 2 | 2.50 |
+--------------+---------------------+
第一个项目中,员工的平均工作年限是 (3 + 2 + 1) / 3 = 2.00;第二个项目中,员工的平均工作年限是 (3 + 2) / 2 = 2.50
解题思路
1、使用group by对project_id分组统计。
2、使用AVG函数求平均值,使用ROUND函数保留小数位。
执行结果
代码
# Write your MySQL query statement below select p.project_id, ROUND(AVG(e.experience_years), 2) as average_years from Project p, Employee e where p.employee_id = e.employee_id group by p.project_id;
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/project-employees-i
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。