SQL 修改學生成績SQL

本文由 koock 在 2021-07-06 發表於 "WebSAMS 討論區" 討論區

  1. 169066

    koock
    Expand Collapse

    文章:
    15
    讚:
    0
    你好,本校一直用以下的sql

    SELECT regno '學生編號', a.chname '學生姓名', a.classcode '班別', a.classno '學號', c.SYSGRADECONVCOMPCODE '宗教教育', d.sysscore '中國語文', d1.sysscore '中閱', d2.sysscore '中寫', d6.sysscore '中聆', d9.sysscore '中說', e.sysscore '普通話', g.sysscore '英國語文', g1.sysscore '英閱', g2.sysscore '英寫', g3.sysscore '英聆', g4.sysscore '英說', h.sysscore '數學', i.sysscore '綜合科學', j.sysscore '綜合人文', j1.sysscore '中國歷史', l.sysscore '家政', m.sysscore '普通電腦', u.sysscore '視覺藝術', v.sysscore '音樂', w.sysscore '體育', x.SYSGRADECONVCOMPCODE '閱讀', y0.syspercscore '平均分',y1.omclasslvl as '級名次', y2.omclass as '班名次', y3.overcondgradeconversioncompcode as '操行' from vw_stu_lateststudent a left outer join wsadmin.TB_ASR_SUBJASSESSDATA d on a.SUID = d.SUID and a.SCHLVL = d.SCHLEVEL and a.SCHSESS = d.SCHSESSION and a.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 a.SCHYEAR = d1.SCHYEAR and a.STUID = d1.STUID and d.SUBJCODE = d1.SUBJCODE and d1.SUBJCOMPCODE = '01' 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 a.SCHYEAR = d2.SCHYEAR and a.STUID = d2.STUID and d.SUBJCODE = d2.SUBJCODE and d2.SUBJCOMPCODE = '02' and d.TIMESEQ = d2.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 a.SCHYEAR = d6.SCHYEAR and a.STUID = d6.STUID and d.SUBJCODE = d6.SUBJCODE and d6.SUBJCOMPCODE = '03' and d.TIMESEQ = d6.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d9
    on a.SUID = d9.SUID and a.SCHLVL = d9.SCHLEVEL and a.SCHSESS = d9.SCHSESSION and a.SCHYEAR = d9.SCHYEAR and a.STUID = d9.STUID and d.SUBJCODE = d9.SUBJCODE and d9.SUBJCOMPCODE = '09' and d.TIMESEQ = d9.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 a.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.SUBJCODE = '350' and e.TIMESEQ = d.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 a.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and g.SUBJCODE = '165' and g.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA g1
    on a.SUID = g1.SUID and a.SCHLVL = g1.SCHLEVEL and a.SCHSESS = g1.SCHSESSION and a.SCHYEAR = g1.SCHYEAR and a.STUID = g1.STUID and g.SUBJCODE = g1.SUBJCODE and g1.SUBJCOMPCODE = '01' and g.TIMESEQ = g1.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA g2
    on a.SUID = g2.SUID and a.SCHLVL = g2.SCHLEVEL and a.SCHSESS = g2.SCHSESSION and a.SCHYEAR = g2.SCHYEAR and a.STUID = g2.STUID and g.SUBJCODE = g2.SUBJCODE and g2.SUBJCOMPCODE = '02' and g.TIMESEQ = g2.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA g3
    on a.SUID = g3.SUID and a.SCHLVL = g3.SCHLEVEL and a.SCHSESS = g3.SCHSESSION and a.SCHYEAR = g3.SCHYEAR and a.STUID = g3.STUID and g.SUBJCODE = g3.SUBJCODE and g3.SUBJCOMPCODE = '03' and g.TIMESEQ = g3.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA g4
    on a.SUID = g4.SUID and a.SCHLVL = g4.SCHLEVEL and a.SCHSESS = g4.SCHSESSION and a.SCHYEAR = g4.SCHYEAR and a.STUID = g4.STUID and g.SUBJCODE = g4.SUBJCODE and g4.SUBJCOMPCODE = '04' and g.TIMESEQ = g4.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 a.SCHYEAR = h.SCHYEAR and a.STUID = h.STUID and h.SUBJCODE = '280' and h.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA c on a.SUID = c.SUID and a.SCHLVL = c.SCHLEVEL and a.SCHSESS = c.SCHSESSION and a.SCHYEAR = c.SCHYEAR and a.STUID = c.STUID and c.SUBJCODE = '180' and c.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA i on a.SUID = i.SUID and a.SCHLVL = i.SCHLEVEL and a.SCHSESS = i.SCHSESSION and a.SCHYEAR = i.SCHYEAR and a.STUID = i.STUID and i.SUBJCODE = '260' and i.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA j on a.SUID = j.SUID and a.SCHLVL = j.SCHLEVEL and a.SCHSESS = j.SCHSESSION and a.SCHYEAR = j.SCHYEAR and a.STUID = j.STUID and j.SUBJCODE = '259' and j.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA j1 on a.SUID = j1.SUID and a.SCHLVL = j1.SCHLEVEL and a.SCHSESS = j1.SCHSESSION and a.SCHYEAR = j1.SCHYEAR and a.STUID = j1.STUID and j1.SUBJCODE = '075' and j1.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA l on a.SUID = l.SUID and a.SCHLVL = l.SCHLEVEL and a.SCHSESS = l.SCHSESSION and a.SCHYEAR = l.SCHYEAR and a.STUID = l.STUID and l.SUBJCODE = '240' and l.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA m on a.SUID = m.SUID and a.SCHLVL = m.SCHLEVEL and a.SCHSESS = m.SCHSESSION and a.SCHYEAR = m.SCHYEAR and a.STUID = m.STUID and m.SUBJCODE = '110' and m.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA u on a.SUID = u.SUID and a.SCHLVL = u.SCHLEVEL and a.SCHSESS = u.SCHSESSION and a.SCHYEAR = u.SCHYEAR and a.STUID = u.STUID and u.SUBJCODE = '432' and u.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA v on a.SUID = v.SUID and a.SCHLVL = v.SCHLEVEL and a.SCHSESS = v.SCHSESSION and a.SCHYEAR = v.SCHYEAR and a.STUID = v.STUID and v.SUBJCODE = '300' and v.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA w on a.SUID = w.SUID and a.SCHLVL = w.SCHLEVEL and a.SCHSESS = w.SCHSESSION and a.SCHYEAR = w.SCHYEAR and a.STUID = w.STUID and w.SUBJCODE = '310' and w.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA x on a.SUID = x.SUID and a.SCHLVL = x.SCHLEVEL and a.SCHSESS = x.SCHSESSION and a.SCHYEAR = x.SCHYEAR and a.STUID = x.STUID and x.SUBJCODE = '901' and x.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_STUDASSESSDATA y0 on a.SUID = y0.SUID and a.SCHLVL = y0.SCHLEVEL and a.SCHSESS = y0.SCHSESSION and a.SCHYEAR = y0.SCHYEAR and a.STUID = y0.STUID and y0.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_STUDASSESSDATA y1 on a.SUID = y1.SUID and a.SCHLVL = y1.SCHLEVEL and a.SCHSESS = y1.SCHSESSION and a.SCHYEAR = y1.SCHYEAR and a.STUID = y1.STUID and y1.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_STUDASSESSDATA y2 on a.SUID = y2.SUID and a.SCHLVL = y2.SCHLEVEL and a.SCHSESS = y2.SCHSESSION and a.SCHYEAR = y2.SCHYEAR and a.STUID = y2.STUID and y2.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_STUDASSESSDATA y3 on a.SUID = y3.SUID and a.SCHLVL = y3.SCHLEVEL and a.SCHSESS = y3.SCHSESSION and a.classlvl=y3.classlevel and a.SCHYEAR = y3.SCHYEAR and a.STUID = y3.STUID and y3.TIMESEQ = d.TIMESEQ
    where a.schyear= 2020 and a.classlvl= 'S1'and (a.status is null or a.status not in (4,5,6)) order by a.classcode, a.classno

    但是希望顯示豁免(EXM)及缺席(ANS)
    現在豁免顯示空格,缺席顯示0

    謝謝!
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    #2 edb-catherinewschan, 2021-07-06