mysql - SQL 查询从度量表中导出时钟数据

Question

首先，非常感谢您对此的调查。

我们有一个名为 measures 的表，它记录了实验室中的员工事件，如下所示:

╔════╦═════════╦═════════════════════╦═════════════════════╦═════════╦══════════════╗║ id ║ staf_id ║     start_time      ║      stop_time      ║ task_id ║ fruit_tested ║╠════╬═════════╬═════════════════════╬═════════════════════╬═════════╬══════════════╣║  1 ║     123 ║ 2016-06-01 10:00:00 ║ 2016-06-01 13:15:00 ║       1 ║           90 ║║  2 ║     123 ║ 2016-06-01 15:20:00 ║ 2016-06-01 18:30:00 ║       2 ║           60 ║║  3 ║     333 ║ 2016-06-02 10:30:30 ║ 2016-06-02 15:45:00 ║       1 ║           30 ║║  4 ║     333 ║ 2016-06-03 09:00:00 ║ 2016-06-03 09:45:00 ║       1 ║           60 ║║  5 ║     555 ║ 2016-06-01 07:00:00 ║ 2016-06-01 11:15:00 ║       1 ║           90 ║║  6 ║     555 ║ 2016-06-01 11:30:00 ║ 2016-06-01 13:00:00 ║       1 ║           30 ║║  7 ║     123 ║ 2016-06-02 10:00:00 ║ 2016-06-02 14:00:00 ║       3 ║           30 ║║  8 ║     333 ║ 2016-06-03 10:20:00 ║ 2016-06-03 13:15:15 ║       1 ║           30 ║║  9 ║     333 ║ 2016-06-04 07:50:20 ║ 2016-06-04 12:30:50 ║       1 ║           60 ║║ 10 ║     555 ║ 2016-06-03 05:30:00 ║ 2016-06-03 10:00:00 ║       1 ║           60 ║║ 11 ║     123 ║ 2016-06-03 06:00:00 ║ 2016-06-03 10:30:30 ║       4 ║           90 ║║ 12 ║     123 ║ 2016-06-03 12:15:04 ║ 2016-06-03 12:45:50 ║       5 ║           90 ║║ 13 ║     123 ║ 2016-06-03 17:10:00 ║ 2016-06-03 19:00:00 ║       1 ║           30 ║║ 14 ║     123 ║ 2016-06-04 12:15:04 ║ 2016-06-04 15:00:00 ║       1 ║           30 ║║ 15 ║     123 ║ 2016-06-04 15:10:00 ║ 2016-06-04 15:30:00 ║       2 ║           90 ║╚════╩═════════╩═════════════════════╩═════════════════════╩═════════╩══════════════╝

目前，我们的 real timeClock 表存在问题，作为临时解决方案，我想从 measures 表中检索每个员工每天的时钟详细信息，其中 clock_in 将是第一个 measures.start_time day 和 clock_out 将是该员工当天的最后一个 measures.stop_time，如下所示:

╔══════════╦═════════════════════╦═════════════════════╗║ staff_id ║      clock_in       ║      clock_out      ║╠══════════╬═════════════════════╬═════════════════════╣║      123 ║ 2016-06-01 10:00:00 ║ 2016-06-01 18:30:00 ║║      123 ║ 2016-06-02 10:00:00 ║ 2016-06-02 14:00:00 ║║      123 ║ 2016-06-03 06:00:00 ║ 2016-06-03 19:00:00 ║║      123 ║ 2016-06-04 12:15:04 ║ 2016-06-04 15:30:00 ║║      333 ║ 2016-06-02 10:30:30 ║ 2016-06-02 15:45:00 ║║      333 ║ 2016-06-03 09:00:00 ║ 2016-06-03 13:15:15 ║║      333 ║ 2016-06-04 07:50:20 ║ 2016-06-04 12:30:50 ║║      555 ║ 2016-06-01 07:00:00 ║ 2016-06-01 13:00:00 ║║      555 ║ 2016-06-03 05:30:00 ║ 2016-06-03 10:00:00 ║╚══════════╩═════════════════════╩═════════════════════╝

我如何获得这个结果？任何建议将不胜感激。

Answer 1

我认为这很简单，只是一个聚合:

select staff_id, min(start_time) as clock_in, max(stop_time) as clock_out
from measures
group by staff_id, date(start_time)
order by staff_id, min(start_time);