mysql查询连续三天100以上_输出连续大于等于三天销售量大于100的日期

CREATE TABLE t_log(f_time DATE, f_value int)

insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-01', 100);

insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-02', 101);

insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-03', 102);

insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-04', 99);

insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-05', 105);

insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-06', 108);

insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-07', 105);

insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-08', 199);

insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-09', 108);

with a as(

select f_group,lead(f_group,1,'9999-01-01') over (order by f_group) as f_grouptwo from

(select F_TIME, F_VALUE,case when lag(f_value,1,0)over (order by f_time)<100 then f_time else null end f_group from t_log)t

where f_group is not null)

, b as(

select * from a

where datediff(dd,f_group,f_grouptwo)>=3)

select f_group,max(f_time) as maxtime

from b

inner join t_log c

on b.f_group<=c.f_time and b.f_grouptwo>c.f_time

where c.f_value>=100

group by f_group

现在的输出：

f_group maxtime

2016-10-01 2016-10-03

2016-10-05 2016-10-09

目标输出

2016-10-01

2016-10-02

2016-10-03

2016-10-05

2016-10-06

2016-10-07

2016-10-08

2016-10-09