原始数据表如下：

uid	on_off_state(1:上线,2:下线)	on_off_time
6195307332967349	1	2016-08-01 15:03:18
6195307332967349	2	2016-08-01 15:23:43
6195307332967349	1	2016-08-01 14:16:27
6195307332967349	2	2016-08-01 14:56:19

第一步：连接相邻两行记录，得到每次上下线的开始时间和结束时间
创建中间表

uid	on_off_state(1:上线,2:下线)	start_time	end_time
6195307332967349	1	2016-08-01 15:03:18	2016-08-01 15:23:43
6195307332967349	2	2016-08-01 15:23:43	2016-08-01 14:16:27
6195307332967349	1	2016-08-01 14:16:27	2016-08-01 14:56:19
6195307332967349	2	2016-08-01 14:56:19	2016-08-01 23:59:59

1. 初始化中间表 2. 更新中间表的结束时间字段，取大于当前行的第一条记录的开始时间作为结束时间，如果没有，则取一天最后时间 UPDATE $table AS a SET end_time=ifnull( (select start_time from (SELECTid,uid,start_time FROM $table WHERE on_date='$statday') as b WHERE b.uid=a.uid AND b.id>a.id ORDER BYb.start_time ASC LIMIT 1) ,'$enddate') WHERE a.on_date='$statday'

原始数据表如下：

uid	on_off_state(1:上线,2:下线)	on_off_time
6195307332967349	1	2016-08-01 15:03:18
6195307332967349	2	2016-08-01 15:23:43
6195307332967349	1	2016-08-01 14:16:27
6195307332967349	2	2016-08-01 14:56:19

第一步：连接相邻两行记录，得到每次上下线的开始时间和结束时间
创建中间表

uid	on_off_state(1:上线,2:下线)	start_time	end_time
6195307332967349	1	2016-08-01 15:03:18	2016-08-01 15:23:43
6195307332967349	2	2016-08-01 15:23:43	2016-08-01 14:16:27
6195307332967349	1	2016-08-01 14:16:27	2016-08-01 14:56:19
6195307332967349	2	2016-08-01 14:56:19	2016-08-01 23:59:59

1. 初始化中间表 2. 更新中间表的结束时间字段，取大于当前行的第一条记录的开始时间作为结束时间，如果没有，则取一天最后时间 UPDATE $table AS a SET end_time=ifnull( (select start_time from (SELECTid,uid,start_time FROM $table WHERE on_date='$statday') as b WHERE b.uid=a.uid AND b.id>a.id ORDER BYb.start_time ASC LIMIT 1) ,'$enddate') WHERE a.on_date='$statday'