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

本文由 Ho Sir 在 2021-06-02 發表於 "WebSAMS 討論區" 討論區

標籤:
  1. 55521840

    Ho Sir
    Expand Collapse

    文章:
    19
    讚:
    0
    以下的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
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    191
    讚:
    0
    你好,

    請試試

    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
     
    #2 edb-catherinewschan, 2021-06-09