SQL每日一题(20201126)查询出最大连续失败场次数大于3的记录

SQL每日一题(20210513)
SQL每日一题(20210823)
SQL每日一题(20220228)

with temp as (
select *, id-row_number () over (PARTITION BY 结果 ORDER BY id) as fz
from t1126),temp1 as (
select *,row_number () over (PARTITION BY fz ORDER BY id) sf,COUNT(fz) over (PARTITION BY fz) as sfcs
from temp)
select id,场次,结果 from temp1 where sfcs>3

with c as (select *, 场次 - row_number() over (order by 场次) as result
           from (select * from T1126 where 结果 = '败' order by 场次) a)
select *
from c
where result in (select result from c group by result having count(result) >= 3)

select  ta.id, ta.场次, ta.结果
from T1126 ta left
         join(select id, 结果 as 结果, min(id) as minid, max(id) as maxid
              from (select id,
                           场次,
                           结果
                    from (select id,
                                 场次,
                                 结果,
                                 lead(场次, 1) over (partition by 结果 order by ID) as leadr,
                                 lag(场次, 1) over (partition by 结果 order by ID)  as lagr from T1126) t
                    where t.场次 = t.leadr - 1
                      and t.场次 = t.lagr + 1
                      and t.结果 = '败') a group by 结果,id) tb on ta.结果 = tb.结果
where ta.id = tb.minid - 1
   or ta.id = tb.maxid + 1
   or ta.id = tb.id
group by ta.id, ta.场次, ta.结果
order by ta.id;