[sql-04] 连续出现至少三次的数字

数据准备

CREATE TABLE `leecode_01` (`id` bigint not null  AUTO_INCREMENT,`num` int DEFAULT NULL COMMENT '用户名',primary key(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'leecode(连续出现3次的数字)'insert into leecode_01(num) values(12);
insert into leecode_01(num) values(12);
insert into leecode_01(num) values(12);
insert into leecode_01(num) values(13);
insert into leecode_01(num) values(13);
insert into leecode_01(num) values(14);
insert into leecode_01(num) values(14);
insert into leecode_01(num) values(14);

sql

select distinct(num) num
from(
select (id+rn) flag,num
from (select id,num,row_number() over(partition by num order by id desc) rn from leecode_01 l2 ) a
) b  group by num,flag having count(*)>=3

运行结果