需求:用SQL查询字段中至少连续出现N次的数字
创建表:
create table LX_01
(
id int,
num int
);
插入数据:
insert into lx_01 (ID, NUM) values (100001, 1);
insert into lx_01 (ID, NUM) values (100002, 1);
insert into lx_01 (ID, NUM) values (100003, 1);
insert into lx_01 (ID, NUM) values (100004, 2);
insert into lx_01 (ID, NUM) values (100005, 1);
insert into lx_01 (ID, NUM) values (100006, 2);
insert into lx_01 (ID, NUM) values (100007, 2);
insert into lx_01 (ID, NUM) values (100008, 3);
insert into lx_01 (ID, NUM) values (100009, 4);
insert into lx_01 (ID, NUM) values (100010, 3);
结果展示:
方法一:用row_number() over()函数