PostgreSQL查询需求：获取每位员工的最新晋升记录

问题背景 #

我们有三张业务表：employee（员工信息）、rank（职级信息）、promotion（晋升记录），需要实现：查询employee的所有记录，同时关联每位员工**最新（最大晋升日期）**的晋升记录；对于没有晋升记录的员工，对应晋升字段显示NULL。

给定的表结构与测试数据如下：

create table employee(id int, name varchar(50), fname varchar(50)); 
create table rank (id int, name varchar(50)); 
create table promotion(
    id int, 
    dt date, 
    from_rank_id int, 
    to_rank_id int, 
    employee_id int, 
    constraint fk_pro_emp foreign key(employee_id) references employee(id),
    constraint fk_pro_rank_f foreign key(from_rank_id) references rank(id),
    constraint fk_pro_rank_t foreign key(to_rank_id) references rank(id)
); 

insert into employee values(1, 'John', 'Roy'), (2, 'Kane', 'Williamson'), (3, 'Yasin', 'Khan'), (4, 'Dwayne', 'Brain'); 
insert into rank values(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E'); 

-- 同一员工多条晋升记录
insert into promotion values(1, '2010-01-01', 1, 2, 1), (2, '2015-01-01', 2, 3, 1); 
-- 第二位员工三次晋升
insert into promotion values(4, '2011-11-23', 1, 2, 2), (5, '2012-04-05', 2, 3, 2), (6, '2013-12-30', 3, 4, 2); 
-- 第三位员工一次晋升
insert into promotion values(7, '2015-10-21', 3, 4, 3); 
-- 第四位员工无晋升记录

期望输出格式：

EMP_ID Name      Father_Name Pro_Date   From_Rank To_Rank
1      John      Roy         2015-01-01 B         C
2      Kane      Williamson  2013-12-30 C         D
3      Yasin     Khan        2015-10-21 C         D
4      Dwayne    Brain       <Null>     <Null>    <Null>

方案1：使用窗口函数（推荐，简洁高效） #

利用ROW_NUMBER()窗口函数，按员工ID分组，对每组的晋升记录按日期降序排序，取排序后第一条（即最新晋升），再关联员工表和职级表：

SELECT
    e.id AS EMP_ID,
    e.name AS Name,
    e.fname AS Father_Name,
    p.dt AS Pro_Date,
    r_from.name AS From_Rank,
    r_to.name AS To_Rank
FROM employee e
LEFT JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY dt DESC) AS rn
    FROM promotion
) p ON e.id = p.employee_id AND p.rn = 1
LEFT JOIN rank r_from ON p.from_rank_id = r_from.id
LEFT JOIN rank r_to ON p.to_rank_id = r_to.id
ORDER BY e.id;

优势：逻辑直观，代码简洁，在MySQL 8+、PostgreSQL、SQL Server等现代数据库中都支持，执行效率较高。

方案2：子查询获取最大晋升日期（兼容性强） #

先通过子查询找出每个员工的最大晋升日期，再关联晋升表拿到对应记录，最后关联职级表：

SELECT
    e.id AS EMP_ID,
    e.name AS Name,
    e.fname AS Father_Name,
    p.dt AS Pro_Date,
    r_from.name AS From_Rank,
    r_to.name AS To_Rank
FROM employee e
LEFT JOIN (
    SELECT employee_id, MAX(dt) AS max_dt
    FROM promotion
    GROUP BY employee_id
) p_max ON e.id = p_max.employee_id
LEFT JOIN promotion p ON p.employee_id = p_max.employee_id AND p.dt = p_max.max_dt
LEFT JOIN rank r_from ON p.from_rank_id = r_from.id
LEFT JOIN rank r_to ON p.to_rank_id = r_to.id
ORDER BY e.id;

优势：兼容所有支持基本SQL语法的数据库，包括MySQL 5.x等旧版本。

关键说明 #

两种方案都使用LEFT JOIN保证无晋升记录的员工也会被纳入结果集，对应晋升字段自动显示NULL，完全匹配需求。

内容的提问来源于stack exchange，提问作者Abdul Raheem Ghani