如何在数据修改历史表中筛选满足条件的行及其相邻行？

嘿，我来帮你搞定这个需求！针对你这张记录修改历史的表，我们可以用SQL的窗口函数或者自连接的方式来实现——既筛选出符合特定条件的行，同时把它们的上、下相邻行也一起捞出来。

首先明确前提：这里的「相邻行」我们默认按修改时间（Last_Modif）的先后顺序定义（毕竟历史记录的时间顺序才是核心逻辑，ID可能和时间不一定完全正相关）。下面给你两种常用的实现方法：

方法一：用窗口函数（推荐，简洁高效） #

窗口函数LAG和LEAD可以轻松拿到每行的上一行和下一行ID，非常适合这种场景。假设你的表名叫history_table，我们以筛选User_Modif = 'John'的行为例（你可以把这个条件换成你需要的特定规则）：

WITH ranked_history AS (
    SELECT 
        *,
        -- 拿到当前行的上一行ID（按修改时间倒序，最新的在前）
        LAG(ID) OVER (ORDER BY Last_Modif DESC) AS prev_id,
        -- 拿到当前行的下一行ID
        LEAD(ID) OVER (ORDER BY Last_Modif DESC) AS next_id
    FROM history_table
),
target_records AS (
    -- 筛选出满足条件的行，同时带上它们的上下行ID
    SELECT 
        ID, prev_id, next_id
    FROM ranked_history
    WHERE User_Modif = 'John' -- 这里替换成你的特定条件
)
-- 取出所有目标行、上一行、下一行的数据，去重避免重复
SELECT DISTINCT h.*
FROM history_table h
JOIN target_records t ON h.ID IN (t.ID, t.prev_id, t.next_id)
ORDER BY h.Last_Modif DESC;

代码说明： #

1. ranked_history：给每一行标记出它在时间序列中的上一行和下一行ID，用Last_Modif DESC是因为历史记录通常最新的在前；如果需要按时间正序（最早的在前），把DESC改成ASC即可。
2. target_records：筛选出你需要的目标行，同时拿到它们的上下行ID。
3. 最后关联原表，取出所有相关ID的行，用DISTINCT去重——比如如果两个相邻行都是目标行，它们的上下行ID会重复，去重后就不会出现重复数据。

方法二：用自连接（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如一些老版本的MySQL），可以用自连接+子查询的方式实现：

SELECT DISTINCT h.*
FROM history_table h
WHERE 
    -- 情况1：当前行就是满足条件的目标行
    h.User_Modif = 'John'
    -- 情况2：当前行是某个目标行的上一行（按时间倒序）
    OR EXISTS (
        SELECT 1 FROM history_table t
        WHERE t.User_Modif = 'John'
        AND t.Last_Modif = (SELECT MIN(Last_Modif) FROM history_table WHERE Last_Modif > h.Last_Modif)
    )
    -- 情况3：当前行是某个目标行的下一行（按时间倒序）
    OR EXISTS (
        SELECT 1 FROM history_table t
        WHERE t.User_Modif = 'John'
        AND t.Last_Modif = (SELECT MAX(Last_Modif) FROM history_table WHERE Last_Modif < h.Last_Modif)
    )
ORDER BY h.Last_Modif DESC;

代码说明： #

通过EXISTS子查询判断当前行是否是某个目标行的上一行或下一行——子查询里通过找比当前行时间晚/早的最小/最大时间，来定位相邻行。

注意点 #

• 边界处理：如果目标行是时间序列的第一行（最新的），它没有下一行；如果是最后一行（最早的），没有上一行，这两种情况代码都会自动忽略不存在的相邻行，不会报错。
• 条件替换：把代码里的User_Modif = 'John'换成你实际需要的筛选条件就行，比如Last_Modif >= '2018-04-09'之类的。

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