我有此查询“删除多人出席”，但仍然对此查询有疑问，我想知道哪一部分错了？这是我用来删除出勤的示例逻辑。 Sample Fiddle

我正在使用的查询：

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) OR
(X.Prv_row_State = 0 and X.verify_state = 0 and  X.nxt_row_State is null) OR
(X.Prv_row_State is null and X.verify_state = 0 and  X.nxt_row_State = 0) OR
(X.Prv_row_State = 1 and X.verify_state = 1 and  X.nxt_row_State is null)
)

还可以验证状态标识：

  0 = Time In and 1 = Time out

上面的输出查询：

id  user_id      verify_date        verify_type verify_state    work_code
1     14      2019-11-15 19:12:00         1           0            0
2     14     2019-11-15 19:12:00          1           1            0
3     14     2019-11-15 17:25:00          1           1            0

期望的输出：

 id     user_id          verify_date       verify_type  verify_state    work_code        
 2         14      2019-11-15 19:12:00           1             1            0    
17         14      2019-11-15 07:29:00           1             0            0