mysql - 将时间输入和超时合并在一行mysql中

Question

目前，我有这个表:date_time_records，其中保存了员工进出的所有时间。

正如您所看到的，每行都存储了所有进出时间的数据。

并且可以使用state字段来识别这些数据是否超时

时间 = C/In

超时 = C/Out

预期输出

现在我正在尝试执行类似这样的查询

您可以在其中查看不同日期的相同员工记录

您可以注意到进出时间正在安排中。

这是我的代码

 SELECT
      a.id,
      a.ACNo,
      a.name,
      a.email,
       (SELECT MAX(datetime) FROM date_time_records WHERE id = a.id AND state = "C/In") as time_in,
       (SELECT MIN(datetime) FROM date_time_records WHERE id = a.id AND state = "C/Out") as time_out,
  FROM `date_time_records` as a GROUP BY datetime ORDER BY `created_at`  ASC

请忽略created_at

我使用datetime，因为系统能够批量上传进出时间。过去的数据。

Answer 1

你可以这样做:

SELECT ACNo, Name,
       SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN state='C/In' THEN DATETIME END ORDER BY DATETIME ASC),',',1) AS time_in,
       SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN state='C/Out' THEN DATETIME END ORDER BY DATETIME ASC),',',-1) AS time_out,
       DATE(DATETIME) AS recDate
FROM   date_time_records
GROUP BY ACNo, Name,recDate
ORDER BY ACNo;

使用 GROUP_CONCAT 然后使用 SUBSTRING_INDEX 获取第一个和最后一个值。

在这里摆弄:https://www.db-fiddle.com/f/bfkoKK13kcE8NYVzo71Zi3/3