SQL 求SQL 抽取每班學生考試成績資料

請問如何利用SQL抽取學生的成績資料?
包括

班別
學號
姓名
性別
T1平均分
T2平均分
T1總分
T2總分
T2操行
T1中文分
T1英文分
T1數學分
T1常識分
T2中文分
T2英文分
T2數學分
T2常識分

謝謝!

 

你好,

沒有T1、T2總分, 只得平均分。請參考以下的SQL。

select
a.classcode '班別',
a.classno '班號',
a.enname '英文姓名',
a.chname '中文姓名',
a.sex '性別',

b.syspercscore 'T1 總平均分',
d.syspercscore 'T2 總平均分',
f.syspercscore '全年總平均分',


g.overcondgradeconversioncompcode 'T1 操行',
h.overcondgradeconversioncompcode 'T2 操行',

i.sysscore 'T1中文總分',
j.sysscore 'T1英文總分',
k.sysscore 'T1數學總分',
l.sysscore 'T1常識總分',

m.sysscore 'T2中文總分',
n.sysscore 'T2英文總分',
o.sysscore 'T2數學總分',
p.sysscore 'T2常識總分'

from wsadmin.vw_stu_lateststudent a
left outer join wsadmin.tb_asr_studassessdata b
on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear and b.timeseq=1100
left outer join wsadmin.tb_asr_studassessdata d
on a.suid=d.suid and a.stuid=d.stuid and a.schyear=d.schyear and d.timeseq=1200
left outer join wsadmin.tb_asr_studassessdata f
on a.suid=f.suid and a.stuid=f.stuid and a.schyear=f.schyear and f.timeseq=1000
left outer join wsadmin.tb_asr_studassessdata g
on a.suid=g.suid and a.stuid=g.stuid and a.schyear=g.schyear and g.timeseq=1100
left outer join wsadmin.tb_asr_studassessdata h
on a.suid=h.suid and a.stuid=h.stuid and a.schyear=h.schyear and h.timeseq=1200

left outer join wsadmin.TB_ASR_SUBJASSESSDATA i
on a.SUID=i.SUID and a.SCHYEAR=i.SCHYEAR and a.STUID=i.STUID and i.SUBJCODE='080' and i.TIMESEQ=1100
left outer join wsadmin.TB_ASR_SUBJASSESSDATA j
on a.SUID=j.SUID and a.SCHYEAR=j.SCHYEAR and a.STUID=j.STUID and j.SUBJCODE='165' and j.TIMESEQ=1100
left outer join wsadmin.TB_ASR_SUBJASSESSDATA k
on a.SUID=k.SUID and a.SCHYEAR=k.SCHYEAR and a.STUID=k.STUID and k.SUBJCODE='280' and k.TIMESEQ=1100
left outer join wsadmin.TB_ASR_SUBJASSESSDATA l
on a.SUID=l.SUID and a.SCHYEAR=l.SCHYEAR and a.STUID=l.STUID and l.SUBJCODE='205' and l.TIMESEQ=1100

left outer join wsadmin.TB_ASR_SUBJASSESSDATA m
on a.SUID=m.SUID and a.SCHYEAR=m.SCHYEAR and a.STUID=m.STUID and m.SUBJCODE='080' and m.TIMESEQ=1200
left outer join wsadmin.TB_ASR_SUBJASSESSDATA n
on a.SUID=n.SUID and a.SCHYEAR=n.SCHYEAR and a.STUID=n.STUID and n.SUBJCODE='165' and n.TIMESEQ=1200
left outer join wsadmin.TB_ASR_SUBJASSESSDATA o
on a.SUID=o.SUID and a.SCHYEAR=o.SCHYEAR and a.STUID=o.STUID and o.SUBJCODE='280' and o.TIMESEQ=1200
left outer join wsadmin.TB_ASR_SUBJASSESSDATA p
on a.SUID=p.SUID and a.SCHYEAR=p.SCHYEAR and a.STUID=p.STUID and p.SUBJCODE='205' and p.TIMESEQ=1200

where a.schyear=? and a.classlvl=?
order by a.classcode, a.classno, a.enname