學生成績 請求 SQL (提取某一學期的學生成績模組內的獎懲遲缺操行列表)

求教：希望利用資料選取功能以下資料列表

全校學生
某一學期的學生成績模組內
班別、班號、姓名(中)、姓名(英)、操行、評語、優點、小功、大功、缺點、小過、大過、缺席日數、遲到次數

 

你好,

在CDR內都有一些SQL可以參考, 你可以參考一下以下的SQL

17. 抽取 學生全年中英數通識成績 及 上下學期的操行成績等

 

select
a.classcode '班別',
a.chname '姓名',
b.CH_DES '社別',
e.sysscore '中文',
f.sysscore '英文',
g.sysscore '數學',
h.sysscore '通識',
d.syspercscore '全年平均分',
d.omclasslvl '全年排名',
d1.overcondgradeconversioncompcode 'T1 操行',
d2.overcondgradeconversioncompcode 'T2 操行',
r.ADJLVL1MERIT 'T2 獎勵級別1',
r.ADJLVL2MERIT 'T2 獎勵級別2',
r.ADJLVL3MERIT 'T2 獎勵級別3',
r.ADJLVL4MERIT 'T2 獎勵級別4',
r.ADJLVL5MERIT 'T2 獎勵級別5',
r.ADJLVL1DEMERIT 'T2 懲罰級別1',
r.ADJLVL2DEMERIT 'T2 懲罰級別2',
r.ADJLVL3DEMERIT 'T2 懲罰級別3',
r.ADJLVL4DEMERIT 'T2 懲罰級別4',
r.ADJLVL5DEMERIT 'T2 懲罰級別5',
isnull(q.ADJABSENTDAY, q.ABSENTDAY) 'T1 缺席',
isnull(r.ADJABSENTDAY, r.ABSENTDAY) 'T2 缺席',
isnull(q.ADJLATEDAY, q.LATEDAY) 'T1 遲到',
isnull(r.ADJLATEDAY, r.LATEDAY) 'T2 遲到',
d2.COMMENTDESCENG 'T2 評語'
from vw_stu_lateststudent a
left outer join TB_HSE_COMMON b
on a.SUID=b.SUID and b.CODE_ID=a.SCHHOUSE and b.TB_ID='SCHHUS'
left outer join 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 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 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_SUBJASSESSDATA e
on a.SUID = e.SUID and a.STUID = e.STUID and a.SCHYEAR = e.SCHYEAR and e.TIMESEQ = 1000 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 = 1000 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 = 1000 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 = 1000 and h.SUBJCODE = '265'
left outer join 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 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