SQL Server查找连续失败记录

Question

我查看了很多其他问题，但没有一个完全适合我的问题或给我提供我需要的答案，也许我今天太慢了:(

DECLARE @t TABLE (
    [InstructionId] INT,
    [InstructionDetailId] INT,
    [Sequence] INT,
    [Status] INT
 ) 
 INSERT INTO @t SELECT 222,111,1, 2
 INSERT INTO @t SELECT 222,112,2,2
 INSERT INTO @t SELECT 222,113,3,4
 INSERT INTO @t SELECT 222,114,4,4
 INSERT INTO @t SELECT 222,115,5,2
 INSERT INTO @t SELECT 222,116,6,4
 INSERT INTO @t SELECT 222,117,7,2
 INSERT INTO @t SELECT 222,118,8,4
 INSERT INTO @t SELECT 222,119,9,4
 INSERT INTO @t SELECT 222,120,10,2
 INSERT INTO @t SELECT 222,121,11,2

我需要通过使用 [Sequence] 字段检查顺序以确定它们是否是连续的，来查找哪些指令详细信息 ID 存在连续失败(状态 = 4)。因此，对于上面的InstructionDetailId 113和114将是连续失败，因为它们的[序列]是3和4，同样对于InstructionDetailId 118和119将是连续失败。我已经尝试了很多行号变化和 cte，但我无法完全破解它:(顺便说一句，这是针对 SQL Server 2008 R2 的。

预期输出:

InstructionId   InstructionDetailId Sequence    Status
222                            113       3           4
222                            114       4           4
222                            118       8           4
222                            119       9           4

谢谢大家!

Answer 1

也许最简单的方法是使用 lag() 和 lead():

select t.*
from (select t.*,
             lag(t.status) over (partition by t.InstructionId order by t.sequence) as prev_status,
             lead(t.status) over (partition by t.InstructionId order by t.sequence) as next_status
      from @t t
     ) t
where status = prev_status or status = next_status;