clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1305662 fiddles created (20275 in the last week).

create table tbl_excel_attendance( id int(11) NOT NULL AUTO_INCREMENT, user_id varchar(255) not null, verify_date datetime null, verify_state int not null, PRIMARY KEY (id) ); INSERT INTO tbl_excel_attendance (user_id,verify_date,verify_state) VALUES ('18','2019-11-07 22:50','1'), ('18','2019-11-07 22:49','1'), ('18','2019-11-07 22:48','0'), ('18','2019-11-07 22:46','0'), ('18','2019-11-07 12:37','0')
 hidden batch(es)


with cte1 as ( select *, coalesce(lag(verify_state) over (partition by user_id order by verify_date asc),1) prev_state, coalesce(lead(verify_state) over (partition by user_id order by verify_date asc),0) next_state from tbl_excel_attendance ), cte2 as ( select *, lead(verify_date) over (partition by user_id order by verify_date asc) next_date from cte1 where (prev_state,verify_state,next_state) in ( (1,0,0),(1,0,1), (0,1,0) ) ) select * from cte2 where verify_state = 0 and next_date is not null order by 2,3
id user_id verify_date verify_state prev_state next_state next_date
 hidden batch(es)