如何查询符合条件的上一条、当前及下一条数据库记录

需求说明 #

需要获取符合特定条件的上一条、当前及下一条记录：仅关注pattern_id=23587462的记录（可存在多条），当该记录的上一条machine_id与下一条machine_id相等时，提取这三条记录。

最初尝试的SQL语句 #

最初编写的SQL仅能筛选指定pattern_id的记录，无法获取目标记录对应的上、下上下文记录：

select SL.*, row_number() over (PARTITION BY Machine_id Order by machine_id, SS2k) as M
from SL
where pattern_id in (23587462,2879003)

优化后的SQL语句 #

在@shawnt00和@SelVazi的帮助下，通过CTE结合窗口函数lead()和lag()实现了需求，可精准筛选出目标记录及其符合条件的上、下记录：

with cte as (
    Select SL.*, 
           lead(machine_id,1) over (order by machine_id) as lead_mid, 
           lead(pattern_id,1) over (order by machine_id) as lead_pid,
           lag(machine_id,1) over (order by machine_id) as lag_mid, 
           lag(pattern_id,1) over (order by machine_id) as lag_pid,
           Case 
               when pattern_id in (23587462) then 'Current'
               when lead_pid in (23587462) and machine_id=lead_mid then 'Source'
               when lag_pid in (23587462) and machine_id=lag_mid then 'Loss'
           End as sourceloss
    from SL
)
Select * from cte 
where sourceloss is not Null;

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