CloudSAMS – 前 5% 學生成績與排名查詢

SELECT a.schyear '考試學年', 
b.timeseq '測考代號',
a.classlvl '級別',  
c.ch_des '科目(中文)',  
b.omclasslvl '科目級名次',  
a.classcode '班別',  
a.chname '中文姓名', 
a.enname 'English Name', 
b.sysscore '科目分數'  
from tb_asr_subjassessdata b left outer join  
tb_hse_common c on b.suid=c.suid and b.subjcode=c.code_id and c.tb_id='sbj' left outer join  
vw_stu_lateststudent a on b.suid=a.suid and b.stuid=a.stuid and b.schyear=a.schyear  
where  
b.schyear=? and b.timeseq=? and  
b.omclasslvl <  
(select max(x.omclasslvl) *0.05 from tb_asr_subjassessdata x  
where b.SUID = x.suid and b.SCHYEAR = x.schyear and b.SCHLEVEL = x.schlevel and b.SCHSESSION = x.schsession and b.CLASSLEVEL = x.classlevel and b.TIMESEQ = x.timeseq and  
b.MOI = x.moi and b.SUBJCODE in ('080','165','280','205','300','432','323','324')) 
order by a.classlvl, c.ch_des, b.omclasslvl