[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
运行结果
原文地址：https://blog.csdn.net/qq_36066039/article/details/142638653
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！