5，SQL训练之： 力扣 1075，项目员工

目录

一，原题力扣链接

二，题干

三，建表语句

四，分析

一，窗口函数

二，拆拆拆

三，合 直接一步到位

五，SQL解答

解法一：开窗

解法二，拆拆拆 

解法三。合，一步到位

六，验证

七，知识点总结

---

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

项目表 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 语句，查询每一个项目中员工的 平均 工作年限，精确到小数点后两位。

以 任意 顺序返回结果表。

查询结果的格式如下。

示例 1:

输入：
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

三，建表语句

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')

四，分析

第一个表是项目表 有项目id 和项目员工id
第二个表是员工表 有员工详细信息  员工id 员工姓名 员工工作年限

题目要求 求出不同项目的 员工平均年限

解体思路一
拼接两个表 以员工id作为链接条件
以项目id分组  求出该项目下的员工 平均工作年限
最后在最平均值四舍五入一下

三种解法 拆和合

一，窗口函数

二，拆拆拆

1，拼接后只要我们想要的字段

2，对该字段进行分组 聚合 求平均值 然后在对这个值 四舍五入一下

三，合 直接一步到位

五，SQL解答

解法一：开窗

-- 用开窗 在四舍五入 然后去重
select
    distinct project_id,
    round(avg(experience_years) over (partition by project_id),2) average_years
    from employee e1,project p1 where e1.employee_id=p1.employee_id;

解法二，拆拆拆 

-- 常规解法
with t1 as (
    select project_id,experience_years from project p1  join employee e1 on p1.employee_id=e1.employee_id
    )
select project_id,round(avg(experience_years),2) as average_years from t1 group by project_id;

解法三。合，一步到位

-- 一条解法
select project_id,round(avg(experience_years),2) average_years
from project p1
    join employee e1 on p1.employee_id=e1.employee_id
group by project_id;

六，验证

开窗

常规

一步到位

七，知识点总结

1，内连接的运用
2，分组，聚合 求平均数
3，四舍五入函数 round的运用
4，只要聚合就可以开窗
5，distinct 去重的运用