我正在使用MySQL 8.0 Table NameL:sc（score）
列：student_id、course_id、score
一些虚拟数据：

create table sc(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into sc values('01' , '01' , 80);
insert into sc values('01' , '02' , 90);
insert into sc values('01' , '03' , 99);
insert into sc values('02' , '01' , 70);
insert into sc values('02' , '02' , 60);
insert into sc values('02' , '03' , 80);
insert into sc values('03' , '01' , 80);
insert into sc values('03' , '02' , 80);
insert into sc values('03' , '03' , 80);
insert into sc values('04' , '01' , 50);
insert into sc values('04' , '02' , 30);
insert into sc values('04' , '03' , 20);
insert into sc values('05' , '01' , 76);
insert into sc values('05' , '02' , 87);
insert into sc values('06' , '01' , 31);
insert into sc values('06' , '03' , 34);
insert into sc values('07' , '02' , 89);
insert into sc values('07' , '03' , 98);

问题是：输出所有分数和每个学生的平均分数，并按平均分数的降序排列结果
我想出了两个解决方案：

-- Solution 1
SELECT
  sc.*,
  AVG(score) OVER (PARTITION BY sid) AS avg_score
FROM sc
ORDER BY avg_score DESC

-- Solution 2
select *  from sc 
left join (
    select sid,avg(score) as avscore from sc 
    group by sid
    )r 
on sc.sid = r.sid
order by avscore desc;

但是，如果表真的很大，这两种解决方案的效率性能是否存在差异？

非常感谢。