select ct.course_id,course_name,max(dt) from (select user_id,course_id, sum(diff) over(partition by course_id order by dt) as dt from (select user_id, course_id, time(in_datetime) as dt,1 diff from attend_tb union all select user_id, course_id, time(out_datetime)as dt,-1 diff from attend_tb) as t1) as t2 inner join course_tb as ct using(course_id) group by course_id,course_name

1.首先我们应该想到用编码+联立。在此对原表in_time和out_time进行编码，in为在线人数+1， out为在线人数-1，我们用union all将两张表格联立 select user_id,course_id, sum(diff) over(partition by course_id order by dt) as dt from (select user_id, course_id, time(in_datetime) as dt,1 diff from attend_tb union all select user_id, course_id, time(out_datetime)as dt,-1 diff from attend_tb 2.我们利用窗口函数对diff进行sum求和，按照course_id进行分区，order by dt select user_id,course_id, sum(diff) over(partition by course_id order by dt) as dt from (# t1) as t2 3.联立两张表格，按照course_id分组输出最大的diff。