mysql - 考勤 : Get time_in and time_out of flexible schedule Employees

Question

根据下面给定的模式，我想在我的数据库中查询员工每天进出的时间。

注意:员工的工作时间很灵活，他们可以上夜类或白类(正如您在员工 #1 中看到的那样)。

架构:出勤表

| id | employee_id | timedata            | in_out |
|----|-------------|---------------------|--------|
| 1  | 1           | 2017-06-08 13:51:02 | IN     | <- night shift ( inserted: 2017-06-08)
| 2  | 1           | 2017-06-09 01:10:04 | OUT    | <- actual time_out (inserted: 2017-06-09)
| 3  | 1           | 2017-06-09 14:11:40 | IN     | 
| 4  | 1           | 2017-06-09 19:41:26 | OUT    |
| 5  | 2           | 2017-06-08 09:25:17 | IN     |
| 6  | 2           | 2017-06-08 20:44:14 | OUT    |
| 7  | 2           | 2017-06-09 11:35:00 | IN     |
| 8  | 2           | 2017-06-09 20:36:06 | OUT    |

这是我到目前为止所做的查询:

SELECT employee_id, 
       DATE(timedata) as attendance_date,
       MIN(IF(in_out='IN',timedata,NULL)) AS time_in, 
       MAX(IF(in_out='OUT',timedata,NULL)) AS time_out
FROM attendances
GROUP BY employee_id,DATE(timedata)

此查询的问题是我无法获取夜类交易的 time_out。这是输出:

| employee_id | attendance_date | time_in             | time_out            |
|-------------|-----------------|---------------------|---------------------|
| 1           | 2017-06-08      | 2017-06-08 13:51:02 | NULL                |
| 2           | 2017-06-08      | 2017-06-08 11:25:17 | 2017-06-08 20:44:14 |
| 2           | 2017-06-09      | 2017-06-09 11:35:00 | 2017-06-09 20:36:06 |
| 1           | 2017-06-09      | 2017-06-09 14:11:40 | 2017-06-09 19:41:26 |

这是我的期望的输出

| employee_id | attendance_date | time_in             | time_out            |
|-------------|-----------------|---------------------|---------------------|
| 1           | 2017-06-08      | 2017-06-08 13:51:02 | 2017-06-09 01:10:04 |
| 2           | 2017-06-08      | 2017-06-08 09:25:17 | 2017-06-08 20:44:14 |
| 1           | 2017-06-09      | 2017-06-09 14:11:40 | 2017-06-09 19:41:26 |
| 2           | 2017-06-09      | 2017-06-09 11:35:00 | 2017-06-09 20:36:06 |

如有任何建议，我们将不胜感激。

Answer 1

您可以使用自连接将 time_in 与 time_out 组合在一起，其中 time_out 在 time_in 之后。然后将其按 time_in 分组并仅选择 first_time_out:

select employee_id, attendance_date,  time_in, MIN(time_out) time_out from (
   select A.id id, DATE(A.timedata) attendance_date, A.employee_id employee_id, A.timedata time_in, A.in_out, B.timedata time_out
   from attendances A
   left join attendances B
       on A.employee_id = B.employee_id
          and A.timedata < B.timedata
          and B.in_out = 'OUT'    
   where A.in_out = 'IN'
) AB
group by employee_id, attendance_date, time_in