时间戳排序,添加标签然后累计求和即可,常见考题
select course_id,course_name,max(num)
from
(select course_tb.course_id ,
course_name,sum(sign) over(partition by course_tb.course_id
order by time,sign desc) as num
FROM
(SELECT user_id,course_id,in_datetime as time,1 as sign
from attend_tb
union
SELECT user_id,course_id,out_datetime as time,-1 as sign
from attend_tb) base RIGHT join course_tb using(course_id))tmp
group by course_id,course_name
order by course_id