SQL 提取學生級名次及缺席遲到紀錄及以往就讀學校紀錄

你好:
想請教如要一次過抽取以下資料(每一學期計)，SQL應怎樣做?
學生姓名，班別，學號，班名次，級名次，操行，缺席次數，遲到次數，以前就讀的學校

謝謝

 

你好

可以參考一下以下的SQL, 可以分別抽取T1和T2的操行和缺席

select
a.chname '姓名',
a.classcode '班別',
a.classno '學號',
d.omclass '全年班名次',
d.omclasslvl '全年級名次',
d.syspercscore '全年平均分',
d1.overcondgradeconversioncompcode 'T1 操行',
d2.overcondgradeconversioncompcode 'T2 操行',
isnull(q.ADJABSENTDAY, q.ABSENTDAY) 'T1 缺席',
isnull(r.ADJABSENTDAY, r.ABSENTDAY) 'T2 缺席',
isnull(q.ADJLATEDAY, q.LATEDAY) 'T1 遲到',
isnull(r.ADJLATEDAY, r.LATEDAY) 'T2 遲到',
a.SCHFROM '以前就讀學校'
from wsadmin.vw_stu_lateststudent a
left outer join wsadmin.tb_asr_studassessdata d
on a.suid=d.suid and a.stuid=d.stuid and d.schyear=a.schyear and d.timeseq=1000
left outer join wsadmin.tb_asr_studassessdata d1
on a.suid=d1.suid and a.stuid=d1.stuid and d1.schyear=a.schyear and d1.timeseq=1100
left outer join wsadmin.tb_asr_studassessdata d2
on a.suid=d2.suid and a.stuid=d2.stuid and d2.schyear=a.schyear and d2.timeseq=1200
left outer join wsadmin.TB_ASR_STUDMISCDATA q
on a.suid=q.suid and a.stuid=q.stuid and a.schyear=q.schyear and q.schlevel=a.schlvl and q.schsession=a.schsess and q.classlevel=a.classlvl and q.timeseq=1100
left outer join wsadmin.TB_ASR_STUDMISCDATA r
on a.suid=r.suid and a.stuid=r.stuid and a.schyear=r.schyear and r.schlevel=a.schlvl and r.schsession=a.schsess and r.classlevel=a.classlvl and r.timeseq=1200
where a.schyear=? and a.classlvl=?
order by 1,2