MySQL中连续出现多次的问题

找出所有至少连续出现三次的数字。

Create table If Not Exists Logs (id int, num int)
Truncate table Logs
insert into Logs (id, num) values ('1', '1')
insert into Logs (id, num) values ('2', '1')
insert into Logs (id, num) values ('3', '1')
insert into Logs (id, num) values ('4', '2')
insert into Logs (id, num) values ('5', '1')
insert into Logs (id, num) values ('6', '2')
insert into Logs (id, num) values ('7', '2')

 

 

1、多张表相连

select distinct s1.num as 'ConsecutiveNums'
from Logs  as s1,Logs as s2,Logs as s3
where s1.id=s2.id+1 and s2.id=s3.id +1
and s1.num=s2.num and s2.num=s3.num 

2、窗口函数

select distinct num as 'ConsecutiveNums'
from (
  select num
,lead(num,1) over() as 'num1'
,lead(num,2) over() as 'num2'
from Logs
) as s1
where num=num1 and num1 =num2

函数	功能
lag(字段名,N,默认值) over(partition by …order by …)	向下窗口函数lead：取出字段名所在的列，向下N行的数据，作为独立的列
lead(字段名,N,默认值) over(partition by …order by …)	向上窗口函数lag：取出字段名所在的列，向上N行的数据，作为独立的

每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

Create table If Not Exists Stadium (id int, visit_date DATE NULL, people int)
Truncate table Stadium
insert into Stadium (id, visit_date, people) values ('1', '2017-01-01', '10')
insert into Stadium (id, visit_date, people) values ('2', '2017-01-02', '109')
insert into Stadium (id, visit_date, people) values ('3', '2017-01-03', '150')
insert into Stadium (id, visit_date, people) values ('4', '2017-01-04', '99')
insert into Stadium (id, visit_date, people) values ('5', '2017-01-05', '145')
insert into Stadium (id, visit_date, people) values ('6', '2017-01-06', '1455')
insert into Stadium (id, visit_date, people) values ('7', '2017-01-07', '199')
insert into Stadium (id, visit_date, people) values ('8', '2017-01-09', '188')

 

 

with t1 as(
    select *,id - row_number() over(order by id) as rk
    from stadium
    where people >= 100
)
select id,visit_date,people
from t1
where rk in(
    select rk
    from t1
    group by rk
    having count(rk) >= 3
)