LeetCode 1075, 1076,1077 Project Employees

最新推荐文章于 2024-12-07 16:27:15 发布

原创最新推荐文章于 2024-12-07 16:27:15 发布 · 419 阅读

CC 4.0 BY-SA版权

本文通过SQL查询，展示了如何计算每个项目的员工平均工作经验，找出员工最多的项目，以及报告每个项目中经验最丰富的员工。这些查询涵盖了使用CTE、JOIN、GROUP BY、COUNT、AVG、RANK等SQL高级功能。

drop table Project
drop table Employee
Create table Project (project_id int, employee_id int)
Create table Employee (employee_id int, name varchar(20), experience_years int)

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

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

select * from Project
select * from Employee

1075. Project Employees I

Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.

select project_id,round(cast(AVG(e.experience_years) as float),2) as average_years
from Project p join Employee e 
on p.employee_id = e.employee_id
group by p.project_id

1076. Project Employees II

Write an SQL query that reports all the projects that have the most employees.

CTE

with cte as(
select project_id,count(employee_id) as people
from Project
group by project_id
),cte2 as
(select project_id,RANK()over(order by people desc) as rnk from cte
)select project_id from cte2 where rnk=1

Regular:

select project_id
from Project
group by project_id
having count(employee_id)=
(
select top 1 count(distinct employee_id) 
from Project 
group by project_id
)

1077. Project Employees III

Write an SQL query that reports the most experienced employees in each project. In case of a tie, report all employees with the maximum number of experience years.

CTE

with cte as(
select p.project_id,e.employee_id,RANK() over(partition by p.project_id order by e.experience_years desc) as rnk
from Project p join Employee e
on p.employee_id=e.employee_id
)select project_id,employee_id from cte where rnk=1