SQL查询，跪求高人指点~~~~~

[lewis219]

有一张A表记录了用户进出房间的记录(roomid,userid,time,type)
roomid：房间ID，userid：用户ID，time：记录时间，type：1进、2出

计算一天24小时，各个时间段的房间最高在线人数？

[zheng_luck]

主要是怎么定义在一个时间段的在线，我理解楼主这个分3种情况处理： 1：在这个时间段进入 2：在这个时间段出去 3：在这个时间段之前进入，并且在这个时间段之后出去

[lewis219]

时间段是以小时为单位，比如0点到1点是一个时间段， 统计的难点在于用户进入房间是一段时间， 比如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

[ACMAIN_CHM]

建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。 参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382 1. 你的 create table xxx .. 语句 2. 你的 insert into xxx ... 语句 3. 结果是什么样，（并给以简单的算法描述） 4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL) 这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

[lewis219]

CREATE TABLE `room` ( `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

[lewis219]

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

[benluobo]

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