求连续出现5次以上的值，并且取第5次所在id

--测试数据
CREATE TABLE temp1 (
    id INT PRIMARY KEY identity(1,1),
    num1 INT,
    num2 INT
);
insert into temp1 values( 11,51),( 12,52);
insert into temp1 values( 10,101),( 10,102),( 10,103),( 10,104),( 10,105),( 10,106),( 10,107);
insert into temp1 values( 13,53),( 14,54);
insert into temp1 values( 10,108),( 10,109),( 10,110);
insert into temp1 values( 15,55),( 16,56);
insert into temp1 values( 10,111),( 10,112),( 10,113),( 10,114),( 10,115),( 10,116),( 10,117);

--解决代码
（1）
    ;with t1 as (
    select *,id-row_number() over(partition by num1 order by id) x from temp1
)
select * from 
(
    select *,
    (
        count(1)  over(partition by x )
    ) as y,
    (
        row_number() over(partition by x order by id)
    ) as z
    from t1 a
) b
where  y>=5 and z=5

（2）
;with t1 as (
    select *,id - row_number() over(partition by num1 order by id) x from temp1
)
select * from 
(
    select *,
    (
        select count(1) from t1 where x=a.x
    ) as y,
    (
        select count(1) from t1 where x=a.x AND id <=a.id        
    ) as z
    from t1 a
) b
where  y>=5 and z=5