MySQL 时间和出勤"parse filter"类型和状态

Question

我有一个 table.a 有多个列，但只有三个是重要的情况下，让他们命名为time_stamp, employee_number和station_id。
我得到了这个查询:

SELECT checktime AS time_stamp, userid AS employee_number, SN AS station_id
FROM checkinout;

获取这些信息:

+---------------------+-----------------+---------------+
|      time_stamp     | employee_number |  station_id   | 
+---------------------+-----------------+---------------+
| 30/05/2015  8:01:07 |               5 | 6068144700170 |
| 31/05/2015  8:05:17 |               2 | 6068144700170 |
| 31/05/2015 17:03:47 |               2 | 6068144700170 |
| 31/05/2015 22:03:24 |              13 | 6068144700170 |
| 01/06/2015 02:30:34 |              13 | 6068144700170 |
| 01/06/2015 03:24:33 |              13 | 6068144700170 |
| 01/06/2015 07:14:24 |              13 | 6068144700170 |
| ...                 |             ... | ...           |
+---------------------+-----------------+---------------+

我需要的是每隔20小时对employee_number的type和status按顺序进行time_stamp的分类。为什么不是每天都这样呢?因为有些员工晚上开始工作，第二天早上结束。分类应该按time_stamp计数进行，就像在employee的时间戳为1的情况下一样。a则t=0/s=I，雇员有2个时间戳的情况。a则t=0/s=I - t=0/s=O，雇员有3个时间戳的情况。a则t=0/s=I - t=2/s=O - t=0/s=O，雇员4个时间戳的情况。然后t = 0/s = I - t = 2/s = O - t = 2/s = I - t = 0/s = O…
所需输出示例:

+---------------------+-----------------+---------------+---------+-----------+
|      time_stamp     | employee_number |  station_id   | type_id | status_id |
+---------------------+-----------------+---------------+---------+-----------+
| 30/05/2015  8:01:07 |               5 | 6068144700170 |       0 |         I |
| 31/05/2015  8:05:17 |               2 | 6068144700170 |       0 |         I |
| 31/05/2015 17:03:47 |               2 | 6068144700170 |       0 |         O |
| 31/05/2015 22:03:24 |              13 | 6068144700170 |       0 |         I |
| 01/06/2015 02:30:34 |              13 | 6068144700170 |       2 |         O |
| 01/06/2015 03:24:33 |              13 | 6068144700170 |       2 |         I |
| 01/06/2015 07:14:24 |              13 | 6068144700170 |       0 |         O |
| ...                 |             ... | ...           |     ... |       ... |
+---------------------+-----------------+---------------+---------+-----------+

这个问题帮助了我，但并没有完全解决问题:
MySQL: Get start &每天的结束时间戳
显示时间戳间隔小于4分钟的记录

Answer 1

解决!MySQL -从前一行中减去值，按
分组代码:

SELECT
t1.userid employee_number,
t1.checktime time_stamp,
@diference := IF( @lastUserid = t1.userid, TIMEDIFF(t1.checktime,@lastChecktime), 0 ) diference,
@lastUserid := t1.userid userid_test,
@lastChecktime := t1.checktime timestamp_test,
@status_id := CASE
WHEN @diference = '0' THEN 'I'
WHEN @diference BETWEEN '00:00:00' AND '12:00:00' THEN 'O'
WHEN @diference BETWEEN '12:00:01' AND '20:59:59' THEN 'I'
WHEN @diference > '26:00:01' THEN 'I'
WHEN @diference BETWEEN '21:00:00' AND '26:00:00' THEN 'O'
END as status_id,
@type_id := CASE
WHEN @diference = '0' THEN 0
WHEN @diference > '99:59:59' THEN 0
WHEN @diference BETWEEN '12:30:00' AND '99:59:59' THEN 0
WHEN @diference BETWEEN '06:30:00' AND '12:29:59' THEN 1
WHEN @diference BETWEEN '00:25:00' AND '06:29:59' THEN 2
WHEN @diference BETWEEN '00:00:15' AND '00:24:59' THEN 4
END as type_id,
t1.SN station_id
FROM
icdat.checkinout t1,
( SELECT @lastUserid := 0,
@lastChecktime := 0 ) SQLVars
ORDER BY t1.userid, t1.checktime;  

它检索三个额外的列(userid_test、timestamp_test和difference)，但对我来说很好，因为我根据前一个时间戳和时间间隔获得了每个时间戳的类型和状态。