學生成績 提取學生成績及任教老師

以下SQL 提取學生學期成績(T?A1,T?A2, T?) 。希望一併提取
- 科任老師
- 中文科分卷
- 英文科分卷

應如何編修？

select
a.classcode,
a.classno,
a.sex,
a.enname,
a.chname,
c.en_des,
c.ch_des,
b1.sysscore '平時分',
b2.sysscore '考試分',
b.sysscore '成績',
b.OMCLASSLVL '名次'
from vw_stu_lateststudent a

left outer join tb_hse_common c
on a.suid=c.suid and c.tb_id='SBJ'

left outer join tb_asr_subjassessdata b1
on a.suid=b1.suid and a.schlvl=b1.schlevel and a.schsess=b1.schsession and a.classlvl=b1.classlevel and a.schyear=b1.schyear and a.stuid=b1.stuid and c.code_id=b1.subjcode

left outer join tb_asr_subjassessdata b2
on a.suid=b2.suid and a.schlvl=b2.schlevel and a.schsess=b2.schsession and a.classlvl=b2.classlevel and a.schyear=b2.schyear and a.stuid=b2.stuid
and c.code_id=b2.subjcode

left outer join tb_asr_subjassessdata b
on a.suid=b.suid and a.schlvl=b.schlevel and a.schsess=b.schsession and a.classlvl=b.classlevel and a.schyear=b.schyear and a.stuid=b.stuid
and c.code_id=b.subjcode

where a.schyear=? and b.timeseq=? and b1.timeseq=(b.timeseq+1) and b2.timeseq=(b.timeseq+2) and a.classlvl in ('S1','S2','S3','S4','S5','S6')
order by a.classlvl, c.en_des, a.classcode, a.classno

 

你好,

可以參考在CDR內的 模組資料 > 學生成績 > 常用SQL
https://cdr.websams.edb.gov.hk/模組資料/學生成績/常用SQL/

有一條SQL比較接近你要求
20. 抽取 各科成績(包括分卷) 及 相關任教老師
考績 班別 班號 姓名 Name Subject 科目 成績 分卷科目 分卷成績 任教老師 Teacher

另外，你所提供的SQL並沒有抽取分卷的資料，建議你用以下的SQL再作修改比較方便。


select

b.timeseq '考績',
a.classcode '班別',
a.classno '班號',
a.chname '姓名',
a.enname 'Name',
c.en_des 'Subject',
c.ch_des '科目',
b.sysscore '成績',
b.OMCLASSLVL '名次',
d2.ch_des '分卷科目',
d1.sysscore '分卷成績',
d1.OMCLASSLVL '分卷名次',

(case when e.CROSSSUBJGRP is null then (select list(h.name_chi, ', ') from wsadmin.TB_SCH_CLSSUBJTCHR g
join wsadmin.VW_ASR_STAFF h on a.SUID = h.SUID and g.STAFFCODE = h.STAFFCODE
where a.SUID = g.SUID and a.SCHYEAR = g.SCHYEAR and a.SCHLVL = g.SCHLEVEL and a.schsess = g.SCHSESSION and a.classlvl = g.CLASSLEVEL and a.CLASSCODE = g.CLASSCODE and b.SUBJCODE = g.SUBJCODE and b.MOI = g.MOI) else (select list(j.name_chi, ', ')
from wsadmin.TB_SCH_DSGSUBJTCHR i
join wsadmin.VW_ASR_STAFF j on a.SUID = j.SUID and i.STAFFCODE = j.STAFFCODE
where a.SUID = i.SUID and a.SCHYEAR = i.SCHYEAR and a.SCHLVL = i.SCHLEVEL and a.schsess = i.SCHSESSION and e.CROSSSUBJCODE=i.DIVSUBJCODE and e.CROSSSUBJGRP=i.SUBJGRPCODE) end) '任教老師',

(case when e.CROSSSUBJGRP is null then (select list(h.name_eng, ', ')
from wsadmin.TB_SCH_CLSSUBJTCHR g
join wsadmin.VW_ASR_STAFF h on a.SUID = h.SUID and g.STAFFCODE = h.STAFFCODE where a.SUID = g.SUID and a.SCHYEAR = g.SCHYEAR and a.SCHLVL = g.SCHLEVEL and a.schsess = g.SCHSESSION and a.classlvl = g.CLASSLEVEL and a.CLASSCODE = g.CLASSCODE and b.SUBJCODE = g.SUBJCODE and b.MOI = g.MOI) else (select list(j.name_eng, ', ')
from wsadmin.TB_SCH_DSGSUBJTCHR i
join wsadmin.VW_ASR_STAFF j on a.SUID = j.SUID and i.STAFFCODE = j.STAFFCODE where a.SUID = i.SUID and a.SCHYEAR = i.SCHYEAR and a.SCHLVL = i.SCHLEVEL and a.schsess = i.SCHSESSION and e.CROSSSUBJCODE=i.DIVSUBJCODE and e.CROSSSUBJGRP=i.SUBJGRPCODE) end) 'Teacher'

from vw_stu_lateststudent a
left outer join tb_asr_subjassessdata b on a.suid=b.suid and a.schlvl=b.schlevel and a.schsess=b.schsession and a.classlvl=b.classlevel and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=?
left outer join tb_hse_common c on a.suid=c.suid and b.subjcode=c.code_id and c.tb_id='SBJ'
left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d1 on a.SUID = d1.SUID and a.SCHLVL = d1.SCHLEVEL and a.SCHSESS = d1.SCHSESSION and a.SCHYEAR = d1.SCHYEAR and a.STUID = d1.STUID and b.SUBJCODE = d1.SUBJCODE and b.TIMESEQ = d1.TIMESEQ
left outer join wsadmin.TB_HSE_SBJCMP d2 on d1.SUID = d2.SUID and d1.SUBJCODE = d2.SBJ_CODE and d1.SUBJCOMPCODE = d2.CODE_ID
left outer join wsadmin.TB_STU_STUSUBJ e on a.suid=e.suid and a.stuid=e.stuid and b.SUBJCODE = e.SUBJCODE and a.STUSCHRECID=e.STUSCHRECID
where a.schyear=?
order by a.classlvl, a.classcode, a.classno, c.ch_des

 

你好，請參考以下的SQL。
按照你原本SQL的需求，在上面的SQL範式加以更改。
最後加入CLASSLVL抽取S1-S6年級的資料。

select
b.timeseq '考績',
a.classcode '班別',
a.classno '班號',
a.chname '姓名',
a.enname 'Name',
c.en_des 'Subject',
c.ch_des '科目',
b.sysscore '成績',
b.OMCLASSLVL '名次',
d2.ch_des '分卷科目',
d1.sysscore '分卷成績',
d1.OMCLASSLVL '分卷名次',

(case when e.CROSSSUBJGRP is null then (select shortname
from wsadmin.TB_SCH_CLSSUBJTCHR g
join wsadmin.VW_ASR_STAFF h on a.SUID = h.SUID and g.STAFFCODE = h.STAFFCODE where a.SUID = g.SUID and a.SCHYEAR = g.SCHYEAR and a.SCHLVL = g.SCHLEVEL and a.schsess = g.SCHSESSION and a.classlvl = g.CLASSLEVEL and a.CLASSCODE = g.CLASSCODE and b.SUBJCODE = g.SUBJCODE and b.MOI = g.MOI) else (select shortname
from wsadmin.TB_SCH_DSGSUBJTCHR i
join wsadmin.VW_ASR_STAFF j on a.SUID = j.SUID and i.STAFFCODE = j.STAFFCODE where a.SUID = i.SUID and a.SCHYEAR = i.SCHYEAR and a.SCHLVL = i.SCHLEVEL and a.schsess = i.SCHSESSION and e.CROSSSUBJCODE=i.DIVSUBJCODE and e.CROSSSUBJGRP=i.SUBJGRPCODE) end) 'Teacher'

from wsadmin.vw_stu_lateststudent a
left outer join wsadmin.tb_asr_subjassessdata b on a.suid=b.suid and a.schlvl=b.schlevel and a.schsess=b.schsession
and a.classlvl=b.classlevel and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=?
left outer join wsadmin.tb_hse_common c on a.suid=c.suid and b.subjcode=c.code_id and c.tb_id='SBJ'
left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d1 on a.SUID = d1.SUID and a.SCHLVL = d1.SCHLEVEL and a.SCHSESS = d1.SCHSESSION and a.SCHYEAR = d1.SCHYEAR and a.STUID = d1.STUID and b.SUBJCODE = d1.SUBJCODE and b.TIMESEQ = d1.TIMESEQ
left outer join wsadmin.TB_HSE_SBJCMP d2 on d1.SUID = d2.SUID and d1.SUBJCODE = d2.SBJ_CODE and d1.SUBJCOMPCODE = d2.CODE_ID
left outer join wsadmin.TB_STU_STUSUBJ e on a.suid=e.suid and a.stuid=e.stuid and b.SUBJCODE = e.SUBJCODE and a.STUSCHRECID=e.STUSCHRECID

where a.schyear=? and a.classlvl in ('S1','S2','S3','S4','S5','S6')
order by a.classlvl, a.classcode, a.classno, c.ch_des