insert into room(roomid,userid,time,type) values(1000,5001,'2015-01-22 01:03:00',1); insert into room(roomid,userid,time,type) values(1000,5002,'2015-01-22 01:33:00',1); insert into room(roomid,userid,time,type) values(1000,5002,'2015-01-22 01:53:00',2); insert into room(roomid,userid,time,type) values(1000,5003,'2015-01-22 02:03:00',1); insert into room(roomid,userid,time,type) values(1000,5001,'2015-01-22 02:10:00',2); insert into room(roomid,userid,time,type) values(1000,5003,'2015-01-22 02:15:00',2);想要得到各时间段最高在线人数 0-1点 1-2点 2-3点 0 2 2
select sum(case when starttime <= 0 and endtime >= 0 then 1 else 0 end) '0-1', sum(case when starttime <= 1 and endtime >= 1 then 1 else 0 end) '1-2', sum(case when starttime <= 2 and endtime >= 2 then 1 else 0 end) '2-3' from (select a.roomid,a.userid,date_format(a.time,'%h') starttime, date_format(min(b.time),'%h') endtime from room a, room b where a.roomid=b.roomid and a.userid=b.userid and a.type=1 and b.type=2 and b.time > a.time group by roomid,userid,starttime) tmp
统计的难点在于用户进入房间是一段时间,
比如A用户0:10进,0:20出,B用户0:15进,2:05出,C用户1:10进,2:40出
那么该房间0点到1点最高在线人数就是2,A和B
1点到2点最高在线人数是2,B和C
2点到3点最高在线也是2,B和C
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
`roomid` int(11) default NULL COMMENT '房间ID',
`userid` int(11) default NULL COMMENT '用户ID',
`time` datetime default NULL COMMENT '记录时间',
`type` tinyint(1) default NULL COMMENT '类型(1进2出)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
insert into room(roomid,userid,time,type) values(1000,5002,'2015-01-22 01:33:00',1);
insert into room(roomid,userid,time,type) values(1000,5002,'2015-01-22 01:53:00',2);
insert into room(roomid,userid,time,type) values(1000,5003,'2015-01-22 02:03:00',1);
insert into room(roomid,userid,time,type) values(1000,5001,'2015-01-22 02:10:00',2);
insert into room(roomid,userid,time,type) values(1000,5003,'2015-01-22 02:15:00',2);想要得到各时间段最高在线人数
0-1点 1-2点 2-3点
0 2 2
sum(case when starttime <= 0 and endtime >= 0 then 1 else 0 end) '0-1',
sum(case when starttime <= 1 and endtime >= 1 then 1 else 0 end) '1-2',
sum(case when starttime <= 2 and endtime >= 2 then 1 else 0 end) '2-3'
from
(select a.roomid,a.userid,date_format(a.time,'%h') starttime, date_format(min(b.time),'%h') endtime
from room a, room b where a.roomid=b.roomid and a.userid=b.userid and a.type=1 and b.type=2
and b.time > a.time group by roomid,userid,starttime) tmp