如何利用另一张表的最新状态记录更新指定表的Status字段？

现有两张表：
TableA（当前状态表）：

Id  Status  User
1   15      111
2   15      111
3   15      111

TableB（状态变更历史表）：

Id  IdA Status  Date
1   1   10      2023-01-18
2   1   30      2022-12-18
3   3   30      2022-01-17
4   3   10      2022-01-16

需要更新TableA中User = 111的所有行：将Status替换为TableB中对应实体（IdA = TableA.Id）的最新状态变更记录里的Status值；若该实体在TableB中无变更记录，则保留原Status值。更新后预期结果：

Id  Status  User
1   10      111
2   15      111
3   30      111

解决方案 #

方法1：窗口函数筛选最新记录（支持PostgreSQL、SQL Server、MySQL 8.0+） #

用ROW_NUMBER()窗口函数给每个IdA的记录按日期倒序排名，取排名第1的最新记录，再关联TableA更新：

WITH LatestStatus AS (
    SELECT 
        IdA, 
        Status,
        ROW_NUMBER() OVER (PARTITION BY IdA ORDER BY Date DESC) AS rn
    FROM TableB
)
UPDATE TableA a
SET Status = ls.Status
FROM a
LEFT JOIN LatestStatus ls ON a.Id = ls.IdA
WHERE a.User = 111 AND ls.rn = 1;

方法2：子查询获取最新状态（兼容低版本数据库） #

如果数据库不支持窗口函数，可通过子查询先找到每个IdA的最新日期对应的状态：

-- MySQL/PostgreSQL 写法
UPDATE TableA a
SET Status = (
    SELECT b.Status
    FROM TableB b
    WHERE b.IdA = a.Id
    ORDER BY b.Date DESC
    LIMIT 1
)
WHERE a.User = 111
AND EXISTS (SELECT 1 FROM TableB b WHERE b.IdA = a.Id);

-- SQL Server 写法
UPDATE TableA a
SET Status = (
    SELECT TOP 1 b.Status
    FROM TableB b
    WHERE b.IdA = a.Id
    ORDER BY b.Date DESC
)
WHERE a.User = 111
AND EXISTS (SELECT 1 FROM TableB b WHERE b.IdA = a.Id);

-- Oracle 写法
UPDATE TableA a
SET Status = (
    SELECT b.Status
    FROM TableB b
    WHERE b.IdA = a.Id
    ORDER BY b.Date DESC
    FETCH FIRST 1 ROW ONLY
)
WHERE a.User = 111
AND EXISTS (SELECT 1 FROM TableB b WHERE b.IdA = a.Id);

关键说明 #

• 两种方法仅更新TableB中存在对应记录的行，无记录的行（如TableA的Id=2）保留原状态。
• 确保TableB的Date字段为日期类型，保证排序逻辑准确。

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