度小满SQL笔试题

#原创文章,转载请注明出处

有趣的笔试题+1:

某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。
已知课程表course_tb如下（其中course_id代表课程编号，course_name表示课程名称，course_datetime代表上课时间）：

  

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

问题:请你统计每个科目最大同时在线人数（按course_id排序），以上数据的输出结果如下：

解法1:向子查询传递参数,***以登出时间为计算节点***

create table endtime
(endtime date);
insert into endtime--吃饱了撑的把登出时间节点提出来
select out_datetime from attend_tb;
select t1.course_id,t1.course_name,
(select max((select count(*) from attend_tb t2 where t1.course_id=t2.course_id and endtime between in_datetime and out_datetime )) from endtime)
max_sum --算法一:向子句传入course_id,时间节点,计算最大值
from course_tb t1;

 

解法2:笛卡尔积,依旧是以登出节点为准--本质上都是for循环

create table endtime
(endtime date);
insert into endtime
select out_datetime from attend_tb;
--创建中间表
with t as
(select t1.user_id,t1.course_id,t1.in_datetime,out_datetime,t2.course_name from attend_tb t1 join course_tb  t2 on t1.course_id=t2.course_id)
--笛卡尔积
select course_id,course_name,max(max_sc) from
(select course_id,course_name,count(*) max_sc from (select * from t,endtime where endtime between in_datetime and out_datetime order by endtime)
 group by course_id,course_name,endtime
 order by course_id,max_sc desc)
 group by course_id,course_name;