56,678
社区成员
发帖
与我相关
我的任务
分享
DROP TABLE IF EXISTS `o_user`;
CREATE TABLE `o_user` (
`user` varchar(255) DEFAULT NULL,
`time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `o_user` VALUES ('zhang', '2016-07-14 09:06:46');
INSERT INTO `o_user` VALUES ('lisi', '2016-07-14 09:07:58');
INSERT INTO `o_user` VALUES ('wang', '2016-07-14 09:08:10');
INSERT INTO `o_user` VALUES ('zhang', '2016-07-14 09:16:46');
INSERT INTO `o_user` VALUES ('lisi', '2016-07-14 09:47:58');
INSERT INTO `o_user` VALUES ('zhang', '2016-07-14 09:46:46');
INSERT INTO `o_user` VALUES ('lisi', '2016-07-14 09:57:58');
INSERT INTO `o_user` VALUES ('zhang', '2016-07-14 09:56:46');
select
temp.user,sum(sum_time) as sum_time
from(
select
m.*
, @end:=time as endtime
,case when m.user=@user then round((@sum:=unix_timestamp(@end)-unix_timestamp(@begin))/60 ,0)
else 0 end as sum_time
,@user:=m.user as temp_user
, @begin:=time as begin_time
from
(select
*
from o_user
order by user,time
)m,
(select @user:='',@begin:=0,@end:=0,@sum:=0)n
)temp where temp.sum_time<=20
group by user
use dbi;
DROP TABLE IF EXISTS `o_user`;
CREATE TABLE `o_user` (
`user` varchar(255) DEFAULT NULL,
`time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `o_user` VALUES
('a', '2016-07-14 09:06:46'),
('b', '2016-07-14 09:07:58'),
('a', '2016-07-14 09:11:10'),
('b', '2016-07-14 09:12:46'),
('a', '2016-07-14 10:07:58'),
('b', '2016-07-14 10:08:10'),
('b', '2016-07-14 10:18:10');
select user, _g, min(time) as start_time, max(time) as finish_time
from(
select data.*
, @i:=if(@u=user,if(time<@t, @i, @i+1), 1) as _g
, @u:=user, @t:=time + INTERVAL 20 MINUTE -- 持续在线的时间间隔
from o_user data,(select @u:=null, @t:='0000-1-1') id
order by data.user, data.time
) x group by user, _g