题解 | #牛客直播各科目同时在线人数#：在借鉴网友的思路上改善

WITH t4 AS 
	(WITH t3 AS
		(WITH t2 AS
			(WITH t1 AS
			 -- 把直播时间修改成为可以与用户登录和退出时间相比的格式
				(SELECT course_id, course_name, CONCAT(DATE_FORMAT(course_datetime, '%Y-%m-%d %H:%i'), ':00') AS begin_time,
					CONCAT(SUBSTRING_INDEX(course_datetime, ' ', 1), ' ', SUBSTRING_INDEX(course_datetime, '-', -1), ':00') AS end_time,
					a.user_id, a.in_datetime, a.out_datetime
				FROM course_tb c
				RIGHT JOIN attend_tb a USING (course_id))
			 -- 进行判断，如果用户在直播开始前就已经登录，令登录时间为直播时间，否则为用户真实登录时间；如果用户在直播结束后才退出，则令直播结束时间为退出时间，否则为用户真实退出时间
			SELECT course_id, course_name, IF(in_datetime <= begin_time, begin_time, in_datetime) AS time_1,
				IF(out_datetime >= end_time, end_time, out_datetime) AS time_2
			FROM t1)
		 -- 网友的思路：可以在题解中的高赞回答中找到
		SELECT course_id, course_name, time_1 AS time_cnt, 1 AS cnt FROM t2
		UNION ALL
		SELECT course_id, course_name, time_2 AS time_cnt, -1 AS cnt FROM t2)
	SELECT course_id, course_name, SUM(cnt) OVER(PARTITION BY course_id ORDER BY time_cnt ASC)AS max_num FROM t3)
SELECT course_id, course_name, MAX(max_num) AS max_num FROM t4
GROUP BY course_id, course_name
ORDER BY course_id;