最近做了京东的24年春招题，题目如下： alt alt

题目分析

本题要求统计2024年上半年（2024-01-01 至 2024-07-01）启动的每个项目组内，员工的绩效分数及其在项目组内的排名，并输出员工ID、姓名、绩效分数、项目组内排名、部门和项目组名称。
输出字段：

  • employee_id（员工ID）
  • employee_name（员工姓名）
  • first_half_2024_score（2024年上半年绩效分数）
  • project_group_rank（项目组内绩效排名，分数高者优先，分数相同按员工ID升序）
  • department（部门）
  • project_group（项目组名称）

输出顺序：

  • 先按项目ID升序（p.project_id）
  • 再按项目组内排名升序（project_group_rank）
  • 最后按员工ID升序（e.employee_id）

涉及知识点：

  • SQL 多表连接（JOIN）
  • 条件筛选（WHERE）
  • 窗口函数（ROW_NUMBER() OVER PARTITION BY ... ORDER BY ...）
  • 字段别名与排序（ORDER BY）

解答步骤

1. 关联员工、绩效和项目表

  • 用 performance 绩效表与 employees 员工表通过员工ID连接，获取员工信息。
  • 再与 projects 项目表通过项目ID连接，获取项目名称。
from performance pe
join employees e on e.employee_id = pe.employee_id
join projects p on p.project_id = pe.project_id

2. 筛选2024年上半年启动的项目

  • 只保留项目启动时间在2024-01-01至2024-07-01之间的项目。
where p.start_date between '2024-01-01' and '2024-07-01'

3. 计算项目组内绩效排名

  • 用窗口函数 row_number() over(partition by p.project_id order by pe.performance_score desc, e.employee_id)，在每个项目组内按绩效分数降序排名，分数相同则员工ID小的排前。
row_number() over(partition by p.project_id order by pe.performance_score desc, e.employee_id) as project_group_rank

4. 输出与排序

  • 输出员工ID、姓名、绩效分数、项目组内排名、部门、项目组名称。
  • 按项目ID、项目组内排名、员工ID排序。
order by p.project_id, project_group_rank, e.employee_id

完整代码

select e.employee_id, e.employee_name,
pe.performance_score as first_half_2024_score,
row_number() over(partition by p.project_id order by pe.performance_score desc, e.employee_id) as project_group_rank,
e.department,
p.project_name as project_group
from performance pe
join employees e on e.employee_id = pe.employee_id
join projects p on p.project_id = pe.project_id
where p.start_date between '2024-01-01' and '2024-07-01'
order by p.project_id, project_group_rank, e.employee_id

近似题目练习推荐

每个顾客最近一次下单的订单信息

  • 知识点：窗口函数、分组排名、JOIN

查找入职员工时间升序排名的情况下的倒数第三的员工所有信息

  • 知识点：窗口函数、分组、排序

获取当前薪水第二多的员工的emp_no以及其对应的薪水

  • 知识点：窗口函数、分组、排序

如需更多类似题目，可在牛客网SQL练习区进行练习。