SQL 抽取科目的任教教師和班別清單

本文由 sososo 在 2014-08-18 發表於 "WebSAMS 討論區" 討論區

  1. 56934772

    sososo
    Expand Collapse

    文章:
    16
    讚:
    0
    請問用SQL 可怎樣可按年度抽取各科目的任教教師和班別清單?

    e.g. 2014 各科目的任教教師和班別清單
    中文 教師1 1A
    中文 教師2 1B
    ...
    英文 教師11 1A
    英文 教師12 1B
    ...
    體育 教師14 1A
    體育 教師15 1A
    體育 教師14 1B
    體育 教師15 1B
    ...
     
  2. 57686791

    edb-marmel
    Expand Collapse

    文章:
    187
    讚:
    0
    回覆 1# sososo

    請問貴校有没有一些科目分為中文(粵語)、中文(普通話)或英文授課,謝謝!
     
    #2 edb-marmel, 2014-08-19
  3. 57686791

    edb-marmel
    Expand Collapse

    文章:
    187
    讚:
    0
    回覆 1# sososo

    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
     
    #3 edb-marmel, 2014-08-20