如何根据指示器在表中选择顶部2行

Question

我有这样的示例数据

Declare @table Table 
(
  ID INT,
  Value VARCHAR(10),
  Is_failure int
)
insert into @table(ID, Value, Is_failure) values (1, 'Bits', 0)
insert into @table(ID, Value, Is_failure) values (2, 'Ip', 0)
insert into @table(ID, Value, Is_failure) values (3, 'DNA', 0)
insert into @table(ID, Value, Is_failure) values (6, 'DCP', 1)
insert into @table(ID, Value, Is_failure) values (8, 'Bits', 0)
insert into @table(ID, Value, Is_failure) values (11, 'calc', 0)
insert into @table(ID, Value, Is_failure) values (14, 'DISC', 0)
insert into @table(ID, Value, Is_failure) values (19, 'DHCP', 1)

看起来像这样：

ID  Value   Is_failure
1   Bits    0
2   Ip      0
3   DNA     0
6   DCP     1
8   Bits    0
11  calc    0
14  DISC    0
19  DHCP    1

这样的数据连续...我需要在is_failure = 1时拿到iS_failure的前2个记录。

样本输出：

ID  Value   Is_failure
2   Ip      0
3   DNA     0
6   DCP     1
11  calc    0
14  DISC    0
19  DHCP    1

建议我尝试使用having count(*)和其他事物，但并不富有成果。

Answer 1

您可以使用此查询

Declare @tmptable Table 
(
ID INT,
Value VARCHAR(10),
Is_failure int,
rowNum int
)
Declare @continuousRows int =2
insert into @tmptable
select *,ROW_NUMBER() over (order by id) from @table
;with cte1 as
(select *
from @tmptable t
where (select sum(Is_failure) from @tmptable t1 where t1.rowNum between t.rowNum-@continuousRows and t.rowNum
having count(*)=@continuousRows+1)=1
and t.Is_failure=1
) 
,cte2 as
(
select t.* from @tmptable t
join cte1 c on t.rowNum between c.rowNum-@continuousRows and c.rowNum
)
select c.ID,value,Is_failure from cte2   c

Answer 2

您可以使用窗口函数：

select id, value, is_failure
from (select t.*,
             lead(Is_failure) over (order by id) as next_if,
             lead(Is_failure, 2) over (order by id) as next_if2
      from @table t
     ) t
where 1 in (Is_failure, next_if, next_if2)
order by id;

您可以使用窗口子句简化此内容：

select id, value, is_failure
from (select t.*,
             max(is_failure) over (order by id rows between current row and 2 following) as has_failure
      from @table t
     ) t
where has_failure > 0
order by id;