按优先级为每个员工筛选对应status_id的单条记录

需求 #

从employee_leaves表中为每个employee_id提取唯一一条记录，仅筛选status_id为7、4、3的行，优先级顺序为 7 > 4 > 3：

• 优先选取status_id=7的行；
• 若该员工无status_id=7的记录，则选取status_id=4的行；
• 若仍无匹配，则选取status_id=3的行。

示例数据 #

预期结果 #

对于employee_id=7，应返回status_id=7的行（如id=4或id=27，可通过额外排序规则确定唯一结果）。

SQL解决方案 #

使用窗口函数ROW_NUMBER()实现优先级筛选，为每个员工的符合条件记录按优先级分配序号，最终取序号为1的记录：

WITH ranked_leaves AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY employee_id 
            ORDER BY 
                CASE status_id 
                    WHEN 7 THEN 1 
                    WHEN 4 THEN 2 
                    WHEN 3 THEN 3 
                END ASC
        ) AS rn
    FROM employee_leaves
    WHERE status_id IN (7, 4, 3)
)
SELECT id, employee_id, status_id
FROM ranked_leaves
WHERE rn = 1;

补充说明 #

• PARTITION BY employee_id：按员工ID分组，保证每个员工的记录独立处理；
• ORDER BY CASE ...：自定义排序逻辑，将status_id=7设为最高优先级，4次之，3最低；
• 若存在多条同最高优先级的记录（如示例中的id=4和id=27），可在ORDER BY中追加排序条件（如id ASC）来确定唯一结果：

  ORDER BY 
      CASE status_id 
          WHEN 7 THEN 1 
          WHEN 4 THEN 2 
          WHEN 3 THEN 3 
      END ASC,
      id ASC -- 取同优先级中ID最小的记录
  

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