题解|复习同时在线max三步走#牛客直播各科目同时在线人数#

### attend_tb
### and what
### 每个科目最大同时在线人数，需要对时间进行排序，记录各个时间的状态时进入还是离开

# (SELECT a.course_id,course_name,in_datetime,1 AS count
# FROM attend_tb a
# LEFT JOIN course_id b
# ON a.course_id = b.course_id)
# UNION ALL
# (SELECT a.course_id,course_name,out_datetime,-1 AS count
# FROM attend_tb a
# LEFT JOIN course_id b
# ON a.course_id = b.course_id)

SELECT course_id,course_name, MAX(num_count) AS max_num
FROM(
    SELECT course_id,course_name,dt,SUM(num) OVER(PARTITION BY course_id ORDER BY dt)AS num_count
    FROM(
        (SELECT a.course_id,course_name,in_datetime AS dt,1 AS num
        FROM attend_tb a
        LEFT JOIN course_tb b
        ON a.course_id = b.course_id)
        UNION ALL
        (SELECT a.course_id,course_name,out_datetime AS dt,-1 AS num
        FROM attend_tb a
        LEFT JOIN course_tb b
        ON a.course_id = b.course_id)
    )t1
)t2
GROUP BY course_id,course_name
ORDER BY course_id