SQL 顯示某選修科全年各項分數及科目名次

本文由 tr-ncm219 在 2022-06-23 , 9:06 上午 發表於 "WebSAMS 討論區" 討論區

  1. 56848873

    tr-ncm219
    Expand Collapse

    文章:
    2
    讚:
    0
    你好,我早前想試顯某選修科全年各項分數(T1A1,T1A2,T1A3,T2A1,T2A2,T2A3和ANNUAL) 和組別名次
    但我用OMCLASSLVL 只能顯示整體級名次,
    不能顯示科目組別名次 (即考績分數/等級清單 (按跨班科目) (R-ASR080-C)顯示的名次)
    求教, 謝謝!!



    select
    b.SCHYEAR 'CURRENT YEAR',
    a.TIMESEQ 'TERM',
    b.REGNO,
    b.CLASSLVL,
    b.CLASSCODE,
    b.CLASSNO,
    b.CHNAME,
    c.SYSSCORE 'Daily1',
    d.SYSSCORE 'UT1',
    e.SYSSCORE 'EXAM1',
    i.SYSSCORE 'Daily2',
    j.SYSSCORE 'UT2',
    k.SYSSCORE 'EXAM2',
    m.SYSSCORE 'Annual'

    from wsadmin.TB_ASR_TIME a

    join wsadmin.VW_STU_LATESTSTUDENT b
    on b.SCHYEAR = ? and a.TIMESEQ = ?

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA c
    on b.SUID = c.SUID and b.STUID = c.STUID and b.SCHYEAR = c.SCHYEAR and
    1101 = c.TIMESEQ and c.SUBJCODE = '075'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA d
    on b.SUID = d.SUID and b.STUID = d.STUID and b.SCHYEAR = d.SCHYEAR and
    1102 = d.TIMESEQ and d.SUBJCODE = '075'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA e
    on b.SUID = e.SUID and b.STUID = e.STUID and b.SCHYEAR = e.SCHYEAR and
    1103 = e.TIMESEQ and e.SUBJCODE = '075'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA i
    on b.SUID = i.SUID and b.STUID = i.STUID and b.SCHYEAR = i.SCHYEAR and
    1201 = i.TIMESEQ and i.SUBJCODE = '075'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA j
    on b.SUID = j.SUID and b.STUID = j.STUID and b.SCHYEAR = j.SCHYEAR and
    1202 = j.TIMESEQ and j.SUBJCODE = '075'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA k
    on b.SUID = k.SUID and b.STUID = k.STUID and b.SCHYEAR = k.SCHYEAR and
    1203 = k.TIMESEQ and k.SUBJCODE = '075'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA m
    on b.SUID = m.SUID and b.STUID = m.STUID and b.SCHYEAR = m.SCHYEAR and
    1000 = m.TIMESEQ and m.SUBJCODE = '075'

    order by b.SCHSESS, b.CLASSCODE, b.CLASSNO
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    240
    讚:
    0
    你好,

    請參考以下的SQL


    select
    b.SCHYEAR 'CURRENT YEAR',
    a.TIMESEQ 'TERM',
    b.REGNO,
    b.CLASSLVL,
    b.CLASSCODE,
    b.CLASSNO,
    b.CHNAME,
    c.SYSSCORE 'Daily1',
    c.OMSUBJDIV 'Daily1_OM',
    d.SYSSCORE 'UT1',
    d.OMSUBJDIV 'UT1_OM',
    e.SYSSCORE 'EXAM1',
    e.OMSUBJDIV 'EXAM1_OM',
    i.SYSSCORE 'Daily2',
    i.OMSUBJDIV 'Daily2_OM',
    j.SYSSCORE 'UT2',
    j.OMSUBJDIV 'UT2_OM',
    k.SYSSCORE 'EXAM2',
    k.OMSUBJDIV 'EXAM2_OM',
    m.SYSSCORE 'Annual',
    m.OMSUBJDIV 'Annual_OM'

    from wsadmin.TB_ASR_TIME a

    join wsadmin.VW_STU_LATESTSTUDENT b
    on b.SCHYEAR = ? and a.TIMESEQ = ?

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA c
    on b.SUID = c.SUID and b.STUID = c.STUID and b.SCHYEAR = c.SCHYEAR and
    c.TIMESEQ=1101 and c.SUBJCODE = '075'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA d
    on b.SUID = d.SUID and b.STUID = d.STUID and b.SCHYEAR = d.SCHYEAR and
    d.TIMESEQ=1102 and d.SUBJCODE = '075'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA e
    on b.SUID = e.SUID and b.STUID = e.STUID and b.SCHYEAR = e.SCHYEAR and
    e.TIMESEQ=1103 and e.SUBJCODE = '075'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA i
    on b.SUID = i.SUID and b.STUID = i.STUID and b.SCHYEAR = i.SCHYEAR and
    i.TIMESEQ=1201 and i.SUBJCODE = '075'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA j
    on b.SUID = j.SUID and b.STUID = j.STUID and b.SCHYEAR = j.SCHYEAR and
    j.TIMESEQ=1202 and j.SUBJCODE = '075'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA k
    on b.SUID = k.SUID and b.STUID = k.STUID and b.SCHYEAR = k.SCHYEAR and
    1203 = k.TIMESEQ and k.SUBJCODE = '075'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA m
    on b.SUID = m.SUID and b.STUID = m.STUID and b.SCHYEAR = m.SCHYEAR and
    1000 = m.TIMESEQ and m.SUBJCODE = '075'

    order by b.SCHSESS, b.CLASSCODE, b.CLASSNO
     
    #2 edb-catherinewschan, 2022-06-24 , 5:50 下午
  3. 56848873

    tr-ncm219
    Expand Collapse

    文章:
    2
    讚:
    0
    謝謝你!!
    成功顯示高中選修科的名次了!
     
    #3 tr-ncm219, 2022-06-27 , 8:47 上午
    Last edited: 2022-06-29 , 8:17 上午