ORACLE 中如何取到自己想要的数据

学生teacher学习评价品德评价学习评价分数品德评价分数
张三王AB8698
张三李BA+7556
李四钟AA-7098
李四陈AB6089
王五钱A+A7797

规则是取学习评价最高分数对应的学习评价和最低分数的品德评价
结果显示为：

学生teachers学习评价品德评价
张三王,李AA+
李四钟,陈AB
王五钱A+A

SELECT
name,
GROUP_CONCAT(teacher),

SUBSTRING(
max(concat(LPAD(xx_score,3,"0"),",",xx_pj)),5),
SUBSTRING(
min(concat(LPAD(pd_score,3,"0"),",",pd_pj)),5)

FROM
student_score

group by name

上面这个是mysql的

SELECT
name,
LISTAGG(teacher,",") WITHIN GROUP(ORDER BY teacher),

SUBSTR(
max(concat(LPAD(xx_score,3,"0"),",",xx_pj)),5),
SUBSTR(
min(concat(LPAD(pd_score,3,"0"),",",pd_pj)),5)

FROM
student_score

group by name

下面这个是oracle的

select 学生,
listagg(teacher,',') within group (order by 1) teachers,
max(学习评价) 学习评价,
max(品德评价) 品德评价 
from (
select 学生,
 teacher,
first_value(学习评价) over(partition by 学生 order by 学习评价分数 desc) 学习评价,
first_value(品德评价) over(partition by 学生 order by 品德评价分数 ) 品德评价
from t) 
group by 学生

img