我看过很多其他问题,但没有一个完全适合我的问题,也无法为我提供所需的答案,也许我今天很慢:(

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]字段检查顺序以确定它们是否连续,来查找哪些InstructionDetailId连续失败(状态= 4)。因此,对于上面的InstructionDetailId 113和114,它们将是连续失败,因为它们的[Sequence]为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

谢谢大家!

最佳答案

您可以使用APPLY:

select t.*
from @t t outer apply
     ( select top (1) t1.*
       from @t t1
       where t1.InstructionId = t.InstructionId and
             t1.Sequence < t.Sequence
       order by t1.Sequence desc
     ) t1 outer apply
     ( select top (1) t2.*
       from @t t2
       where t2.InstructionId = t.InstructionId and
             t2.Sequence > t.Sequence
       order by t2.Sequence
     ) t2
where t.status = 4 and (t.status = t1.status or t.status = t2.status);

关于sql - SQL Server查找连续失败记录,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/52292615/

10-13 08:08