抽取 中、英、數、常等各科總平均分
select
c.classlvl as '班級',
s.classname as '班別',
c.classno as '班號',
c.chname as '中文姓名',
c.enname as '英文姓名',
a1.sysscore as '中文',
a2.sysscore as '英文',
a3.sysscore as '數學',
a4.sysscore as '常識',
a5.SYSGRADECONVCOMPCODE as '音樂',
a6.SYSGRADECONVCOMPCODE as '視藝',
a7.SYSGRADECONVCOMPCODE as '體育',
a8.SYSGRADECONVCOMPCODE as '普通話',
a9.SYSGRADECONVCOMPCODE as '電腦',
a10.sysscore as '人文',
a11.sysscore as '科學',
d1.syspercscore as '平均分',
d1.omclasslvl as '級名次',
d1.omclass as '班名次',
dc1.overcondgradeconversioncompcode as '操行',
o.ABSENTDAY as '缺席日數',
d1.COMMENTDESCCHI '整體評語'
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
left outer join tb_asr_subjassessdata a5
on c.suid=a5.suid and c.stuid=a5.stuid and a5.subjcode='300' and
c.schyear=a5.schyear and c.schlvl=a3.schlevel and
c.schsess=a5.schsession and c.classlvl=a5.classlevel and
a5.timeseq=d1.timeseq
left outer join tb_asr_subjassessdata a6
on c.suid=a6.suid and c.stuid=a6.stuid and a6.subjcode='432' and
c.schyear=a6.schyear and c.schlvl=a6.schlevel and
c.schsess=a6.schsession and c.classlvl=a6.classlevel and
a6.timeseq=d1.timeseq
left outer join tb_asr_subjassessdata a7
on c.suid=a3.suid and c.stuid=a7.stuid and a7.subjcode='310' and
c.schyear=a7.schyear and c.schlvl=a7.schlevel and
c.schsess=a7.schsession and c.classlvl=a7.classlevel and
a7.timeseq=d1.timeseq
left outer join tb_asr_subjassessdata a8
on c.suid=a8.suid and c.stuid=a8.stuid and a8.subjcode='350' and
c.schyear=a8.schyear and c.schlvl=a8.schlevel and
c.schsess=a8.schsession and c.classlvl=a8.classlevel and
a8.timeseq=d1.timeseq
left outer join tb_asr_subjassessdata a9
on c.suid=a9.suid and c.stuid=a9.stuid and a9.subjcode='115' and
c.schyear=a9.schyear and c.schlvl=a9.schlevel and
c.schsess=a9.schsession and c.classlvl=a9.classlevel and
a9.timeseq=d1.timeseq
left outer join tb_asr_subjassessdata a10
on c.suid=a10.suid and c.stuid=a10.stuid and a10.subjcode='323' and
c.schyear=a10.schyear and c.schlvl=a10.schlevel and
c.schsess=a10.schsession and c.classlvl=a10.classlevel and
a10.timeseq=d1.timeseq
left outer join tb_asr_subjassessdata a11
on c.suid=a11.suid and c.stuid=a11.stuid and a11.subjcode='324' and
c.schyear=a11.schyear and c.schlvl=a11.schlevel and
c.schsess=a11.schsession and c.classlvl=a11.classlevel and
a11.timeseq=d1.timeseq
left outer join tb_asr_studassessdata dc1
on c.suid=dc1.suid and c.stuid=dc1.stuid and
c.schyear=dc1.schyear and c.schlvl=dc1.schlevel and
c.schsess=dc1.schsession and c.classlvl=dc1.classlevel and
dc1.timeseq=d1.timeseq
left outer join TB_ASR_STUDMISCDATA o
on o.suid=c.suid and o.stuid=c.stuid and
o.schyear=c.schyear and o.schlevel=c.schlvl and
o.schsession=c.schsess and o.classlevel=c.classlvl and
o.timeseq=d1.timeseq
where c.schyear=? and d1.timeseq=? and c.classlvl=?
order by c.classcode, c.classno