【LC中等】1077. 项目员工 III 窗口函数

❤️博客主页: 楚生辉
❤️系列专栏:【LeetCode刷题】
❤️一句短话: 坚持不懈，孜孜不倦

1.题目描述

项目表 Project：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| project_id  | int     |
| employee_id | int     |
+-------------+---------+
(project_id, employee_id) 是这个表的主键
employee_id 是员工表 Employee 的外键

员工表 Employee：

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+------------------+---------+
employee_id 是这个表的主键

写 一个 SQL 查询语句，报告在每一个项目中经验最丰富的雇员是谁。

如果出现经验年数相同的情况，请报告所有具有最大经验年数的员工。

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

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   | 3                |
| 4           | Doe    | 2                |
+-------------+--------+------------------+

Result 表：
+-------------+---------------+
| project_id  | employee_id   |
+-------------+---------------+
| 1           | 1             |
| 1           | 3             |
| 2           | 1             |
+-------------+---------------+
employee_id 为 1 和 3 的员工在 project_id 为 1 的项目中拥有最丰富的经验。在 project_id 为 2 的项目中，employee_id 为 1 的员工拥有最丰富的经验。

2.代码实现

• 每个项目的最长工龄 ，再组合id 工龄

SELECT
	a.project_id,
	a.employee_id 
FROM
	Project a
	LEFT JOIN Employee b ON a.employee_id = b.employee_id 
WHERE
	( a.project_id, b.experience_years ) IN (
        SELECT
        a.project_id,
        max( b.experience_years ) 
        FROM
        Project a
        LEFT JOIN Employee b ON a.employee_id = b.employee_id 
        GROUP BY
        project_id)

• 使用窗口函数

SELECT
	project_id,
	employee_id 
FROM
	(
        SELECT
        a.project_id,
        a.employee_id,
        dense_rank() over ( PARTITION BY a.project_id ORDER BY b.experience_years DESC ) rk 
        #每一行都有分组里的排序情况
        FROM
        project a
        JOIN employee b ON a.employee_id = b.employee_id 
    ) t 
WHERE
	rk =1

ㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤ

如图，我们想要筛选出标红的数据，我们发现，只需要根据a.project_id进行分组，然后对b.experience_years进行排序，相同的放在同一每名次，最后再嵌套一层查询，查询排名为1的，也就可以将每个分组的最有经验的数据查询出来