SQL大神带你飞 | 24春招京东SQL真题解析-更新员工信息表

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

这道题目要求我们结合两个表 EMPLOYEE_INFO 和 EMPLOYEE_UPDATE，输出最新的员工职位信息和更新时间。我们需要根据更新表中的最新更新日期来决定是否更新员工信息表中的职位和日期。

题目分析

1. 表结构：

   • EMPLOYEE_INFO（旧表）：包含员工的当前职位和最后一次职位信息更新时间。
   • EMPLOYEE_UPDATE（更新表）：包含员工的职位更新记录，包括更新日期和新职位。

2. 目标：

   • 对于每个员工，找到更新表中最晚的更新日期。
   • 如果更新表中的最晚更新日期晚于员工信息表中的最后更新时间，则使用更新表中的职位和时间。
   • 否则，使用员工信息表中的职位和时间。

3. 输出要求：

   • 输出结果按照员工编号 EMPLOYEE_ID 升序排序。

知识点分析：

• ROW_NUMBER() 窗口函数、JOIN操作、CASE 语句

解答步骤

1. 获取更新表中每个员工的最新更新记录：

• 首先，我们要找到更新表中的最晚更新日期，可以使用窗口函数 ROW_NUMBER() 按员工编号 EMPLOYEE_ID 分区，并按 UPDATE_DT 降序排列，获取每个员工的最新更新记录。

 ROW_NUMBER() OVER (PARTITION BY EMPLOYEE_ID ORDER BY UPDATE_DT DESC) AS rk,

• 而后，我们使用where检索出rk=1的数据，即为最新更新记录。

WHERE 
    rk = 1

2. 连接两个表：

• 将 EMPLOYEE_INFO 表与更新表中最新的记录进行连接，使用 EMPLOYEE_ID 作为连接条件。

JOIN (
   --子查询
) u ON i.EMPLOYEE_ID = u.EMPLOYEE_ID

3. 选择最新的职位和更新时间：

• 使用 CASE 语句比较 EMPLOYEE_INFO 表中的 LAST_UPDATE_DT 和更新表中的 UPDATE_DT。
  • 如果 LAST_UPDATE_DT 大于等于 UPDATE_DT，则选择 EMPLOYEE_INFO 表中的职位和时间。
  • 否则，选择更新表中的职位和时间。

	CASE 
        WHEN i.LAST_UPDATE_DT >= u.UPDATE_DT THEN i.POSITION 
        ELSE u.NEW_POSITION 
    END AS POSITION,
    CASE 
        WHEN i.LAST_UPDATE_DT >= u.UPDATE_DT THEN i.LAST_UPDATE_DT 
        ELSE u.UPDATE_DT 
    END AS LAST_UPDATE_DT

4. 排序输出：

• 按 EMPLOYEE_ID 升序排序输出结果。

ORDER BY
    EMPLOYEE_ID ASC;

完整代码

SELECT
    i.EMPLOYEE_ID,
    CASE 
        WHEN i.LAST_UPDATE_DT >= u.UPDATE_DT THEN i.POSITION 
        ELSE u.NEW_POSITION 
    END AS POSITION,
    CASE 
        WHEN i.LAST_UPDATE_DT >= u.UPDATE_DT THEN i.LAST_UPDATE_DT 
        ELSE u.UPDATE_DT 
    END AS LAST_UPDATE_DT
FROM 
    EMPLOYEE_INFO i
JOIN (
    SELECT
        EMPLOYEE_ID,
        ROW_NUMBER() OVER (PARTITION BY EMPLOYEE_ID ORDER BY UPDATE_DT DESC) AS rk,
        UPDATE_DT,
        NEW_POSITION
    FROM
        EMPLOYEE_UPDATE
) u ON i.EMPLOYEE_ID = u.EMPLOYEE_ID
WHERE 
    rk = 1
ORDER BY
    EMPLOYEE_ID ASC;

部分同类题目推荐

• 牛客每个人最近的登录日期（一）

  • 题目链接：牛客每个人最近的登录日期（一）
  • 知识点：ROW_NUMBER() 窗口函数、PARTITION BY、ORDER BY、JOIN。

• 牛客每个人最近的登录日期（二）

  • 题目链接：牛客每个人最近的登录日期（二）
  • 知识点：ROW_NUMBER() 窗口函数、PARTITION BY、ORDER BY、JOIN。

• 牛客每个人最近的登录日期（三）

  • 题目链接：牛客每个人最近的登录日期（三）
  • 知识点：CASE 函数、MAX 函数、GROUP BY。

• 获取有奖金的员工相关信息

  • 题目链接：获取有奖金的员工相关信息
  • 知识点：CASE 函数、WHERE 条件过滤、ORDER BY。

牛客题库里还有海量同类型宝藏题目，正等着大家去探索挖掘哦ღ( ´･ᴗ･` )~