牛客直播开始时各直播间在线人数

course_id	course_name	course_datetime
1	Python	2021-12-1 19:00-21:00
2	SQL	2021-12-2 19:00-21:00
3	R	2021-12-3 19:00-21:00

上课情况表attend_tb如下（其中user_id表示用户编号、course_id代表课程编号、in_datetime表示进入直播间的时间、out_datetime表示离开直播间的时间）：

user_id	course_id	in_datetime	out_datetime
100	1	2021-12-01 19:00:00	2021-12-01 19:28:00
100	1	2021-12-01 19:30:00	2021-12-01 19:53:00
101	1	2021-12-01 19:00:00	2021-12-01 20:55:00
102	1	2021-12-01 19:00:00	2021-12-01 19:05:00
104	1	2021-12-01 19:00:00	2021-12-01 20:59:00
101	2	2021-12-02 19:05:00	2021-12-02 20:58:00
102	2	2021-12-02 18:55:00	2021-12-02 21:00:00
104	2	2021-12-02 18:57:00	2021-12-02 20:56:00
107	2	2021-12-02 19:10:00	2021-12-02 19:18:00
100	3	2021-12-03 19:01:00	2021-12-03 21:00:00
102	3	2021-12-03 18:58:00	2021-12-03 19:05:00
108	3	2021-12-03 19:01:00	2021-12-03 19:56:00

请你统计直播开始时（19：00），各科目的在线人数，以上例子的输出结果为（按照course_id升序排序）：

course_id	course_name	online_num
1	Python	4
2	SQL	2
3	R	1

示例1

输入:

CREATE TABLE course_tb(
course_id int(10) NOT NULL, 
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);
INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');

CREATE TABLE attend_tb(
user_id int(10) NOT NULL, 
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:00:00', '2021-12-1 19:28:00');
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:30:00', '2021-12-1 19:53:00');
INSERT INTO attend_tb VALUES(101, 1, '2021-12-1 19:00:00', '2021-12-1 20:55:00');
INSERT INTO attend_tb VALUES(102, 1, '2021-12-1 19:00:00', '2021-12-1 19:05:00');
INSERT INTO attend_tb VALUES(104, 1, '2021-12-1 19:00:00', '2021-12-1 20:59:00');
INSERT INTO attend_tb VALUES(101, 2, '2021-12-2 19:05:00', '2021-12-2 20:58:00');
INSERT INTO attend_tb VALUES(102, 2, '2021-12-2 18:55:00', '2021-12-2 21:00:00');
INSERT INTO attend_tb VALUES(104, 2, '2021-12-2 18:57:00', '2021-12-2 20:56:00');
INSERT INTO attend_tb VALUES(107, 2, '2021-12-2 19:10:00', '2021-12-2 19:18:00');
INSERT INTO attend_tb VALUES(100, 3, '2021-12-3 19:01:00', '2021-12-3 21:00:00');
INSERT INTO attend_tb VALUES(102, 3, '2021-12-3 18:58:00', '2021-12-3 19:05:00');
INSERT INTO attend_tb VALUES(108, 3, '2021-12-3 19:01:00', '2021-12-3 19:56:00');

输出:

1|Python|4
2|SQL|2
3|R|1

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 36ms, 内存消耗: 6508KB, 提交时间: 2022-01-24

-- 2021年12月16日

select
  c.course_id as course_id, c.course_name as course_name
, count(distinct if( a.in_datetime<=str_to_date(substring_index(c.course_datetime, '-', 3), '%Y-%m-%e %H:%i')
     and a.out_datetime>str_to_date(substring_index(c.course_datetime, '-', 3), '%Y-%m-%e %H:%i')
     , a.user_id, null)) as online_num
from course_tb c
left outer join attend_tb a
    on c.course_id = a.course_id
group by c.course_id, c.course_name
order by course_id
;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6372KB, 提交时间: 2021-12-19

select 
c.course_id,
c.course_name,
count(distinct user_id) asonline_num
from course_tb c
join attend_tb a using (course_id)
where time(in_datetime)<='19:00:00'
    and time(out_datetime)>='19:00:00'
group by c.course_id,c.course_name
order by c.course_id

Mysql 解法, 执行用时: 37ms, 内存消耗: 6380KB, 提交时间: 2022-01-22

select course_id, course_name, 
count(*) as online_num
from
(
    select a.course_id, course_name,
    concat(substring_index(course_datetime, '-', 3), ':00') as start_time,
    in_datetime, out_datetime
    from attend_tb as a left join course_tb as b
    on a.course_id = b.course_id
) as c
where start_time >= in_datetime and start_time <= out_datetime
group by course_id, course_name
order by course_id

Mysql 解法, 执行用时: 37ms, 内存消耗: 6388KB, 提交时间: 2021-12-31

# 请你统计直播开始时（19：00），各科目的在线人数，以上例子的输出结果为（按照course_id升序排序）：
select 
    ct.course_id,
    course_name,
    count(distinct user_id) as online_num
from course_tb  ct
left join attend_tb at
on ct.course_id=at.course_id
where DATE_FORMAT(course_datetime,'%Y-%m-%d%')=DATE_FORMAT(in_datetime,'%Y-%m-%d%')
     and time(in_datetime) <= '19:00:00'and time(out_datetime) >= '19:00:00'
group by  ct.course_id,course_name
ORDER by course_id

Mysql 解法, 执行用时: 37ms, 内存消耗: 6388KB, 提交时间: 2021-12-08

select a.course_id,course_name,
    COUNT(*) online_num
FROM attend_tb a left join course_tb c
on a.course_id = c.course_id
where "19:00" between date_format(in_datetime,"%H:%i") 
    and date_format(out_datetime,"%H:%i")
GROUP BY a.course_id,course_name
ORDER BY a.course_id

上一题

下一题

详情