如何在SELECT查询中用LAG/LEAD或递归替换最高recordid的记录值

需求规则 #

• 针对每个personid，以**最高recordid**的记录为基准
• 若基准记录的leftresult或rightresult属于{1,2,12}，则依次检查次高、更低recordid的历史记录
• 当找到某条历史记录的bestear=1时，将基准记录中仍属于{1,2,12}的对应字段替换为该历史记录的字段值
• 若替换后字段仍属于{1,2,12}，继续向前查找，直到字段值不在目标集合或无符合条件的历史记录为止

表结构与测试数据 #

CREATE TABLE Prod_data_v2.data_meaareport (
    personid INT NULL,
    leftresult INT NULL,
    rightresult INT NULL,
    bestear INT NULL,
    recordid UNSIGNED NOT NULL
);

INSERT INTO Prod_data_v2.data_meaareport VALUES
(33122,1,1,0,2),
(33122,6,7,0,1),
(41228,1,1,0,3),
(41228,1,8,0,2),
(41228,6,7,0,1),
(45336,1,1,0,3),
(45336,NULL,8,1,2),
(45336,1,1,0,1),
(54122,NULL,2,1,2),
(54122,6,6,0,1),
(371339,2,2,0,4),
(371339,NULL,2,1,3),
(371339,3,3,0,2),
(371339,3,3,0,1);

解决方案SQL #

利用MySQL 8.0支持的递归CTE（公共表表达式）实现迭代查找替换：

WITH ranked_records AS (
    -- 给每个personid的记录按recordid降序排名，rn=1为最新记录
    SELECT 
        personid,
        leftresult,
        rightresult,
        bestear,
        recordid,
        ROW_NUMBER() OVER (PARTITION BY personid ORDER BY recordid DESC) AS rn
    FROM Prod_data_v2.data_meaareport
),
recursive_replacement AS (
    -- 初始数据集：取每个personid的最新记录
    SELECT 
        personid,
        leftresult AS final_left,
        rightresult AS final_right,
        rn AS current_rn
    FROM ranked_records
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归迭代：逐条查找历史记录并判断是否替换
    SELECT 
        r.personid,
        -- 处理leftresult替换逻辑
        CASE 
            WHEN r.final_left IN (1,2,12) AND rr.bestear = 1 THEN COALESCE(rr.leftresult, r.final_left)
            ELSE r.final_left
        END AS final_left,
        -- 处理rightresult替换逻辑
        CASE 
            WHEN r.final_right IN (1,2,12) AND rr.bestear = 1 THEN COALESCE(rr.rightresult, r.final_right)
            ELSE r.final_right
        END AS final_right,
        r.current_rn + 1 AS current_rn
    FROM recursive_replacement r
    JOIN ranked_records rr ON r.personid = rr.personid AND rr.rn = r.current_rn + 1
    -- 终止条件：当两个字段都不在目标集合时，停止递归
    WHERE r.final_left IN (1,2,12) OR r.final_right IN (1,2,12)
)
-- 取每个personid最后一次迭代的结果（完成所有替换后的最终值）
SELECT 
    personid,
    final_left,
    final_right
FROM (
    SELECT 
        personid,
        final_left,
        final_right,
        ROW_NUMBER() OVER (PARTITION BY personid ORDER BY current_rn DESC) AS rn
    FROM recursive_replacement
) t
WHERE rn = 1;

结果验证 #

执行上述SQL后，最终结果如下：

关键结果解释 #

• 45336：最新记录rightresult=1属于目标集合，匹配到次高记录（bestear=1），替换为8后不在目标集合，停止迭代
• 54122：最新记录rightresult=2属于目标集合，匹配到次高记录（bestear=1），替换为6后不在目标集合，停止迭代
• 371339：最新记录的左右结果均属于目标集合，次高记录（bestear=1）的rightresult=2仍在目标集合，后续历史记录bestear≠1，无法继续替换，最终保留原值

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