SQL中使用join关联查找历史前值更新emp表address字段的方案

实现逻辑 #

• 先对emphistory员工历史表按EId、AccountId两个字段分组，分组内部按Date字段降序排序，最新的记录行号为1，次新的历史记录行号为2，对应需求要取的历史前值
• 筛选出每个分组内行号为2的记录后，和emp员工表通过EId、AccountId两个字段关联，更新emp表的Address字段即可

通用SQL实现（支持MySQL 8.0+、SQL Server、Oracle等支持窗口函数的数据库） #

UPDATE emp e
INNER JOIN (
    SELECT 
        EId,
        AccountId,
        Address
    FROM (
        SELECT 
            EId,
            AccountId,
            Address,
            ROW_NUMBER() OVER (PARTITION BY EId, AccountId ORDER BY `Date` DESC) AS rn
        FROM emphistory
    ) t
    WHERE rn = 2
) eh ON e.EId = eh.EId AND e.AccountId = eh.AccountId
SET e.Address = eh.Address;

PostgreSQL版本实现 #

UPDATE emp e
SET Address = eh.Address
FROM (
    SELECT 
        EId,
        AccountId,
        Address
    FROM (
        SELECT 
            EId,
            AccountId,
            Address,
            ROW_NUMBER() OVER (PARTITION BY EId, AccountId ORDER BY "Date" DESC) AS rn
        FROM emphistory
    ) t
    WHERE rn = 2
) eh
WHERE e.EId = eh.EId AND e.AccountId = eh.AccountId;

执行上述SQL后，emp表中John对应的Address字段就会被更新为历史记录中的USA，符合需求。

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