SQL 跨班別科目各分組學生名單及任教老師

56784352 · 2017-10-31

請問如何用SQL提取跨班別科目各分組 (sub-group) 學生名單及任教老師? 如中一中文有3個分組, Chi1B7CGpA,
Chi1B7CGpB, Chi1B7CGpC, 我們想要ChiB7CGpA 等每個分組學生名單及任教老師, 謝謝

58019970 · 2017-11-01

老師請參考以下:

https://forum.hkedcity.net/index.php?threads/抽取班別、科目、科任老師資料-跨班及分組.147101/

56784352 · 2017-11-02

這條SQL 抽取出來記錄很多會重覆, 重覆記錄唯一不同的欄位是任教老師，而多出來的記錄的那位任教老師並沒有任教該組，麻煩跟進

另外，我們並沒有subject group，如有需要可省略，謝謝

58019970 · 2017-11-02

老師是使用 #2 的SQL嗎?
測試過SQL沒有問題，老師是不是設定了同組有多過一位任教老師?如果是，重覆記錄會根據不同任教老師，而這個問題SQL是不能分辨的。

另外，多出的不需要欄位老師可以自行刪除。

希望進一步了解貴校問題以作跟進，請聯絡貴校網上校管系統聯絡主任以提供更多資料，謝謝！

56784352 · 2017-11-02

我們十分肯定每個分組只有一位老師，會找校主跟進

55090821 · 2017-11-13

我都測試了一下，load 左其他年份的老師出來

58019970 · 2017-11-16

老師請參考以下SQL語句:

select distinct
a.REGNO 'REGNO',
a.hkid 'HKID',
a.strn 'STRN',
a.classlvl 'Class Level',
a.classcode 'Class',
a.classno 'Class No.',
a.chname 'Student Name (Chi)',
a.enname 'Student Name (Eng)',
a.sex 'Sex',
dateformat(a.dob, 'DD/MM/YYYY') as DOB,
c.subjcode 'Subject Code',
b.ch_des 'Subject Name (Chi)',
b.en_des 'Subject Name (Eng)',
(case when c.CLSSUBJTYPE = 'E' then '選修'
else (case when c.CLSSUBJTYPE = 'G' then '科目組別'
else (case when c.CLSSUBJTYPE = 'X' then '跨班別科目'
else null end) end) end) 'Class Type',
(case when c.CLSSUBJTYPE = 'E' then 'X1'
else (case when c.CLSSUBJTYPE = 'G' then '科目組別'
else (case when c.CLSSUBJTYPE = 'X' then i.subjblkcode
else null end) end) end) 'X1X2X3',
b.ch_sname 'Elective / Cross Class Subject Shortname (Chi) ',
b.en_sname 'Elective / Cross Class Subject Shortname (Eng) ',
h.subjgrpdesc 'CrossClassSubjectGroupName',
h.subjgrpcode 'CrossClassSubjectGroupCode',
i.divsubjcode 'CrossClassSubjectSubGroupCode',
m.ch_des 'MOI',
(case when c.CLSSUBJTYPE = 'C' then '必修' when c.CLSSUBJTYPE = 'E' then '選修' when c.CLSSUBJTYPE = 'G' then '科目組別' else 'Cross class subject' end) '科目類別',
o.CHIDESC '科目集名稱',
(case when c.CROSSSUBJGRP is null then d.name_chi else l.name_chi end)
'Teacher Name (Chi)',
(case when c.CROSSSUBJGRP is null then d.name_eng else l.name_eng end)
' Teacher Name (Eng)',
(case when c.CROSSSUBJGRP is null then d.shortname else l.shortname end)
'Teacher (shortname)'

from vw_stu_lateststudent a
join tb_stu_stusubj c
on a.suid=c.suid and a.stuid=c.stuid and a.stuschrecid=c.stuschrecid
left outer join tb_hse_common m
on a.suid=m.suid and c.moi=m.code_id and m.tb_id='MOI'
left outer join tb_hse_common b
on a.suid=b.suid and c.subjcode=b.code_id and b.tb_id='SBJ'

left outer join TB_SCH_CLSSUBJTCHR e
on a.SUID = e.SUID and a.SCHYEAR = e.SCHYEAR and a.SCHLVL = e.SCHLEVEL and a.schsess = e.SCHSESSION and a.classlvl = e.CLASSLEVEL and a.CLASSCODE = e.CLASSCODE and c.SUBJCODE = e.SUBJCODE and c.MOI = e.MOI
left outer join VW_ASR_STAFF d
on a.SUID = d.SUID and e.STAFFCODE = d.STAFFCODE and e.STAFFSUID=d.STAFFSUID

left outer join tb_sch_divsubjgrp h
on a.suid=h.suid and a.schlvl=h.schlevel and a.schsess=h.schsession and a.schyear=h.schyear and c.crosssubjgrp=h.subjgrpcode
left outer join tb_sch_dsgsubj i
on a.suid=i.suid and a.schlvl=i.schlevel and a.schsess=i.schsession and a.schyear=i.schyear and h.subjgrpcode=i.subjgrpcode and c.crosssubjcode=i.divsubjcode
left outer join tb_sch_dsgsubjcomp j
on a.suid=j.suid and a.schlvl=j.schlevel and a.schsess=j.schsession and a.schyear=j.schyear and h.subjgrpcode=j.subjgrpcode and c.crosssubjcode=j.divsubjcode
left outer join TB_SCH_DSGSUBJTCHR k on a.suid=k.suid and a.schlvl=k.SCHLEVEL and a.schsess=k.SCHSESSION and h.subjgrpcode=k.SUBJGRPCODE and i.divsubjcode=k.DIVSUBJCODE
and k.STAFFSEQ=1 and a.schyear=k.schyear
left outer join VW_ASR_STAFF l
on a.SUID=l.SUID and k.STAFFCODE=l.STAFFCODE and k.STAFFSUID=l.STAFFSUID
left outer join CD_SCH_SUBJBLK o
on a.SUID=o.SUID and i.SUBJBLKCODE=o.SUBJBLKCODE

where a.schyear=? and c.clssubjtype in ('E', 'X')
group by a.classcode, a.classno, a.chname, a.enname,c.clssubjtype,
b.ch_des, b.en_des, a.regno, a.hkid, a.strn, a.classlvl, a.sex, a.dob, a.SUID, a.schyear, c.subjcode, c.MOI, c.CROSSSUBJGRP, b.ch_sname, b.en_sname, d.name_chi, l.name_chi, d.name_eng, l.name_eng, d.shortname, l.shortname, o.CHIDESC, m.ch_des, i.subjblkcode, h.subjgrpdesc, h.subjgrpcode, i.divsubjcode
order by a.classcode, a.classno, a.chname, b.en_des
Click to expand...

55090821 · 2017-11-23

謝謝，可以load 到，不過科目有2、3個老師的，就只會出現第一個老師，第二個老師就不會顯示

58019970 · 2017-11-24

老師請試一下把紅色部分刪除:

left outer join TB_SCH_DSGSUBJTCHR k on a.suid=k.suid and a.schlvl=k.SCHLEVEL and a.schsess=k.SCHSESSION and h.subjgrpcode=k.SUBJGRPCODE and i.divsubjcode=k.DIVSUBJCODE
and k.STAFFSEQ=1 and a.schyear=k.schyear
Click to expand...

55090821 · 2017-11-24

可以了，謝謝

教城討論區

SQL 跨班別科目各分組學生名單及任教老師

tr-angus
Expand Collapse

edb-jackal
Expand Collapse

tr-angus
Expand Collapse

edb-jackal
Expand Collapse

tr-angus
Expand Collapse

rockfulam
Expand Collapse

edb-jackal
Expand Collapse

rockfulam
Expand Collapse

edb-jackal
Expand Collapse

rockfulam
Expand Collapse

快捷搜尋

SQL 跨班別科目各分組學生名單及任教老師

tr-angus Expand Collapse

edb-jackal Expand Collapse

tr-angus Expand Collapse

edb-jackal Expand Collapse

tr-angus Expand Collapse

rockfulam Expand Collapse

edb-jackal Expand Collapse

rockfulam Expand Collapse

edb-jackal Expand Collapse

rockfulam Expand Collapse

tr-angus
Expand Collapse

edb-jackal
Expand Collapse

tr-angus
Expand Collapse

edb-jackal
Expand Collapse

tr-angus
Expand Collapse

rockfulam
Expand Collapse

edb-jackal
Expand Collapse

rockfulam
Expand Collapse

edb-jackal
Expand Collapse

rockfulam
Expand Collapse