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

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

关键字：求在某列连续出现N次值的的数据，并且取第M次出现所在行

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

 

SQL SERVER:

--测试数据
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

 

 

 

mysql

（1）临时表方法
CREATE TABLE test1 (
    id INT PRIMARY KEY auto_increment,
    num1 INT,
    num2 INT
);
insert into test1 values(null,11,51),(null,12,52);
insert into test1 values(null,10,101),(null,10,102),(null,10,103),(null,10,104),(null,10,105),(null,10,106),(null,10,107);

insert into test1 values(null,13,53),(null,14,54);
insert into test1 values(null,10,108),(null,10,109),(null,10,110);
insert into test1 values(null,15,55),(null,16,56);
insert into test1 values(null,10,111),(null,10,112),(null,10,113),(null,10,114),(null,10,115),(null,10,116),(null,10,117);


CREATE  TABLE test2 like test1;
alter table test2 change id id int;
alter table test2 add rn int unique auto_increment;
insert into test2(id,num1,num2) select * from test1 where num1=10;

select *,id-rn as x from test2;

select * from (
select *,
    (select count(1) from (select *,id-rn as x from test2) t where t.x=t1.x) y,
    (select count(1) from (select *,id-rn as x from test2) t where t.x=t1.x and t.id <= t1.id) z
from (select *,id-rn as x from test2) t1
) t
where y>=5 and z=5

（2）构造row_number()方法
　　

select * from (
select *,
(select count(1) from (select *,id-rn as x from (select test1.*, @num:=@num+1 as rn from test1 join (select @num:=0) temp1 where test1.num1=10) temp2) t where t.x=t1.x) y,
(select count(1) from (select *,id-rn as x from (select test1.*,@num1:=@num1+1 as rn from test1 join (select @num1:=0) temp1 where test1.num1=10) temp2 ) t where t.x=t1.x and t.id <= t1.id) z
from (select *,id-rn as x from (select test1.*,@num2:=@num2+1 as rn from test1 join (select @num2:=0) temp1 where test1.num1=10) temp2) t1
) t
where y>=5 and z=5

 

--再简化

select * from (
select *,
(select count(1) from (select test1.*, id-@num:=@num+1 as x from test1 join (select @num:=0) temp1 where test1.num1=10) t where t.x=t1.x) y,
(select count(1) from (select test1.*,id-@num1:=@num1+1 as x from test1 join (select @num1:=0) temp1 where test1.num1=10) t where t.x=t1.x and t.id <= t1.id) z
from (select test1.*,id-@num2:=@num2+1 as x from test1 join (select @num2:=0) temp1 where test1.num1=10) t1
) t
where y>=5 and z=5

 原表数据：

　　

结果：