MySQL 在单行中获取最小和最大列的匹配行

Question

专家您好!我有一个时间表如下

时间表表

+-------------------------------------------+
|employee_no    |time             |device_id|
+-------------------------------------------+
|1              |2019-09-17 07:00 |001      |
|1              |2019-09-17 14:00 |002      |
|2              |2019-09-19 08:00 |002      |
|2              |2019-09-20 15:00 |003      |
+-------------------------------------------+

我正在使用以下查询来获取员工的进出时间

select  timesheets.employee_no, 
        MIN(time) as in_time, 
        MAX(time) as out_time, 
        COUNT(`time`) as record_count
from timesheets 
where `time` between '2019-09-17 00:00:00' 
                      and '2019-09-17 23:59:59' 
group by timesheets.employee_no

我得到了预期的输出，如下所示

+----------------------------------------------------------------+
|employee_no    |in_time          |out_time         |record_count|
+----------------------------------------------------------------+
|1              |2019-09-17 07:00 |2019-09-17 14:00 |2           |
|2              |2019-09-19 08:00 |2019-09-20 15:00 |2           |
+---------------------------------------------------+------------+

现在我需要获取进出记录的 device_id 作为 time_in_device_id 和 time_out_device_id。如何实现？

Answer 1

不要将 between 与时间一起使用。下面的更简单更准确。

MySQL 不提供“first()”和“last()”聚合函数。但是，您可以使用字符串操作来执行您想要的操作:

select ts.employee_no, 
       MIN(time) as in_time, 
       MAX(time) as out_time, 
       COUNT(time) as record_count,
       SUBSTRING_INDEX(GROUP_CONCAT(device_id ORDER BY time), ',', 1) as first_device_id,
       SUBSTRING_INDEX(GROUP_CONCAT(device_id ORDER BY time DESC), ',', 1) as lasst_device_id
from timesheets ts
where `time` >= '2019-09-17' and
      `time` < '2019-09-18' 
group by ts.employee_no