如何根据同ID下首条与第二条记录的匹配值删除用户回退操作对应行

核心思路 #

优先使用窗口函数实现，相比嵌套子查询性能更高、逻辑可读性更强，支持MySQL 8.0+、PostgreSQL、Oracle等所有主流支持窗口函数的数据库。

实现步骤 #

• 按ID字段分组，组内按操作时间升序排序，标记每条记录的行号
• 通过LEAD窗口函数提取同ID下下一条记录的value2值，无需额外表关联
• 过滤首条记录满足两个条件：首条value1 = 首条value2，且首条value1 = 下一条记录的value2
• 若需要同时返回对应ID的两条操作记录，可通过CTE匹配符合条件的ID后捞出全量对应行

代码示例 #

假设业务表名为user_operation_log，排序字段为操作时间operate_time，示例代码如下：

WITH ranked_operation AS (
    SELECT
        *,
        -- 同ID内按操作时间排序标记行号
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY operate_time ASC) AS row_num,
        -- 取同ID下一条记录的value2值
        LEAD(value2) OVER(PARTITION BY id ORDER BY operate_time ASC) AS next_row_value2
    FROM user_operation_log
)
-- 仅查询符合条件的首条记录使用以下语句
SELECT id, value1, value2, operate_time
FROM ranked_operation
WHERE row_num = 1
  AND value1 = value2
  AND value1 = next_row_value2;

-- 如需查询符合条件的ID对应的两条操作记录，替换为以下语句
/*
SELECT *
FROM user_operation_log
WHERE id IN (
    SELECT id
    FROM ranked_operation
    WHERE row_num = 1
      AND value1 = value2
      AND value1 = next_row_value2
)
ORDER BY id, operate_time ASC;
*/

低版本数据库兼容方案 #

如果使用不支持窗口函数的MySQL 5.x及更低版本，可改用子查询+自关联实现：

SELECT a.id, a.value1, a.value2, a.operate_time, b.value2 AS second_value2
FROM user_operation_log a
INNER JOIN user_operation_log b
    ON a.id = b.id
    AND b.operate_time > a.operate_time
    -- 确保b是a的下一条记录
    AND NOT EXISTS (
        SELECT 1
        FROM user_operation_log c
        WHERE c.id = a.id
          AND c.operate_time > a.operate_time
          AND c.operate_time < b.operate_time
    )
WHERE a.value1 = a.value2
  AND a.value1 = b.value2
GROUP BY a.id;

示例表参考 #

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