试试这个：

SELECT 
    *, 
    TIMEDIFF(exit_time,entry_time) as duration
FROM
    (
        SELECT 
            Employee, 
            ac_date as entry_date,
            ac_date as exit_date,
            MIN(CASE WHEN ac_event=1 THEN ac_time END) entry_time,
            MAX(CASE WHEN ac_event=0 THEN ac_time END) exit_time
        FROM
            AC
        GROUP BY 
            Employee,
            ac_date
    ) as t

可以使用相关子查询来获取入口/出口对：

SELECT employee, ac_date AS entry_date,
       (SELECT ac_date 
       FROM AC AS i
       WHERE i.ac_event = 0 
             AND i.employee = t.employee 
             AND i.ac_date >= t.ac_date 
             AND i.ac_time >= t.ac_time
       ORDER BY i.ac_date, i.ac_time LIMIT 1) AS exit_date,
       ac_time AS entry_time,       
       (SELECT ac_time
       FROM AC AS i
       WHERE i.ac_event = 0 
             AND i.employee = t.employee 
             AND i.ac_date >= t.ac_date 
             AND i.ac_time >= t.ac_time
       ORDER BY i.ac_date, i.ac_time LIMIT 1) AS exit_time       
FROM AC AS t
WHERE t.ac_date BETWEEN '2018-05-12' AND '2018-05-13' AND t.ac_event = 1;

输出：

employee entry_date  exit_date   entry_time  exit_time
-------------------------------------------------------
2032     2018-05-12  2018-05-12  08:52:00    11:39:33
2032     2018-05-12  2018-05-12  11:48:06    11:52:54
2032     2018-05-12  2018-05-12  11:59:54    12:23:40
2032     2018-05-13  2018-05-13  08:34:43    09:02:25
2032     2018-05-13  2018-05-13  09:12:16    11:45:21
2032     2018-05-13  2018-05-13  12:50:40    12:52:16

然后可以使用timestampdiff来计算每次进入-退出之间的时间：

SELECT employee, entry_date, exit_date, entry_time, exit_time, 
        SEC_TO_TIME(TIMESTAMPDIFF(SECOND,
                                  CONCAT(entry_date, ' ', entry_time), 
                                  CONCAT(exit_date, ' ', exit_time))) AS duration
FROM (
SELECT employee, ac_date AS entry_date,
       (SELECT ac_date 
       FROM AC AS i
       WHERE i.ac_event = 0 
             AND i.employee = t.employee 
             AND i.ac_date >= t.ac_date 
             AND i.ac_time >= t.ac_time
       ORDER BY i.ac_date, i.ac_time LIMIT 1) AS exit_date,
       ac_time AS entry_time,       
       (SELECT ac_time
       FROM AC AS i
       WHERE i.ac_event = 0 
             AND i.employee = t.employee 
             AND i.ac_date >= t.ac_date 
             AND i.ac_time >= t.ac_time
       ORDER BY i.ac_date, i.ac_time LIMIT 1) AS exit_time       
FROM AC AS t
WHERE t.ac_date BETWEEN '2018-05-12' AND '2018-05-13' AND t.ac_event = 1) AS x

此处演示

另一个选项是使用双连接到同一个表以获取退出记录：

SELECT t1.employee, 
       t1.ac_date AS entry_date,
       t1.ac_time AS entry_time,
       t2.ac_date AS exit_date,
       t2.ac_time AS exit_time,
       SEC_TO_TIME(TIMESTAMPDIFF(SECOND,
                                  CONCAT(t1.ac_date, ' ', t1.ac_time), 
                                  CONCAT(t2.ac_date, ' ', t2.ac_time))) AS duration       

/* fetches entry records */
FROM AC AS t1       

/* fetches exit records that occur after the correlated entry record */
INNER JOIN AC AS t2 

   ON t1.employee = t2.employee AND t2.ac_event = 0
      AND CONCAT(t1.ac_date, ' ', t1.ac_time) <= CONCAT(t2.ac_date, ' ', t2.ac_time) 

/* fetches exit records that occur between t1, t2 records */
LEFT JOIN AC AS t3  
   ON t2.employee = t3.employee AND t3.ac_event = 0 
      AND CONCAT(t3.ac_date, ' ', t3.ac_time) >= CONCAT(t1.ac_date, ' ', t1.ac_time) 
      AND CONCAT(t3.ac_date, ' ', t3.ac_time) < CONCAT(t2.ac_date, ' ', t2.ac_time) 
WHERE t1.ac_date BETWEEN '2018-05-12' AND '2018-05-13' 
      AND t1.ac_event = 1 
      AND t3.employee IS NULL /* There is no record between t1 entry and t2 exit */

此处演示

这是原始查询的简化版本：

select AC.employee, AC.ac_date, AC.ac_time,
   min(AC2.ac_time) as exit_time,
   timediff(min(AC2.ac_time), AC.ac_time)
from AC
left join AC as AC2
  on AC2.ac_date = AC.ac_date 
 and AC2.ac_time > ac.ac_time
 and AC2.ac_event = 0
where AC.ac_event = 1
  AND AC.ac_date >= '2018-05-11'
  AND AC.ac_date <= '2018-05-13'
  AND AC.ac_time >= '00:00:00'
  AND AC.ac_time <= '23:59:00'
group by AC.employee, AC.ac_date, AC.ac_time
order by AC.employee, AC.ac_date, AC.ac_time
;

看小提琴
但不管你怎么写，它总是一种非等联接（即不是基于 = )如果没有匹配的索引，性能可能会很差。
顺便说一句，如果这是mariadb或mysql 8，那么对于lag/lead来说是一个简单的任务。

试试这个：只需对每个条目的数据进行排名 employee, ac_date, ac_time 在单独的集合中退出，然后加入，这是您可以使用的一种简单方法 INNER JOIN 而不是 LEFT JOIN 为了更好的性能，如果你想要的记录与入口和出口

SELECT en.employee,  
    en.entry_date,
    ex.exit_date,
    en.entry_time,
    ex.exit_time,
TIMEDIFF(ex.exit_time, en.entry_time) as duration
FROM (
    SELECT employee,
        ac_date AS entry_date,
        ac_time AS entry_time,
        @enRank := @enRank + 1 AS rank
    FROM AC, (SELECT @enRank := 0) r
    WHERE ac_event = 1 ORDER BY employee, ac_date, ac_time) en
LEFT JOIN (
            SELECT employee,
                ac_date AS exit_date,
                ac_time AS exit_time,
                @exRank := @exRank + 1 AS rank
            FROM AC, (SELECT @exRank := 0) r
            WHERE ac_event = 0 ORDER BY employee, ac_date, ac_time) ex ON ex.rank = en.rank
AND ex.employee = en.employee
WHERE en.entry_date >= '2018-05-12' AND ex.exit_date <= '2018-05-13'
ORDER BY en.entry_date

outputhttp://sqlfiddle.com/#!2018年9月891日