Medium之1077.项目员工III

野性酷女 2022-10-19 14:59 167阅读 0赞

##### 项目表 Project： #####

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>project_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>employee_id</td> 
   <td>int</td> 
  </tr> 
 </tbody> 
</table>

(project\_id, employee\_id) 是这个表的主键  
employee\_id 是员工表 Employee 的外键

##### 员工表 Employee： #####

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>employee_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>name</td> 
   <td>varchar</td> 
  </tr> 
  <tr> 
   <td>experience_years</td> 
   <td>int</td> 
  </tr> 
 </tbody> 
</table>

employee\_id 是这个表的主键

### 问题 ###

写 一个 SQL 查询语句，报告在每一个项目中经验最丰富的雇员是谁。如果出现经验年数相同的情况，请报告所有具有最大经验年数的员工。

### 示例 ###

查询结果格式在以下示例中：

Project 表：

<table> 
 <thead> 
  <tr> 
   <th>project_id</th> 
   <th>employee_id</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>4</td> 
  </tr> 
 </tbody> 
</table>

Employee 表：

<table> 
 <thead> 
  <tr> 
   <th>employee_id</th> 
   <th>name</th> 
   <th>experience_years</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Khaled</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Ali</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>John</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Doe</td> 
   <td>2</td> 
  </tr> 
 </tbody> 
</table>

Result 表：

<table> 
 <thead> 
  <tr> 
   <th>project_id</th> 
   <th>employee_id</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>1</td> 
  </tr> 
 </tbody> 
</table>

employee\_id 为 1 和 3 的员工在 project\_id 为 1 的项目中拥有最丰富的经验。在 project\_id 为 2 的项目中，employee\_id 为 1 的员工拥有最丰富的经验。

来源：力扣（LeetCode）  
链接：https://leetcode-cn.com/problems/project-employees-iii  
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

### 解答：窗口函数大法 ###

select project_id,employee_id
    from 
        (select p.project_id,p.employee_id,
                dense_rank() over(partition by p.project_id order by e.experience_years desc) as mes
         from project p left join employee e using(employee_id)) t
    where mes = 1