select stu,sub,ltrim(max(sys_connect_by_path(score,',')),',') score from(
select stu,sub,score,--学员、科目、分数
row_number()over(partition by stu,sub order by score desc)rn1,--根据学员、科目进行分组,在组内按照分数进行降序排序
(row_number()over(order by stu,sub,score desc))+(dense_rank()over(order by stu,sub))rnid --构造树的条件
from(
select 'sub1' sub,100 score,'stu1' stu from dual
union all select 'sub1',100,'stu1' from dual
union all select 'sub1',60,'stu2' from dual
union all select 'sub1',90,'stu2' from dual
union all select 'sub2',80,'stu1' from dual
union all select 'sub2',70,'stu1' from dual
union all select 'sub2',90,'stu2' from dual
union all select 'sub2',100,'stu2' from dual)
)start with rn1=1 connect by rnid-1= pri