SQL 抽取各班任教老師名單，任教科目及電郵(如有)

請問如何用SQL抽取抽取各班任教老師名單，任教科目及電郵(如有)

e.g. 1A teachername1 subject_taught teacher1_email
1A teachername2 subject_taught teacher2_email
.....
6A teachername5 subject_taught teacher5_email

 

請老師參考以下的 SQL 句子，並以校長權限登入才可取出資料：

select
SCHYEAR,
CLASSLEVEL,
CLASSCODE,
SUBJECT,
MOI,
TEACHER,
EMAILADDRESS

from (select a.SCHYEAR, a.SCHLEVEL, a.SCHSESSION, a.CLASSLEVEL, a.CLASSCODE, d.NAME_CHI 'TEACHER', b.CH_DES 'SUBJECT', c.CH_DES 'MOI', h.EMAILADDRESS
from TB_SCH_CLSSUBJTCHR a
join TB_HSE_COMMON b
on a.SUID = b.SUID and b.TB_ID = 'SBJ' and a.SUBJCODE = b.CODE_ID
join TB_HSE_COMMON c
on a.SUID = c.SUID and c.TB_ID = 'MOI' and a.MOI = c.CODE_ID
left outer join VW_ASR_STAFF d
on a.SUID = d.SUID and a.STAFFCODE = d.STAFFCODE
left outer join TB_STF_MASTER h
on a.SUID=h.SUID and d.STAFFCODE=h.STAFFCODE

union all

select a.SCHYEAR, a.SCHLEVEL, a.SCHSESSION, a.CLASSLEVEL, a.CLASSCODE, g.NAME_CHI, e.CH_DES, f.CH_DES, h.EMAILADDRESS
from TB_SCH_CLSSUBJGRP a
join TB_SCH_DIVSUBJGRP b
on a.SUID = b.SUID and a.SCHLEVEL = b.SCHLEVEL and a.SCHSESSION = b.SCHSESSION and a.SCHYEAR = b.SCHYEAR and a.SUBJGRPCODE = b.SUBJGRPCODE and a.GRPTYPE = 'D' join TB_SCH_DSGSUBJ c
on a.SUID = c.SUID and a.SCHLEVEL = c.SCHLEVEL and a.SCHSESSION = c.SCHSESSION and a.SCHYEAR = c.SCHYEAR and a.SUBJGRPCODE = c.SUBJGRPCODE
join TB_SCH_DSGSUBJTCHR d
on a.SUID = d.SUID and a.SCHLEVEL = d.SCHLEVEL and a.SCHSESSION = d.SCHSESSION and a.SCHYEAR = d.SCHYEAR and a.SUBJGRPCODE = d.SUBJGRPCODE and c.DIVSUBJCODE = d.DIVSUBJCODE
join TB_HSE_COMMON e
on a.SUID = e.SUID and e.TB_ID = 'SBJ' and b.SUBJCODE = e.CODE_ID
join TB_HSE_COMMON f
on a.SUID = f.SUID and f.TB_ID = 'MOI' and c.MOI = f.CODE_ID
left outer join VW_ASR_STAFF g
on a.SUID = g.SUID and d.STAFFCODE = g.STAFFCODE
left outer join TB_STF_MASTER h
on a.SUID=h.SUID and g.STAFFCODE=h.STAFFCODE
group by a.SCHYEAR, a.SCHLEVEL, a.SCHSESSION, a.CLASSLEVEL, a.CLASSCODE, g.NAME_CHI, e.CH_DES, f.CH_DES, h.EMAILADDRESS

union all

select a.SCHYEAR, a.SCHLEVEL, a.SCHSESSION, a.CLASSLEVEL, a.CLASSCODE, f.NAME_CHI, d.CH_DES, e.CH_DES, h.EMAILADDRESS
from TB_SCH_CLSSUBJGRP a
join TB_SCH_ESGSUBJ b
on a.SUID = b.SUID and a.SCHLEVEL = b.SCHLEVEL and a.SCHSESSION = b.SCHSESSION and a.SCHYEAR = b.SCHYEAR and a.CLASSLEVEL = b.CLASSLEVEL and a.SUBJGRPCODE = b.SUBJGRPCODE and a.GRPTYPE = 'E'
join TB_SCH_ESGSUBJTCHR c
on a.SUID = c.SUID and a.SCHLEVEL = c.SCHLEVEL and a.SCHSESSION = c.SCHSESSION and a.SCHYEAR = c.SCHYEAR and a.CLASSLEVEL = c.CLASSLEVEL and a.SUBJGRPCODE = c.SUBJGRPCODE and b.SUBJCODE = c.SUBJCODE
join TB_HSE_COMMON d on a.SUID = d.SUID and d.TB_ID = 'SBJ' and b.SUBJCODE = d.CODE_ID join TB_HSE_COMMON e on a.SUID = e.SUID and e.TB_ID = 'MOI' and b.MOI = e.CODE_ID
left outer join VW_ASR_STAFF f
on a.SUID = f.SUID and c.STAFFCODE = f.STAFFCODE
left outer join TB_STF_MASTER h
on a.SUID=h.SUID and c.STAFFCODE=h.STAFFCODE
group by a.SCHYEAR, a.SCHLEVEL, a.SCHSESSION, a.CLASSLEVEL, a.CLASSCODE, f.NAME_CHI, d.CH_DES, e.CH_DES, h.EMAILADDRESS) a

where SCHYEAR = ?
order by SCHLEVEL, SCHSESSION, CLASSLEVEL, CLASSCODE, TEACHER, SUBJECT, MOI