如何将filldubl表中rn=2行的mykey空值填充为前序rn=1行的值？

给定表结构及数据：

CREATE TABLE `filldubl` (
  `recid` int(11) NOT NULL AUTO_INCREMENT,
  `mykey` varchar(200) DEFAULT NULL,
  `id` varchar(10) DEFAULT NULL,
  `rn` int(11) DEFAULT 0,
  PRIMARY KEY (`recid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `filldubl` VALUES 
('1', '10.1109/ICLP56858.2022.9942468', '4762', '1'),
('2', null, '5556', '2'),
('3', '10.23919/ECC55457.2022.9838149', '4931', '1'),
('4', null, '9327', '2'),
('5', '223Nakamurar', '12033', '1'),
('6', null, '7720', '2'),
('7', '10.1016/j.cherd.2022.06.019', '4080', '1'),
('8', null, '6168', '2'),
('9', '10.1016/S0959-8049(22)00919-4', '367', '1'),
('10', null, '6775', '2');

需求是将所有rn=2且mykey为NULL的记录，填充为对应组内前序rn=1行的mykey值。

原语句的问题 #

你之前尝试的语句：

update filldubl a,filldubl b set a.mykey=b.mykey where a.recid=b.recid+1 and a.rn=2;

缺少b.rn=1的限制条件，若表中存在非组内的recid连续记录（比如某条rn=2的记录后跟着另一条rn=2的记录），就会错误关联；即使当前数据是严格连续的，该逻辑也没有明确绑定“同组”关系，不够严谨。

解决方案 #

方案1：使用窗口函数（MySQL 8.0+） #

利用LAG()窗口函数直接获取当前记录的前一条记录的mykey，适合MySQL 8.0及以上版本：

UPDATE filldubl
SET mykey = (
    SELECT lag_mykey
    FROM (
        SELECT 
            recid,
            LAG(mykey) OVER (ORDER BY recid) AS lag_mykey,
            rn
        FROM filldubl
    ) AS sub
    WHERE sub.recid = filldubl.recid
)
WHERE rn = 2 AND mykey IS NULL;

LAG(mykey) OVER (ORDER BY recid)会按recid顺序，为每条记录获取其前一条记录的mykey值，正好匹配每组rn=1到rn=2的对应关系。

方案2：关联子查询（兼容MySQL 5.x） #

如果使用的是不支持窗口函数的MySQL 5.x版本，可以通过子查询找到当前rn=2记录之前最近的rn=1记录：

UPDATE filldubl a
SET a.mykey = (
    SELECT b.mykey
    FROM filldubl b
    WHERE b.recid < a.recid
      AND b.rn = 1
    ORDER BY b.recid DESC
    LIMIT 1
)
WHERE a.rn = 2 AND a.mykey IS NULL;

子查询会筛选出当前记录之前所有rn=1的记录，按recid倒序取第一条，确保拿到的是对应组的前序rn=1记录。

方案3：修正原关联语句（适用于严格连续的组） #

如果可以确保每组的rn=1和rn=2记录是严格连续的recid（如给定的数据），只需给原语句加上b.rn=1的限制即可：

UPDATE filldubl a
JOIN filldubl b ON a.recid = b.recid + 1
SET a.mykey = b.mykey
WHERE a.rn = 2 AND a.mykey IS NULL AND b.rn = 1;

验证结果 #

执行上述任意语句后，查询表数据：

SELECT * FROM filldubl;

所有rn=2的记录的mykey都会被正确填充为对应组rn=1的mykey值。

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