如何用同表中关联前序记录的dateon列更新currentdate列

你的表记录了同code下的ID变更历史，需求是将每条记录的currentdate更新为同组内下一条记录的dateon值（最后一条无后续记录则保持NULL）。原SQL出现随机结果的原因是：当存在多条满足b.code = a.code AND b.oldid = a.newid的记录时，数据库会随机选取一条赋值，导致结果不可控。

方案1：使用LEAD窗口函数（推荐） #

LEAD()窗口函数可以直接获取同组内下一行的指定字段值，语法简洁且避免关联歧义，支持SQL Server、PostgreSQL、MySQL 8.0+等主流数据库。

通用子查询写法 #

UPDATE Table t
SET currentdate = (
    SELECT LEAD(dateon) OVER (PARTITION BY code ORDER BY Id)
    FROM Table
    WHERE Id = t.Id
);

PostgreSQL专属写法 #

UPDATE Table
SET currentdate = next_dateon
FROM (
    SELECT 
        Id,
        LEAD(dateon) OVER (PARTITION BY code ORDER BY Id) AS next_dateon
    FROM Table
) AS sub
WHERE Table.Id = sub.Id;

方案2：修正自关联UPDATE #

如果需使用自关联方式，需通过TOP 1或MIN(Id)确保仅匹配唯一的下一条记录：

SQL Server写法 #

UPDATE a
SET currentdate = b.dateon
FROM Table a
OUTER APPLY (
    SELECT TOP 1 dateon
    FROM Table b
    WHERE b.code = a.code AND b.oldid = a.newid
    ORDER BY b.Id
) b;

通用自关联写法 #

UPDATE a
SET a.currentdate = b.dateon
FROM Table a
LEFT JOIN Table b 
    ON b.code = a.code 
    AND b.oldid = a.newid
    AND b.Id = (SELECT MIN(Id) FROM Table WHERE code = a.code AND oldid = a.newid);

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