SQL 怎樣從JOIN得出的資料表顯示STAFFCODE欄位?

以下的SQL從JOIN得出的資料表顯示各班各科及任教老師的資料。如果想最後輸出老師的STAFFCODE，應該怎樣修改? 謝謝!

select SUBJECT '科目', MOI, TEACHER '老師', CLASSCODE '班別' from
(select a.SCHYEAR, a.SCHLEVEL, a.CLASSLEVEL, a.CLASSCODE, d.NAME_CHI 'TEACHER', b.CH_DES 'SUBJECT', c.CH_DES 'MOI', h.PERIODPERCYCLE 'PERIOD'
from wsadmin.TB_SCH_CLSSUBJTCHR a
join wsadmin.TB_HSE_COMMON b
on a.SUID = b.SUID and b.TB_ID = 'SBJ' and a.SUBJCODE = b.CODE_ID
join wsadmin.TB_HSE_COMMON c
on a.SUID = c.SUID and c.TB_ID = 'MOI' and a.MOI = c.CODE_ID
left outer join wsadmin.VW_ASR_STAFF d
on a.SUID = d.SUID and a.STAFFCODE = d.STAFFCODE
left outer join wsadmin.TB_SCH_CLSSUBJ h
on a.SUID = h.SUID and a.SCHLEVEL = h.SCHLEVEL and a.SCHSESSION = h.SCHSESSION and a.SCHYEAR = h.SCHYEAR and a.CLASSLEVEL = h.CLASSLEVEL and a.CLASSCODE = h.CLASSCODE and a.SUBJCODE = h.subjCODE and a.MOI = h.moi
union all
select a.SCHYEAR, a.SCHLEVEL, a.CLASSLEVEL, a.CLASSCODE, g.NAME_CHI, e.CH_DES, f.CH_DES, h.PERIODPERCYCLE
from wsadmin.TB_SCH_CLSSUBJGRP a
join wsadmin.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 wsadmin.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 wsadmin.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 wsadmin.TB_HSE_COMMON e
on a.SUID = e.SUID and e.TB_ID = 'SBJ' and b.SUBJCODE = e.CODE_ID
join wsadmin.TB_HSE_COMMON f
on a.SUID = f.SUID and f.TB_ID = 'MOI' and c.MOI = f.CODE_ID
left outer join wsadmin.VW_ASR_STAFF g
on a.SUID = g.SUID and d.STAFFCODE = g.STAFFCODE
left outer join wsadmin.TB_SCH_DIVSUBJGRP h
on a.SUID = h.SUID and a.SCHLEVEL = h.SCHLEVEL and a.SCHSESSION = h.SCHSESSION and a.SCHYEAR = h.SCHYEAR and a.SUBJGRPCODE = h.SUBJGRPCODE
group by a.SCHYEAR, a.SCHLEVEL, a.CLASSLEVEL, a.CLASSCODE, g.NAME_CHI, e.CH_DES, f.CH_DES, h.PERIODPERCYCLE
union all
select a.SCHYEAR, a.SCHLEVEL, a.CLASSLEVEL, a.CLASSCODE, f.NAME_CHI, d.CH_DES, e.CH_DES, h.PERIODPERCYCLE
from wsadmin.TB_SCH_CLSSUBJGRP a
join wsadmin.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 wsadmin.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 wsadmin.TB_HSE_COMMON d
on a.SUID = d.SUID and d.TB_ID = 'SBJ' and b.SUBJCODE = d.CODE_ID
join wsadmin.TB_HSE_COMMON e
on a.SUID = e.SUID and e.TB_ID = 'MOI' and b.MOI = e.CODE_ID
left outer join wsadmin.VW_ASR_STAFF f
on a.SUID = f.SUID and c.STAFFCODE = f.STAFFCODE
left outer join wsadmin.TB_SCH_DIVSUBJGRP h
on a.SUID = h.SUID and a.SCHLEVEL = h.SCHLEVEL and a.SCHSESSION = h.SCHSESSION and a.SCHYEAR = h.SCHYEAR and a.SUBJGRPCODE = h.SUBJGRPCODE
group by a.SCHYEAR, a.SCHLEVEL, a.CLASSLEVEL, a.CLASSCODE, f.NAME_CHI, d.CH_DES, e.CH_DES, h.PERIODPERCYCLE) a
where SCHYEAR = ? AND PERIOD != 0
order by SUBJECT, MOI, TEACHER, SCHLEVEL, CLASSLEVEL, CLASSCODE, SCHYEAR

 

你好,

請試試

select SUBJECT '科目', MOI, TEACHER '老師', a.STAFFCODE, CLASSCODE '班別' from
(
select a.SCHYEAR, a.SCHLEVEL, a.CLASSLEVEL, a.CLASSCODE, a.STAFFCODE, d.NAME_CHI 'TEACHER', b.CH_DES 'SUBJECT', c.CH_DES 'MOI', h.PERIODPERCYCLE 'PERIOD'
from wsadmin.TB_SCH_CLSSUBJTCHR a
join wsadmin.TB_HSE_COMMON b
on a.SUID = b.SUID and b.TB_ID = 'SBJ' and a.SUBJCODE = b.CODE_ID
join wsadmin.TB_HSE_COMMON c
on a.SUID = c.SUID and c.TB_ID = 'MOI' and a.MOI = c.CODE_ID
left outer join wsadmin.VW_ASR_STAFF d
on a.SUID = d.SUID and a.STAFFCODE = d.STAFFCODE
left outer join wsadmin.TB_SCH_CLSSUBJ h
on a.SUID = h.SUID and a.SCHLEVEL = h.SCHLEVEL and a.SCHSESSION = h.SCHSESSION and a.SCHYEAR = h.SCHYEAR and a.CLASSLEVEL = h.CLASSLEVEL and a.CLASSCODE = h.CLASSCODE and a.SUBJCODE = h.subjCODE and a.MOI = h.moi
union all
select a.SCHYEAR, a.SCHLEVEL, a.CLASSLEVEL, a.CLASSCODE, d.STAFFCODE, g.NAME_CHI, e.CH_DES, f.CH_DES, h.PERIODPERCYCLE
from wsadmin.TB_SCH_CLSSUBJGRP a
join wsadmin.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 wsadmin.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 wsadmin.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 wsadmin.TB_HSE_COMMON e
on a.SUID = e.SUID and e.TB_ID = 'SBJ' and b.SUBJCODE = e.CODE_ID
join wsadmin.TB_HSE_COMMON f
on a.SUID = f.SUID and f.TB_ID = 'MOI' and c.MOI = f.CODE_ID
left outer join wsadmin.VW_ASR_STAFF g
on a.SUID = g.SUID and d.STAFFCODE = g.STAFFCODE
left outer join wsadmin.TB_SCH_DIVSUBJGRP h
on a.SUID = h.SUID and a.SCHLEVEL = h.SCHLEVEL and a.SCHSESSION = h.SCHSESSION and a.SCHYEAR = h.SCHYEAR and a.SUBJGRPCODE = h.SUBJGRPCODE
group by a.SCHYEAR, a.SCHLEVEL, a.CLASSLEVEL, a.CLASSCODE, d.STAFFCODE, g.NAME_CHI, e.CH_DES, f.CH_DES, h.PERIODPERCYCLE
union all
select a.SCHYEAR, a.SCHLEVEL, a.CLASSLEVEL, a.CLASSCODE, c.STAFFCODE, f.NAME_CHI, d.CH_DES, e.CH_DES, h.PERIODPERCYCLE
from wsadmin.TB_SCH_CLSSUBJGRP a
join wsadmin.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 wsadmin.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 wsadmin.TB_HSE_COMMON d
on a.SUID = d.SUID and d.TB_ID = 'SBJ' and b.SUBJCODE = d.CODE_ID
join wsadmin.TB_HSE_COMMON e
on a.SUID = e.SUID and e.TB_ID = 'MOI' and b.MOI = e.CODE_ID
left outer join wsadmin.VW_ASR_STAFF f
on a.SUID = f.SUID and c.STAFFCODE = f.STAFFCODE
left outer join wsadmin.TB_SCH_DIVSUBJGRP h
on a.SUID = h.SUID and a.SCHLEVEL = h.SCHLEVEL and a.SCHSESSION = h.SCHSESSION and a.SCHYEAR = h.SCHYEAR and a.SUBJGRPCODE = h.SUBJGRPCODE
group by a.SCHYEAR, a.SCHLEVEL, a.CLASSLEVEL, a.CLASSCODE, c.STAFFCODE, f.NAME_CHI, d.CH_DES, e.CH_DES, h.PERIODPERCYCLE) a
where SCHYEAR = ? AND PERIOD != 0
order by SUBJECT, MOI, TEACHER, SCHLEVEL, CLASSLEVEL, CLASSCODE, SCHYEAR