mysql - 计算两条记录并将它们连接到同一个 MySQL 行中

Question

我有一个表，其中包含员工访问控制的数据，在不同的行中进入和离开办公室。输出基于日期和时间范围示例中的查询 ac_date >= '2018-05-12' AND ac_date <= '2018-05-13' AND ac_time >='08:00:00' AND ac_time <= '13:00:00']

表AC

CREATE TABLE `AC` (
  `employee` int(11) NOT NULL,
  `ac_date` date NOT NULL,
  `ac_time` time NOT NULL,
  `ac_event` tinyint(4) NOT NULL,
  KEY `index2` (`employee`,`ac_date`,`ac_time`,`ac_event`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Employee     ac_date        ac_time     ac_event
2032        2018-05-12      08:52:00        1
2032        2018-05-12      11:39:33        0
2032        2018-05-12      11:48:06        1
2032        2018-05-12      11:52:54        0
2032        2018-05-12      11:59:54        1
2032        2018-05-12      12:23:40        0
2032        2018-05-13      08:34:43        1
2032        2018-05-13      09:02:25        0
2032        2018-05-13      09:12:16        1
2032        2018-05-13      11:45:21        0
2032        2018-05-13      12:50:40        1
2032        2018-05-13      12:52:16        0

其中 ac_event = 1 是输入，ac_event = 0 是输出。

我需要进行查询以在同一行(进入/退出)中显示数据，然后计算员工离开办公室的时间。Ej:

第一个 block 的输出:

Employee    entry_date   exit_date      entry_date   exit_date  duration 
2032        2018-05-12   2018-05-12     08:52:00     11:39:33   02:47:33

我能够通过对同一个表进行查询来获得结果，但是我有重复的数据，所以我不得不求助于 [ac_date, ac_time] 组。我不知道我尝试的方法是否正确，所以我想看看专家的解决方案。谢谢你!

更新: http://sqlfiddle.com/#!9/6f36f/3

Answer 1

这是您原始查询的简化版本:

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
;

参见 fiddle

但无论你怎么写，它总是一种非等值连接(即不基于=)，如果没有匹配索引，性能可能会很差。

顺便说一句，如果这是 MariaDB 或 MySQL 8，对于 LAG/LEAD 来说这将是一项简单的任务。