mysql - 查找不同子序列行的日期时间之间的间隙

Question

如何查找不同行的日期时间之间的差距？以下面为例。

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   

在这段代码之后，

select (@rn := @rn + 1) as id,
   max(case when door = 'in' then datetime end) as clockin,
   max(case when door = 'out' then datetime end) as clockout
from (select t.*,
         @grp := if(@d = door, @grp,
                    if(@d := door, @grp + 1, @grp + 1)
                   ) as grp
  from t cross join
       (select @d := '', @grp := 0) param
  order by id
 ) t cross join
 (select @rn := 0) param
group by floor((grp - 1) / 2)

将输出他的:

Id  Clock In             Clock 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   

感谢戈登提供的代码。但现在我想从下一行找到下类签到和签到之间的时间间隔，如下所示:

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

到目前为止，我已尝试过此操作，但它在同一行的 checkout 和 checkin 之间产生了间隙:

select (@rn := @rn + 1) as id,
   max(case when door = 'in' then datetime end) as clockin,
   max(case when door = 'out' then datetime end) as clockout,
   timediff(max(case when door = 'out' then datetime end),max(case when door   = 'in' then datetime end)) as gaps
 from (select t.*,
         @grp := if(@d = door, @grp,
                    if(@d := door, @grp + 1, @grp + 1)
                   ) as grp
  from t cross join
       (select @d := '', @grp := 0) param
  order by id
 ) t cross join
 (select @rn := 0) param
  group by floor((grp - 1) / 2)

请大家帮帮我。感谢您的努力和帮助。

Answer 1

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(Id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,DateTime DATETIME NOT NULL
,Door VARCHAR(12) NOT NULL
);

INSERT INTO my_table 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 n.id
     , n.datetime
     , n.door
     , n.diff
  FROM
     (
    SELECT x.*
     , TIMEDIFF(datetime,@prev) diff
     , @prev := datetime
  FROM my_table x 
  JOIN 
     ( SELECT DISTINCT MAX(b.id) id FROM my_table a JOIN my_table b ON b.door <> a.door AND b.id <= a.id GROUP BY a.id) y 
    ON y.id = x.id
  JOIN (SELECT @prev:=null) vars
 ORDER 
    BY id
    ) n
   WHERE door = 'in';

+----+---------------------+------+----------+
| id | datetime            | door | diff     |
+----+---------------------+------+----------+
|  1 | 2016-01-01 08:00:00 | In   | NULL     |
|  4 | 2016-01-01 09:30:00 | In   | 00:30:00 |
|  8 | 2016-01-01 13:00:00 | In   | 03:00:00 |
+----+---------------------+------+----------+
3 rows in set (0.00 sec)