从查询表中推导出时钟数据的SQL查询

问题描述：

我们有记录的实验室工作人员的活动表叫措施如下图所示：

 
╔════╦═════════╦═════════════════════╦═════════════════════╦═════════╦══════════════╗ 
║ 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 ║ 
╚════╩═════════╩═════════════════════╩═════════════════════╩═════════╩══════════════╝

目前，我们有一个问题与我们的实际时钟进度表，作为临时解决方案，我想找回每个工作人员从度量表中获取实时时钟详细信息，其中clock_in将是第一个度量值.start_time为当天，clock_out为该工作人员当天的最后一个度量值.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 ║ 
╚══════════╩═════════════════════╩═════════════════════╝

我该如何达到有这个结果吗？任何建议将深表感谢。

所以，你要为每个用户每个 – Strawberry

答

我觉得这是很容易，只是一个聚集：

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);

天最小和最大时间戳**感谢戈登** –

答

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

这是如何不同从第一个答案给出？ – Nicarus

从查询表中推导出时钟数据的SQL查询

相关推荐