短视频直播间晚上11-12点之间各直播间的在线人数_

现有某天短视频直播间用户观看直播间的信息表user_view_tb如下，

（其中字段包含用户id：user_id、直播间id：room_id、进入时间：in_time，离开时间：out_time）

user_id	room_id	in_time	out_time
1	1001	10:00:00	10:30:00
2	1001	10:01:00	10:05:00
3	1001	10:05:00	10:20:00
1	1002	19:05:00	20:05:00
2	1002	19:15:00	19:55:00
2	1002	20:15:00	20:45:00
3	1002	20:15:00	20:45:00
4	1003	22:15:00	23:15:00
1	1002	23:15:00	23:45:00
4	1002	23:10:00	23:25:00
3	1002	23:00:00	23:35:00
4	1001	23:10:00	23:25:00
3	1001	23:00:00	23:35:00
4	1003	23:10:00	23:15:00
1	1001	20:10:00	20:15:00
1	1001	20:00:00	23:35:00

有直播间信息表room_info_tb如下：

room_id	room_name	room_type
1001	娱乐大王牌	娱乐
1002	声家班	搞笑
1003	嗨嗨嗨	搞笑

请你统计晚上11-12点之间各直播间的在线人数(包含边界值11:00、12:00)，并按在线人数降序排序，以上例子输出结果如下：

room_id	room_name	user_count
1001	娱乐大王牌	3
1002	声家班	3
1003	嗨嗨嗨	1

输入

drop table if exists user_view_tb;
CREATE TABLE user_view_tb(
user_id int(10) NOT NULL,
room_id int(10) NOT NULL,
in_time time NOT NULL,
out_time time NOT NULL
);
INSERT INTO user_view_tb VALUES(1, 1001, '10:00:00', '10:30:00');
INSERT INTO user_view_tb VALUES(2, 1001, '10:01:00', '10:05:00');
INSERT INTO user_view_tb VALUES(3, 1001, '10:05:00', '10:20:00');
INSERT INTO user_view_tb VALUES(1, 1002, '19:05:00', '20:05:00');
INSERT INTO user_view_tb VALUES(2, 1002, '19:15:00', '19:55:00');
INSERT INTO user_view_tb VALUES(2, 1002, '20:15:00', '20:45:00');
INSERT INTO user_view_tb VALUES(3, 1002, '20:15:00', '20:45:00');
INSERT INTO user_view_tb VALUES(4, 1003, '22:15:00', '23:15:00');
INSERT INTO user_view_tb VALUES(1, 1002, '23:15:00', '23:45:00');
INSERT INTO user_view_tb VALUES(4, 1002, '23:10:00', '23:25:00');
INSERT INTO user_view_tb VALUES(3, 1002, '23:00:00', '23:35:00');
INSERT INTO user_view_tb VALUES(4, 1001, '23:10:00', '23:25:00');
INSERT INTO user_view_tb VALUES(3, 1001, '23:00:00', '23:35:00');
INSERT INTO user_view_tb VALUES(4, 1003, '23:10:00', '23:15:00');
INSERT INTO user_view_tb VALUES(1, 1001, '20:10:00', '20:15:00');
INSERT INTO user_view_tb VALUES(1, 1001, '20:00:00', '23:35:00');

drop table if exists room_info_tb;
CREATE TABLE room_info_tb(
room_id int(10) NOT NULL,
room_name varchar(20) NOT NULL,
room_type varchar(20) NOT NULL
);
INSERT INTO room_info_tb VALUES(1001, '娱乐大王牌', '娱乐');
INSERT INTO room_info_tb VALUES(1002, '声家班', '搞笑');
INSERT INTO room_info_tb VALUES(1003, '嗨嗨嗨', '搞笑');

SELECT r.room_id, r.room_name, count(t1.user_id)AS user_count FROM( SELECT DISTINCT user_id, room_id FROM user_view_tb u WHERE TIME(in_time) >= '23:00:00' AND TIME(in_time) <= '24:00:00' &nbs***bsp;TIME(in_time) < '23:00:00' AND TIME(out_time) >= '23:00:00') t1 RIGHT JOIN room_info_tb r ON r.room_id = t1.room_id GROUP BY r.room_id,r.room_name

短视频直播间晚上11-12点之间各直播间的在线人数

输入

输出

问题信息

热门推荐

相关试题