mysql - 从考勤表MYSQL中查找最后一个和最后一个

Question

我有一个MYSQL考勤表，记录如下:

   Id   DateTime             Door Employee_id
    1   2016-01-01 08:00:00  In   100
    2   2016-01-01 09:00:00  Out  100
    3   2016-01-01 09:15:00  In   100
    4   2016-01-01 09:30:00  In   100
    5   2016-01-01 10:00:00  Out  100
    6   2016-01-01 11:00:00  In   100
    7   2016-01-01 12:00:00  In   100
    8   2016-01-01 13:00:00  In   100
    9   2016-01-01 13:30:00  Out  100
   10   2016-01-01 14:00:00  Out  100
   11   2016-01-01 15:00:00  In   100

我希望输出为 Last Clock In 和 Last Clock Out，如下所示。如果上次打卡后没有打卡，则忽略打卡。

   Id   Clock In             Clock Out             Employee Id
    1   2016-01-01 08:00:00  2016-01-01 09:00:00   100
    2   2016-01-01 09:30:00  2016-01-01 10:00:00   100
    3   2016-01-01 13:00:00  2016-01-01 14:00:00   100

我真的不知道如何执行此操作。我已经问过我的同事并努力寻找答案，但没有运气。感谢你们的帮助。提前致谢。

Answer 1

这是一个带有演示的解决方案。

SQL:

-- data
create table attendance(Id int, DateTime datetime, Door char(20));
INSERT INTO attendance VALUES
(    1,   '2016-01-01 08:00:00',  'In'),
(    2,   '2016-01-01 09:00:00',  'Out'),
(    3,   '2016-01-01 09:15:00',  'In'),
(    4,   '2016-01-01 09:30:00',  'In'),
(    5,   '2016-01-01 10:00:00',  'Out'),
(    6,   '2016-01-01 11:00:00',  'In'),
(    7,   '2016-01-01 12:00:00',  'In'),
(    8,   '2016-01-01 13:00:00',  'In'),
(    9,   '2016-01-01 13:30:00',  'Out'),
(   10,   '2016-01-01 14:00:00',  'Out'),
(   11,   '2016-01-01 15:00:00',  'In');
SELECT * FROM attendance;

-- SQL needed:
SELECT 
    @id:=@id+1 Id,
    MAX(IF(Door = 'In', DateTime, NULL)) `Check In`,
    MAX(IF(Door = 'Out', DateTime, NULL)) `Check Out`
FROM
    (SELECT 
        *, 
        CASE 
            WHEN 
                (door = 'In' AND @last_door = '') OR
                (door = 'In' AND @last_door = 'In') OR
                (door = 'Out' AND @last_door = 'In') OR
                (door = 'Out' AND @last_door = 'Out')
            THEN @group_num
            WHEN
                (door = 'In' AND @last_door = 'Out')
            THEN @group_num:=@group_num+1
        ELSE 0
        END door_group,
        @last_door:=Door
    FROM attendance 
        JOIN (SELECT @group_num:=1) a
    ) t JOIN (SELECT @id:=0) b
GROUP BY t.door_group
HAVING SUM(Door = 'In') > 0 AND SUM(Door = 'Out') > 0;

输出:

mysql> SELECT * FROM attendance;
+------+---------------------+------+
| Id   | DateTime            | Door |
+------+---------------------+------+
|    1 | 2016-01-01 08:00:00 | In   |
|    2 | 2016-01-01 09:00:00 | Out  |
|    3 | 2016-01-01 09:15:00 | In   |
|    4 | 2016-01-01 09:30:00 | In   |
|    5 | 2016-01-01 10:00:00 | Out  |
|    6 | 2016-01-01 11:00:00 | In   |
|    7 | 2016-01-01 12:00:00 | In   |
|    8 | 2016-01-01 13:00:00 | In   |
|    9 | 2016-01-01 13:30:00 | Out  |
|   10 | 2016-01-01 14:00:00 | Out  |
|   11 | 2016-01-01 15:00:00 | In   |
+------+---------------------+------+
11 rows in set (0.00 sec)

mysql>
mysql> -- SQL needed:
mysql> SELECT
    ->     @id:=@id+1 Id,
    ->     MAX(IF(Door = 'In', DateTime, NULL)) `Check In`,
    ->     MAX(IF(Door = 'Out', DateTime, NULL)) `Check Out`
    -> FROM
    ->     (SELECT
    ->         *,
    ->         CASE
    ->             WHEN
    ->                 (door = 'In' AND @last_door = '') OR
    ->                 (door = 'In' AND @last_door = 'In') OR
    ->                 (door = 'Out' AND @last_door = 'In') OR
    ->                 (door = 'Out' AND @last_door = 'Out')
    ->             THEN @group_num
    ->             WHEN
    ->                 (door = 'In' AND @last_door = 'Out')
    ->             THEN @group_num:=@group_num+1
    ->         ELSE 0
    ->         END door_group,
    ->         @last_door:=Door
    ->     FROM attendance
    ->         JOIN (SELECT @group_num:=1) a
    ->     ) t JOIN (SELECT @id:=0) b
    -> GROUP BY t.door_group
    -> HAVING SUM(Door = 'In') > 0 AND SUM(Door = 'Out') > 0;
+------+---------------------+---------------------+
| Id   | Check In            | Check Out           |
+------+---------------------+---------------------+
|    1 | 2016-01-01 08:00:00 | 2016-01-01 09:00:00 |
|    2 | 2016-01-01 09:30:00 | 2016-01-01 10:00:00 |
|    3 | 2016-01-01 13:00:00 | 2016-01-01 14:00:00 |
+------+---------------------+---------------------+
3 rows in set (0.00 sec)