MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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')
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


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

id	user_id	verify_date	verify_type	verify_state
1	14	2019-11-13 07:46:00	1	0
2	14	2019-11-12 14:49:00	1	0
3	14	2019-11-12 14:05:00	1	0
4	14	2019-11-12 13:39:00	1	0
5	14	2019-11-12 07:38:00	1	0
6	14	2019-11-11 20:05:00	1	1
7	14	2019-11-11 19:57:00	1	1
8	14	2019-11-11 16:09:00	1	0
9	14	2019-11-11 14:13:00	1	0
10	14	2019-11-11 13:30:00	1	0
11	14	2019-11-11 09:26:00	1	0
12	14	2019-11-11 07:20:00	1	0
13	14	2019-11-11 07:20:00	1	1
14	14	2019-11-08 17:38:00	1	1
15	14	2019-11-08 07:18:00	1	0
16	14	2019-11-07 20:09:00	1	1
17	14	2019-11-07 07:25:00	1	0
18	14	2019-11-07 07:25:00	1	0
19	14	2019-11-06 19:32:00	1	0
20	14	2019-11-06 07:23:00	1	0
21	14	2019-11-05 20:00:00	1	1
22	14	2019-11-05 07:23:00	1	0
23	15	2019-11-12 20:05:00	1	0
24	15	2019-11-12 19:57:00	1	0
25	15	2019-11-12 20:09:00	1	1
26	15	2019-11-12 20:13:00	1	1
27	15	2019-11-12 20:30:00	1	0
28	15	2019-11-13 06:26:00	1	0
29	15	2019-11-13 07:20:00	1	0
30	15	2019-11-13 07:20:00	1	1