clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601395 fiddles created (48001 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_type int not null, verify_state int not null, work_code int not null, PRIMARY KEY (id) ); INSERT INTO tbl_excel_attendance (user_id,verify_date,verify_type,verify_state,work_code) VALUES ('14','2019-11-13 07:46','1','0','0'), ('14','2019-11-12 14:49','1','0','0'), ('14','2019-11-12 14:05','1','0','0'), ('14','2019-11-12 13:39','1','0','0'), ('14','2019-11-12 07:38','1','0','0'), ('14','2019-11-11 20:05','1','1','0'), ('14','2019-11-11 19:57','1','1','0'), ('14','2019-11-11 16:09','1','0','0'), ('14','2019-11-11 14:13','1','0','0'), ('14','2019-11-11 13:30','1','0','0'), ('14','2019-11-11 09:26','1','0','0'), ('14','2019-11-11 07:20','1','0','0'), ('14','2019-11-11 07:20','1','1','0'), ('14','2019-11-08 17:38','1','1','0'), ('14','2019-11-08 07:18','1','0','0'), ('14','2019-11-07 20:09','1','1','0'), ('14','2019-11-07 07:25','1','0','0'), ('14','2019-11-07 07:25','1','0','0'), ('14','2019-11-06 19:32','1','0','0'), ('14','2019-11-06 07:23','1','0','0'), ('14','2019-11-05 20:00','1','1','0'), ('14','2019-11-05 07:23','1','0','0'), ('15','2019-11-12 20:05','1','0','0'), ('15','2019-11-12 19:57','1','0','0'), ('15','2019-11-12 20:09','1','1','0'), ('15','2019-11-12 20:13','1','1','0'), ('15','2019-11-12 20:30','1','0','0'), ('15','2019-11-13 06:26','1','0','0'), ('15','2019-11-13 07:20','1','0','0'), ('15','2019-11-13 07:20','1','1','0')
 hidden batch(es)


SELECT * FROM tbl_excel_attendance
id user_id verify_date verify_type verify_state work_code
1 14 2019-11-13 07:46:00 1 0 0
2 14 2019-11-12 14:49:00 1 0 0
3 14 2019-11-12 14:05:00 1 0 0
4 14 2019-11-12 13:39:00 1 0 0
5 14 2019-11-12 07:38:00 1 0 0
6 14 2019-11-11 20:05:00 1 1 0
7 14 2019-11-11 19:57:00 1 1 0
8 14 2019-11-11 16:09:00 1 0 0
9 14 2019-11-11 14:13:00 1 0 0
10 14 2019-11-11 13:30:00 1 0 0
11 14 2019-11-11 09:26:00 1 0 0
12 14 2019-11-11 07:20:00 1 0 0
13 14 2019-11-11 07:20:00 1 1 0
14 14 2019-11-08 17:38:00 1 1 0
15 14 2019-11-08 07:18:00 1 0 0
16 14 2019-11-07 20:09:00 1 1 0
17 14 2019-11-07 07:25:00 1 0 0
18 14 2019-11-07 07:25:00 1 0 0
19 14 2019-11-06 19:32:00 1 0 0
20 14 2019-11-06 07:23:00 1 0 0
21 14 2019-11-05 20:00:00 1 1 0
22 14 2019-11-05 07:23:00 1 0 0
23 15 2019-11-12 20:05:00 1 0 0
24 15 2019-11-12 19:57:00 1 0 0
25 15 2019-11-12 20:09:00 1 1 0
26 15 2019-11-12 20:13:00 1 1 0
27 15 2019-11-12 20:30:00 1 0 0
28 15 2019-11-13 06:26:00 1 0 0
29 15 2019-11-13 07:20:00 1 0 0
30 15 2019-11-13 07:20:00 1 1 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_type verify_state work_code prev_state next_state next_date
22 14 2019-11-05 07:23:00 1 0 0 1 1 2019-11-05 20:00:00
20 14 2019-11-06 07:23:00 1 0 0 1 0 2019-11-07 20:09:00
15 14 2019-11-08 07:18:00 1 0 0 1 1 2019-11-08 17:38:00
12 14 2019-11-11 07:20:00 1 0 0 1 1 2019-11-11 07:20:00
11 14 2019-11-11 09:26:00 1 0 0 1 0 2019-11-12 07:38:00
24 15 2019-11-12 19:57:00 1 0 0 1 0 2019-11-12 20:30:00
27 15 2019-11-12 20:30:00 1 0 0 1 0 2019-11-13 07:20:00
 hidden batch(es)