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
