如何计算同分类连续行的字符串差异并修复数据库中重复拼接的评论字段

嘿，这个历史数据修复的需求很常见，我来给你一步步搞定！核心思路就是针对每个分类下的每一行，拿到它上一行的评论内容，然后提取当前行比上一行新增的部分，完全重复的就留空。

核心逻辑 #

我们需要用窗口函数 LAG() 来获取同分类下上一行的评论，然后通过字符串处理对比当前行和上一行的内容：

1. 分类的第一行（没有上一行）：直接保留原评论
2. 当前行和上一行评论完全相同：返回空字符串
3. 当前行是上一行的拼接扩展：截取上一行长度之后的部分，去掉前导空格（因为示例里是用空格分隔追加的）

方案1：先查询验证结果（推荐先跑这个确认正确性） #

以下代码适用于**MySQL 8.0+、PostgreSQL、SQL Server 2012+**等支持窗口函数的数据库，假设你的表名为 comments：

WITH ranked_comments AS (
    SELECT 
        ID,
        Category_ID,
        comment,
        -- 按分类分区，按ID排序，获取上一行的评论
        LAG(comment) OVER (PARTITION BY Category_ID ORDER BY ID) AS prev_comment
    FROM comments
)
SELECT 
    ID,
    Category_ID,
    CASE
        -- 分类第一行，直接保留原内容
        WHEN prev_comment IS NULL THEN comment
        -- 和上一行完全相同，返回空
        WHEN comment = prev_comment THEN ''
        -- 截取新增部分，TRIM去掉前导空格
        ELSE TRIM(SUBSTRING(comment, LENGTH(prev_comment) + 1))
    END AS comment
FROM ranked_comments
ORDER BY ID;

运行这段代码就能得到你期望的修复后数据，先确认结果符合要求再执行更新操作。

方案2：直接更新原表 #

如果验证结果正确，可以用下面的代码直接修改原表：

MySQL 版本

WITH ranked_comments AS (
    SELECT 
        ID,
        Category_ID,
        comment,
        LAG(comment) OVER (PARTITION BY Category_ID ORDER BY ID) AS prev_comment
    FROM comments
)
UPDATE comments c
JOIN ranked_comments rc ON c.ID = rc.ID
SET c.comment = CASE
        WHEN rc.prev_comment IS NULL THEN rc.comment
        WHEN rc.comment = rc.prev_comment THEN ''
        ELSE TRIM(SUBSTRING(rc.comment, LENGTH(rc.prev_comment) + 1))
    END;

PostgreSQL 版本

WITH ranked_comments AS (
    SELECT 
        ID,
        CASE
            WHEN LAG(comment) OVER (PARTITION BY Category_ID ORDER BY ID) IS NULL THEN comment
            WHEN comment = LAG(comment) OVER (PARTITION BY Category_ID ORDER BY ID) THEN ''
            ELSE TRIM(SUBSTRING(comment FROM LENGTH(LAG(comment) OVER (PARTITION BY Category_ID ORDER BY ID)) + 1))
        END AS new_comment
    FROM comments
)
UPDATE comments c
SET comment = rc.new_comment
FROM ranked_comments rc
WHERE c.ID = rc.ID;

边界情况说明 #

• 如果某一行的评论不是严格基于上一行拼接的（比如中间有内容删减或跳变），这个方法会提取出差异部分，如果你有这类异常数据，可能需要额外的逻辑处理，但根据你的示例场景，这个方案完全适用。
• 评论中的前导/尾随空格会被TRIM处理，不影响增量内容的提取。

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