1075. 项目员工 I 1076. 项目员工II1077. 项目员工 III

---

1075. 项目员工 I

---

• inner join = join [交集]

• 涉及到排名问题首先考虑***rank()、dense_rank()、row_number()***函数等
  rank() -------------->1,2,2,4,5,5,5,8
  dense_rank()----->1,2,2,3,4,4,4,5
  row_number()---->为每一种类分配单独的排名（每一种类都有自己的第一名）（每一种类排名同rank()函数一致）

• 注意partition by 和group by的使用时机 有join的推荐使用partition by
  partition by和group by的区别和对比

# Write your MySQL query statement below
select P.project_id , (round(sum(experience_years)/count(*),2))average_years
from Project P inner join Employee E
on P.employee_id = E.employee_id
group by P.project_id

---

1076. 项目员工II

---

# Write your MySQL query statement below
# select project_id ,count(employee_id)
# from Project
# group by project_id
# #有排名的首推rank()函数等
select project_id
from(
select project_id ,
rank()over(order by (count(employee_id))desc)  weici
from Project
group by project_id
)new_table
where weici =1

---

1077. 项目员工 III

---

# Write your MySQL query statement below
select project_id ,employee_id
from(
select  P.project_id , P.employee_id ,
dense_rank()over(partition by P.project_id order by experience_years desc) weici
from Project P left join Employee E
on P.employee_id = E.employee_id
)new_table
where weici = 1