如何修改SQL以按最高优先级获取每个候选人的单条lead数据

原问题背景 #

现有lead表结构及数据如下：

id,candidate_id,job_id,stage,created_at,updated_at
1,2,1,3,"2023-09-04 11:35:18","2023-09-04 11:53:12"
2,2,2,2,"2023-09-04 11:36:28","2023-09-05 02:13:13"
3,3,2,1,"2023-09-04 11:36:35","2023-09-04 11:36:35"
4,3,3,2,"2023-09-04 11:59:41","2023-09-05 00:52:29"
5,4,3,1,"2023-09-04 23:58:49","2023-09-04 23:58:49"
6,4,1,1,"2023-09-05 01:07:09","2023-09-05 01:07:09"

原SQL用于获取每个候选人updated_at最新的记录：

SELECT a.*
FROM lead a
LEFT JOIN lead b ON a.candidate_id = b.candidate_id AND b.updated_at > 
a.updated_at
WHERE b.id IS NULL 

现在需要修改SQL，获取每个候选人最高优先级（stage数值越大优先级越高）的记录，期望结果为id=1、4、6的三条数据。

方案1：修改LEFT JOIN条件（兼容老版本数据库） #

沿用原有的LEFT JOIN思路，将判断条件从比较updated_at改为优先比较stage，同时处理同stage下取最新updated_at的情况：

SELECT a.*
FROM lead a
LEFT JOIN lead b 
  ON a.candidate_id = b.candidate_id 
  AND (b.stage > a.stage OR (b.stage = a.stage AND b.updated_at > a.updated_at))
WHERE b.id IS NULL

逻辑说明：

• 对于每条记录a，关联同候选人的记录b
• 如果存在b的stage比a大，或者stage相同但updated_at更新，则a不是目标记录
• 最终筛选出不存在符合上述条件的b的记录，即为每个候选人的最高优先级（同优先级取最新）记录

方案2：使用窗口函数（现代SQL写法，更易读） #

利用ROW_NUMBER()窗口函数按候选人分组，先按stage降序排序，再按updated_at降序排序，取每组第一条：

SELECT id, candidate_id, job_id, stage, created_at, updated_at
FROM (
  SELECT 
    *,
    ROW_NUMBER() OVER (
      PARTITION BY candidate_id 
      ORDER BY stage DESC, updated_at DESC
    ) AS rn
  FROM lead
) t
WHERE rn = 1

逻辑说明：

• PARTITION BY candidate_id：按候选人ID分组
• ORDER BY stage DESC, updated_at DESC：每组内先按stage从大到小排序，同stage则按updated_at从新到旧排序
• ROW_NUMBER()为每组内的记录生成序号，rn=1即为每组的第一条（最高优先级/最新）记录

内容的提问来源于stack exchange，提问作者botjaeger