问题1
查询所有课程总成绩前三名的按照升序排在最开头,其余数据排序保持默认,显示(学号、成绩、总成绩)
解决1
select a.rn,a.student_no,
a.core,
a.total_core,
dense_rank() OVER(ORDER BY a.total_core DESC nulls last) ranks
from (select rownum rn,
s.student_no,
core,
sum(core) over(Partition by s.student_no) total_core
from HAND_STUDENT s, HAND_STUDENT_CORE sc
where s.student_no = sc.student_no(+)) a
ORDER BY CASE
WHEN ranks <= 3 THEN
-ranks
ELSE
null
END,a.rn;
结果1
问题2
查询所有课程成绩前三名的按照升序排在最开头,其余数据排序保持默认,显示(学号、成绩)
解决2
SELECT hs.student_no,
hs.core
FROM (SELECT rownum rn,
hsc.student_no,
hsc.core,
row_number() OVER(ORDER BY hsc.core DESC) ranks
FROM hand_student_core hsc) hs
ORDER BY CASE WHEN ranks <= 3 THEN -ranks ELSE null END,rn;