SQL SQL 查詢 (抽取 IT, PE, VA, 中英文分卷)

本文由 Mr Gordon 在 2019-12-13 發表於 "WebSAMS 討論區" 討論區

標籤:
  1. 58250848

    Mr Gordon
    Expand Collapse

    文章:
    25
    讚:
    0
    敝校(小學)需要抽取IT, PE, VA, 和中英文科部份分卷, 參考了常用SQL參考庫, 寫了以下SQL, 但很奇怪, 我可以抽出去年, 前年....的成績, 但不能抽出本年度的成績 (已入分, 能抽出姓名班別學號), 可否幫忙查看以下的 SQL? 謝謝.

    select
    b.SCHYEAR '年度',
    d.TIMESEQ '考試',
    b.CLASSCODE '班別',
    b.CLASSNO '班號',
    a.CHNAME '中文姓名',
    d1.sysscore '說話_',
    d2.sysscore '默書_',
    d3.SYSGRADECONVCOMPCODE '書法_',
    e1.sysscore 'Oral_',
    e2.sysscore 'Dict_',
    e4.SYSGRADECONVCOMPCODE 'Pen_',
    f.SYSGRADECONVCOMPCODE '體育_',
    g.SYSGRADECONVCOMPCODE '視藝_',
    h.SYSGRADECONVCOMPCODE 'I.T._'
    from wsadmin.TB_STU_STUDENT a
    join wsadmin.VW_STU_LATESTSTUSCHREC b
    on a.SUID = b.SUID and a.STUID = b.STUID and b.SCHYEAR =? and b.CLASScode = ?
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA d
    on a.SUID = d.SUID and a.SCHLVL = d.SCHLEVEL and a.SCHSESS = d.SCHSESSION and b.SCHYEAR = d.SCHYEAR and a.STUID = d.STUID and d.SUBJCODE = '080' and d.TIMESEQ = ? left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d1
    on a.SUID = d1.SUID and A.SCHLVL = d1.SCHLEVEL and a.SCHSESS = d1.SCHSESSION and b.SCHYEAR = d1.SCHYEAR and a.STUID = d1.STUID and d.SUBJCODE = d1.SUBJCODE and d1.SUBJCOMPCODE = '04' and d.TIMESEQ = d1.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d2
    on a.SUID = d2.SUID and A.SCHLVL = d2.SCHLEVEL and a.SCHSESS = d2.SCHSESSION and b.SCHYEAR = d2.SCHYEAR and a.STUID = d2.STUID and d.SUBJCODE = d2.SUBJCODE and d2.SUBJCOMPCODE = '05' and d.TIMESEQ = d2.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d3
    on a.SUID = d3.SUID and A.SCHLVL = d3.SCHLEVEL and a.SCHSESS = d3.SCHSESSION and b.SCHYEAR = d3.SCHYEAR and a.STUID = d3.STUID and d.SUBJCODE = d3.SUBJCODE and d3.SUBJCOMPCODE = '06' and d.TIMESEQ = d3.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d5
    on a.SUID = d5.SUID and A.SCHLVL = d5.SCHLEVEL and a.SCHSESS = d5.SCHSESSION and b.SCHYEAR = d5.SCHYEAR and a.STUID = d5.STUID and d.SUBJCODE = d5.SUBJCODE and d5.SUBJCOMPCODE = '07' and d.TIMESEQ = d5.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d6
    on a.SUID = d6.SUID and A.SCHLVL = d6.SCHLEVEL and a.SCHSESS = d6.SCHSESSION and b.SCHYEAR = d6.SCHYEAR and a.STUID = d6.STUID and d.SUBJCODE = d6.SUBJCODE and d6.SUBJCOMPCODE = '08' and d.TIMESEQ = d6.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d7
    on a.SUID = d7.SUID and A.SCHLVL = d7.SCHLEVEL and a.SCHSESS = d7.SCHSESSION and b.SCHYEAR = d7.SCHYEAR and a.STUID = d7.STUID and d.SUBJCODE = d7.SUBJCODE and d7.SUBJCOMPCODE = '09' and d.TIMESEQ = d7.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA e
    on a.SUID = e.SUID and a.SCHLVL = e.SCHLEVEL and a.SCHSESS = e.SCHSESSION and b.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.SUBJCODE = '165' and d.TIMESEQ = e.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e1
    on a.SUID = e1.SUID and A.SCHLVL = e1.SCHLEVEL and a.SCHSESS = e1.SCHSESSION and b.SCHYEAR = e1.SCHYEAR and a.STUID = e1.STUID and e.SUBJCODE = e1.SUBJCODE and e1.SUBJCOMPCODE = '04' and d.TIMESEQ = e1.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e2
    on a.SUID = e2.SUID and A.SCHLVL = e2.SCHLEVEL and a.SCHSESS = e2.SCHSESSION and b.SCHYEAR = e2.SCHYEAR and a.STUID = e2.STUID and e.SUBJCODE = e2.SUBJCODE and e2.SUBJCOMPCODE = '05' and d.TIMESEQ = e2.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e4
    on a.SUID = e4.SUID and A.SCHLVL = e4.SCHLEVEL and a.SCHSESS = e4.SCHSESSION and b.SCHYEAR = e4.SCHYEAR and a.STUID = e4.STUID and e.SUBJCODE = e4.SUBJCODE and e4.SUBJCOMPCODE = '06' and d.TIMESEQ = e4.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA f
    on a.SUID = f.SUID and a.SCHLVL = f.SCHLEVEL and a.SCHSESS = f.SCHSESSION and b.SCHYEAR = f.SCHYEAR and a.STUID = f.STUID and f.SUBJCODE = '310' and d.TIMESEQ = f.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA g
    on a.SUID = g.SUID and a.SCHLVL = g.SCHLEVEL and a.SCHSESS = g.SCHSESSION and b.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and g.SUBJCODE = '432' and d.TIMESEQ = g.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA h
    on a.SUID = h.SUID and a.SCHLVL = h.SCHLEVEL and a.SCHSESS = h.SCHSESSION and b.SCHYEAR = h.SCHYEAR and a.STUID = h.STUID and h.SUBJCODE = '906' and d.TIMESEQ = h.TIMESEQ
    order by b.SCHLVL, b.SCHSESS, b.CLASSLVL, b.CLASSCODE, b.CLASSNO
     
    #1 Mr Gordon, 2019-12-13
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    97
    讚:
    0
    你好,

    因為第一個JOIN TABLE的RESULT要有VALUE才可以JOIN到其他的TABLE

    根據這句 d.SUBJCODE = '080' and d.TIMESEQ = ? , '080' 是中文科, 而TIMESEQ是學期考績, 如果你輸入1101, 那麼中文科的考績1101, 有沒有入齊分數??
     
    #2 edb-catherinewschan, 2019-12-16
  3. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    97
    讚:
    0
    如果仍然有疑問,可以聯絡所屬的校主作進一步的跟進。謝謝
     
    #3 edb-catherinewschan, 2019-12-18
  4. 58250848

    Mr Gordon
    Expand Collapse

    文章:
    25
    讚:
    0
    全部分都入齊, 最後發現"整合"後就出到全部結果, 唔係好明未整合前出唔到分卷.
     
    #4 Mr Gordon, 2019-12-18
  5. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    97
    讚:
    0
    你好

    執行"數據整合"後Websams系統會重新計算分數及排名等資料,就算列印成績表之前也要做"數據整合"。
     
    #5 edb-catherinewschan, 2019-12-20