# SQL 中、英、數、常頭五名學生及積分

1. ### nickname-872660 Expand Collapse

文章:
27
讚:
0
想取T1 SQL每級中、英、數、常頭五名學生積分及等及

#1 nickname-872660, 2023-11-13
2. ### edb-brianchow Expand Collapse

文章:
9
讚:
0
老師可試試以下SQL，由於每科頭五名的同學都有不同，所以建議老師使用EXCEL格式做匯出，然後利用EXCEL的排序功能排序。另外T1的timeseq是1100。

select
c.classlvl as '班級',
s.classname as '班別',
c.classno as '班號',
c.chname as '中文姓名',
c.enname as '英文姓名',
c.sex as '性別',
a1.sysscore as '中文',
a2.sysscore as '英文',
a3.sysscore as '數學',
a4.sysscore as '常識',

d1.syspercscore as '平均分',
d1.omclasslvl as '級名次',
d1.omclass as '班名次',

from vw_stu_lateststudent c

left outer join TB_SCH_SCHCLASS s on s.SUID=c.SUID and s.SCHYEAR=c.SCHYEAR and s.SCHLEVEL=c.SCHLVL and s.SCHSESSION=c.SCHSESS and s.CLASSLEVEL=c.CLASSLVL and s.CLASSCODE=c.CLASSCODE
left outer join TB_ASR_STUDASSESSDATA d1 on c.suid=d1.suid and c.stuid=d1.stuid and c.schyear=d1.schyear and c.schlvl=d1.schlevel and c.schsess=d1.schsession and c.classlvl=d1.classlevel
left outer join TB_ASR_SUBJASSESSDATA a1 on c.suid=a1.suid and c.stuid=a1.stuid and a1.subjcode='080' and c.schyear=a1.schyear and c.schlvl=a1.schlevel and c.schsess=a1.schsession and c.classlvl=a1.classlevel and a1.timeseq=d1.timeseq
left outer join TB_ASR_SUBJASSESSDATA a2 on c.suid=a2.suid and c.stuid=a2.stuid and a2.subjcode='165' and c.schyear=a2.schyear and c.schlvl=a2.schlevel and c.schsess=a2.schsession and c.classlvl=a2.classlevel and a2.timeseq=d1.timeseq
left outer join TB_ASR_SUBJASSESSDATA a3 on c.suid=a3.suid and c.stuid=a3.stuid and a3.subjcode='280' and c.schyear=a3.schyear and c.schlvl=a3.schlevel and c.schsess=a3.schsession and c.classlvl=a3.classlevel and a3.timeseq=d1.timeseq
left outer join TB_ASR_SUBJASSESSDATA a4 on c.suid=a4.suid and c.stuid=a4.stuid and a4.subjcode='205' and c.schyear=a4.schyear and c.schlvl=a4.schlevel and c.schsess=a4.schsession and c.classlvl=a4.classlevel and a4.timeseq=d1.timeseq

where c.schyear=? and d1.timeseq=? and c.classlvl=? order by c.classcode, c.classno

#2 edb-brianchow, 2023-11-13