请问这道难题要怎么解，用mysql和oracle都行

问题遇到的现象和发生背景

img

img

img

问题相关代码，请勿粘贴截图

题目：

2）请完成一段SQL脚本，输出每位教师所教科目最高分的学生，输出信息包括教师姓名、教授课程、所教授学生的整体平均分、该科目整体平均分(该年级该科目平均分)、所教授学生的整体平均分年级名次，最高分分数，最高分学生姓名（如果并排分数，用逗号合并为一行显示）。

运行结果及报错内容
我的解答思路和尝试过的方法

不会

我想要达到的结果

想要正确解题思路，不是很懂

你的数据库创建语句发一下

  1. 数据有问题,19计算机2班有两个老师都是教的同一个科目,找不到老师和考试成绩之间的唯一关系,是不是教师的最后一行应该为204？
  2. "所教授学生的整体平均分"、"该科目整体平均分(该年级该科目平均分)" ,这两个栏位需要再解释一下,前者是指通过老师找到科目找到学生,再反向求这些学生的所有科目的平均分？还是只看这个老师教的科目的平均分？那这样和"该科目整体平均分“有什么区别？从你的数据里来看，反正一个老师只会教一个年级的一个科目？一个科目也只存在于一个年级？

WITH TP AS (
select st.studentid,
       st.sname,
       st.schoolyear,
       st.classno,
       sc.exid,
       sc.score,
       et.subjectid,
       sj.exname,
       t.TEACHER,
       avg(sc.score) over(partition  by t.TEACHER) T_AVG,
       avg(sc.score) over(partition  by SCHOOLYEAR,et.subjectid) Y_AVG,
       RANK() OVER(partition BY et.subjectid ORDER BY sc.score DESC) RK
  from Scores sc, Students st, Examinations et, Teach t, Subjects sj
 where sc.studentid = st.studentid
   and sc.exid = et.exid
   and t.subjectid = et.subjectid
   and t.classno = st.classno
   and t.subjectid = sj.subjectid
)
select TEACHER 教师,
       MIN(exname) 科目,
       round(MIN(T_AVG),2) 所授平均分,
       round(MIN(Y_AVG),2) 科目年级平均分,
       DENSE_RANK() OVER(PARTITION BY MIN(exname) ORDER BY MIN(T_AVG) DESC) 所授平均分名次,
       MAX(score) 最高分,
       LISTAGG(CASE
                 WHEN RK = 1 THEN
                  SNAME
               END,
               ',') within group (order by 1) 最高分姓名
  from TP GROUP BY TEACHER

img

用的oracle，LISTAGG这个函数在最新的几个oracle版本里变更了好多次，上面这个sql有一点点缺陷，就是逗号多了，需要用listagg(distinct)来处理，但不清楚你的oracle的版本，所以我没加，或者直接用正则替换把两个以上的逗号替换成一个逗号，再去掉首尾的逗号也行。
如果是11g以下的版本，直接用wm_concat(distinct ) 也行，但是这个函数是官方未公开函数，从12c版本起已经没有了，不建议使用