如何删除多条出勤记录？我已经尝试过几个查询来删除未处理的数据。还要验证状态标识

 0 = Time In and 1 = Time Out

这是示例Fiddle

预期输出：

user_id        verify_date           verify_state   prev_state  next_state      TO_Date
14           2019-11-15 07:29:00         0               1         1       2019-11-15 19:12:00
15           2019-11-15 22:12:00         0               1         1       2019-11-16 06:29:00

我尝试过的示例查询

delete from tbl_excel_attendance where concat(User_id,verify_date) in (select  concat(User_id,verify_date)  from
(select
A.*,LAG(A.Verify_State) OVER (PARTITION BY A.User_id ORDER BY A.User_id,A.verify_date) as Prv_row_State,LEAD(A.Verify_State) OVER (PARTITION BY A.User_id ORDER BY A.User_id,A.verify_date) as nxt_row_State,LEAD(A.verify_date) OVER (PARTITION BY A.User_id ORDER BY A.User_id,A.verify_date)  as TO_Date

from tbl_excel_attendance as A ) as X
where
(X.Prv_row_State = 1 and X.verify_state = 1 and  X.nxt_row_State = 0) OR
(X.Prv_row_State = 1 and X.verify_state = 1 and  X.nxt_row_State = 1 and TIMESTAMPDIFF(Hour,X.verify_date,X.TO_Date)< 12.01) OR
(X.Prv_row_State = 0 and X.verify_state = 1 and  X.nxt_row_State = 1 and TIMESTAMPDIFF(Hour,X.TO_Date)< 12.01) OR
(X.Prv_row_State = 0 and X.verify_state = 0 and  X.nxt_row_State = 1) OR
(X.Prv_row_State = 0 and X.verify_state = 0 and  X.nxt_row_State = 0) OR
(X.Prv_row_State is null and X.verify_state = 1 and  X.nxt_row_State = 1))

根据我的分析删除了上座人数

编辑：他们的生物特征识别是门禁，因此，如果他们需要外出，则需要点击生物特征识别。

用户ID 15是夜班。