WebSams – 插班生成績SQL滙出 2026.01.02
select
a.schyear '年度',
a1.timeseq '測考',
a.classcode '班別',
a.classno '學號',
a.chname '姓名',
dateformat(a.FIRSTATTDATE, 'DD/MM/YYYY') '入學日期',
d1.omclass||'/'||d1.omclassbase '班名次',
d1.omclasslvl||'/'||d1.omclasslvlbase '級名次',
e.sysscore '中文',
f.sysscore '英文',
g.sysscore '數學',
h.sysscore '常識',
m.sysscore '人文',
n.sysscore '科學',
i.SYSGRADECONVCOMPCODE '音樂',
j.SYSGRADECONVCOMPCODE '視藝',
k.SYSGRADECONVCOMPCODE '體育',
l.SYSGRADECONVCOMPCODE 'I.T.',
d1.overcondgradeconversioncompcode '操行'
from vw_stu_lateststudent a
join tb_asr_time a1
on a1.timeseq=?
left outer join tb_asr_studassessdata d1
on a.suid=d1.suid and a.stuid=d1.stuid and d1.schyear=a.schyear and d1.timeseq=a1.timeseq
left outer join wsadmin.TB_ASR_SUBJASSESSDATA e
on a.SUID=e.SUID and a.STUID=e.STUID and a.SCHYEAR=e.SCHYEAR and e.TIMESEQ=a1.timeseq and e.SUBJCODE='080'
left outer join wsadmin.TB_ASR_SUBJASSESSDATA f
on a.SUID=f.SUID and a.STUID=f.STUID and a.SCHYEAR=f.SCHYEAR and f.TIMESEQ=a1.timeseq and f.SUBJCODE='165'
left outer join wsadmin.TB_ASR_SUBJASSESSDATA g
on a.SUID=g.SUID and a.STUID=g.STUID and a.SCHYEAR=g.SCHYEAR and g.TIMESEQ=a1.timeseq and g.SUBJCODE='280'
left outer join wsadmin.TB_ASR_SUBJASSESSDATA h
on a.SUID=h.SUID and a.STUID=h.STUID and a.SCHYEAR=h.SCHYEAR and h.TIMESEQ=a1.timeseq and h.SUBJCODE='205'
left outer join wsadmin.TB_ASR_SUBJASSESSDATA i
on a.SUID=i.SUID and a.STUID=i.STUID and a.SCHYEAR=i.SCHYEAR and i.TIMESEQ=a1.timeseq and i.SUBJCODE='300'
left outer join wsadmin.TB_ASR_SUBJASSESSDATA j
on a.SUID=j.SUID and a.STUID=j.STUID and a.SCHYEAR=j.SCHYEAR and j.TIMESEQ=a1.timeseq and j.SUBJCODE='432'
left outer join wsadmin.TB_ASR_SUBJASSESSDATA k
on a.SUID=k.SUID and a.STUID=k.STUID and a.SCHYEAR=k.SCHYEAR and k.TIMESEQ=a1.timeseq and k.SUBJCODE='310'
left outer join wsadmin.TB_ASR_SUBJASSESSDATA l
on a.SUID=l.SUID and a.STUID=l.STUID and a.SCHYEAR=l.SCHYEAR and l.TIMESEQ=a1.timeseq and l.SUBJCODE='906'
left outer join wsadmin.TB_ASR_SUBJASSESSDATA m
on a.SUID=m.SUID and a.STUID=m.STUID and a.SCHYEAR=m.SCHYEAR and m.TIMESEQ=a1.timeseq and m.SUBJCODE='323'
left outer join wsadmin.TB_ASR_SUBJASSESSDATA n
on a.SUID=n.SUID and a.STUID=n.STUID and a.SCHYEAR=n.SCHYEAR and n.TIMESEQ=a1.timeseq and n.SUBJCODE='324'
where a.schyear=? and a.classlvl not in ('p1') and a.stuid not in (select distinct b.stuid from VW_STU_LATESTSTUSCHREC b where b.schyear = a.schyear - 1)
order by a.classcode,a.classno