course_id	course_name	course_datetime
1	Python	2021-12-1 19:00-21:00
2	SQL	2021-12-2 19:00-21:00
3	R	2021-12-3 19:00-21:00

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

示例1

输入:

drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL, 
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);

INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');

drop table if exists attend_tb;
CREATE TABLE attend_tb(
user_id int(10) NOT NULL, 
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:00:00', '2021-12-1 19:28:00');
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:30:00', '2021-12-1 19:53:00');
INSERT INTO attend_tb VALUES(101, 1, '2021-12-1 19:00:00', '2021-12-1 20:55:00');
INSERT INTO attend_tb VALUES(102, 1, '2021-12-1 19:00:00', '2021-12-1 19:05:00');
INSERT INTO attend_tb VALUES(104, 1, '2021-12-1 19:00:00', '2021-12-1 20:59:00');
INSERT INTO attend_tb VALUES(101, 2, '2021-12-2 19:05:00', '2021-12-2 20:58:00');
INSERT INTO attend_tb VALUES(102, 2, '2021-12-2 18:55:00', '2021-12-2 21:00:00');
INSERT INTO attend_tb VALUES(104, 2, '2021-12-2 18:57:00', '2021-12-2 20:56:00');
INSERT INTO attend_tb VALUES(107, 2, '2021-12-2 19:10:00', '2021-12-2 19:18:00');
INSERT INTO attend_tb VALUES(100, 3, '2021-12-3 19:01:00', '2021-12-3 21:00:00');
INSERT INTO attend_tb VALUES(102, 3, '2021-12-3 18:58:00', '2021-12-3 19:05:00');
INSERT INTO attend_tb VALUES(108, 3, '2021-12-3 19:01:00', '2021-12-3 19:56:00');

输出:

SQL|91.25
R|60.33
Python|58.00

原站题解

select C.COURSE_NAME,ROUND(AVG(TIMESTAMPDIFF(MINUTE,B.in_datetime,B.out_datetime)),2) AS AVG_LEN
from course_tb C
inner join attend_tb B 
on C.course_id=B.course_id
GROUP BY C.COURSE_NAME
ORDER BY AVG_LEN DESC 

select c.course_name,
       round(avg(TIMESTAMPDIFF(minute,a.in_datetime,a.out_datetime)),2) avg_len
from course_tb c left join attend_tb a using(course_id)
group by c.course_name
order by avg_len desc

SELECT b.course_name,round(avg(TIMESTAMPDIFF(MINUTE,a.in_datetime,a.out_datetime)),2) as avg_Len
from attend_tb a join course_tb b
on a.course_id=b.course_id
GROUP by b.course_id,b.course_name
ORDER by avg_Len DESC

select course_name,round(avg(len),2)as avg_len
from
(
select a.course_id,course_name,TIMESTAMPDIFF(MINUTE,in_datetime,out_datetime)len
from attend_tb a left join course_tb b
on a.course_id=b.course_id)c
group by course_name
order by avg_len desc

SELECT course_name,
       ROUND(AVG(TIMESTAMPDIFF(MINUTE, in_datetime, out_datetime)), 2) AS avg_Len
FROM attend_tb a
JOIN course_tb c
ON a.course_id = c.course_id
GROUP BY course_name
ORDER BY avg_Len DESC