Day05 查询出每个项目中经验最丰富(experience_years最大)的员工

题目

有如下两张表
Project 表:

Employee 表:

查询出每个项目中经验最丰富(experience_years最大)的员工，返回的结果如下：

说明：员工1和3是project_id为1中exprerience_years最丰富的,
而project_id为2的项目，员工id为1的是exprerience_years最丰富

数据准备

CREATE TABLE project(
project_id INT,
employee_id int
)
;
CREATE TABLE employee(
employee_id INT,
name VARCHAR(20),
experience_years INT
)
;
INSERT INTO project VALUES(1,1);
INSERT INTO project VALUES(1,2);
INSERT INTO project VALUES(1,3);
INSERT INTO project VALUES(2,1);
INSERT INTO project VALUES(2,4);
INSERT INTO employee VALUES(1,'Khaled',3);
INSERT INTO employee VALUES(2,'Ali',2);
INSERT INTO employee VALUES(3,'John',3);
INSERT INTO employee VALUES(4,'Doe',2);

解题思路

1. 本题主要考察表连接查询和排名函数RANK() 或 DENSE_RANK()

RANK()与DENSE_RANK()的本质区别是当排名重复时排名相同,后续排名是顺延还是按照真实排序

SELECT p.project_id,p.employee_id
,e.experience_years
,RANK() OVER(PARTITION BY p.project_id ORDER BY e.experience_years DESC) R
 FROM dbo.project p
LEFT JOIN dbo.employee e ON e.employee_id = p.employee_id

1. 查询R=1的就是工作经验比较丰富的

   SELECT t.project_id,t.employee_id FROM(
   SELECT p.project_id,p.employee_id
   ,e.experience_years
   ,RANK() OVER(PARTITION BY p.project_id ORDER BY e.experience_years DESC) R
   FROM dbo.project p
   LEFT JOIN dbo.employee e ON e.employee_id = p.employee_id
   )t
   WHERE t.R = 1