SQL 平均分顯示問題和排名

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

  1. 58250848

    Mr Gordon
    Expand Collapse

    文章:
    25
    讚:
    0
    你好, 因要分析學生排名和分數, R-ASR077-C 出來的EXCEL格式又要再執, 於是寫了以下SQL, 但發現有兩個問題:
    (1) SQL 抽出的平均分(.syspercscore) 都是整數, 但表格R-ASR077-C顯示的平均分都是到小數後兩位, 結果兩者的排名有落差 (即SQL 的omclasslvl和R-ASR077-C的不一樣), 請問如何令SQL 可顯示完整的平均分分數(小數後兩位)呢?
    (2) 有些缺考學生是設定了不排名次, 但SQL 抽出來的是沒有這考慮, 把學生僅有的分數都計一次並一併排名, 結果是SQL的排名不準確. 可否指導一下如何修改SQL令它考慮不排名次並把這些學生堆到最尾 (最理想), 或退一步顯示不排名次這選項令我可再人手排序呢? 謝謝.


    Select a.schyear'學年', d.timeseq'測考編號', a.classcode '班別', a.classno '學號', a.chname '學生姓名', a.enname'English name', d.omclasslvl '級名次',
    d.omclass '班名次', d.syspercscore '平均分', d.sysscore '中文', e.sysscore '英文', f.sysscore '數學', g.sysscore '常識', c.sysscore '音樂(分)', c.SYSGRADECONVCOMPCODE '音樂', j.sysscore '視藝(分)', j.SYSGRADECONVCOMPCODE '視藝' 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_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 = '165' and e.TIMESEQ = d.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 a.SCHYEAR = f.SCHYEAR and a.STUID = f.STUID and f.SUBJCODE = '280' and f.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 = '205' and g.TIMESEQ = d.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 = '115' and h.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA b on a.SUID = b.SUID and a.SCHLVL = b.SCHLEVEL and a.SCHSESS = b.SCHSESSION and a.SCHYEAR = b.SCHYEAR and a.STUID = b.STUID and b.SUBJCODE = '350' and b.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 = '300' and c.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 = '432' and j.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA n on a.SUID = n.SUID and a.SCHLVL = n.SCHLEVEL and a.SCHSESS = n.SCHSESSION and a.SCHYEAR = n.SCHYEAR and a.STUID = n.STUID and n.SUBJCODE = '310' and n.TIMESEQ = d.TIMESEQ where a.schyear= ? and a.classlvl= ? order by d.omclasslvl, a.classcode, a.classno
     
    #1 Mr Gordon, 2020-01-13
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    144
    讚:
    0
    1,
    用以用 round(d.syspercscore,2) 來做出小數後兩個位

    Select
    z.schyear'學年',
    d.timeseq'測考編號',
    z.classcode '班別',
    z.classno '學號',
    z.chname '學生姓名',
    z.enname'English name',
    d.omclasslvl '級名次',
    d.omclass '班名次',
    round(d.syspercscore,2) '平均分',
    d.sysscore '中文',
    e.sysscore '英文',
    f.sysscore '數學',
    g.sysscore '常識',
    c.sysscore '音樂(分)',
    c.SYSGRADECONVCOMPCODE '音樂',
    j.sysscore '視藝(分)',
    j.SYSGRADECONVCOMPCODE '視藝'
    from wsadmin.vw_stu_lateststudent z
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA d on z.SUID = d.SUID and z.SCHLVL = d.SCHLEVEL and z.SCHSESS = d.SCHSESSION and z.SCHYEAR = d.SCHYEAR and z.STUID = d.STUID and d.SUBJCODE = '080' and d.TIMESEQ = ?
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA e on z.SUID = e.SUID and z.SCHLVL = e.SCHLEVEL and z.SCHSESS = e.SCHSESSION and z.SCHYEAR = e.SCHYEAR and z.STUID = e.STUID and e.SUBJCODE = '165' and e.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA f on z.SUID = f.SUID and z.SCHLVL = f.SCHLEVEL and z.SCHSESS = f.SCHSESSION and z.SCHYEAR = f.SCHYEAR and z.STUID = f.STUID and f.SUBJCODE = '280' and f.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA g on z.SUID = g.SUID and z.SCHLVL = g.SCHLEVEL and z.SCHSESS = g.SCHSESSION and z.SCHYEAR = g.SCHYEAR and z.STUID = g.STUID and g.SUBJCODE = '205' and g.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA h on z.SUID = h.SUID and z.SCHLVL = h.SCHLEVEL and z.SCHSESS = h.SCHSESSION and z.SCHYEAR = h.SCHYEAR and z.STUID = h.STUID and h.SUBJCODE = '115' and h.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA b on z.SUID = b.SUID and z.SCHLVL = b.SCHLEVEL and z.SCHSESS = b.SCHSESSION and z.SCHYEAR = b.SCHYEAR and z.STUID = b.STUID and b.SUBJCODE = '350' and b.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA c on z.SUID = c.SUID and z.SCHLVL = c.SCHLEVEL and z.SCHSESS = c.SCHSESSION and z.SCHYEAR = c.SCHYEAR and z.STUID = c.STUID and c.SUBJCODE = '300' and c.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA j on z.SUID = j.SUID and z.SCHLVL = j.SCHLEVEL and z.SCHSESS = j.SCHSESSION and z.SCHYEAR = j.SCHYEAR and z.STUID = j.STUID and j.SUBJCODE = '432' and j.TIMESEQ = d.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA n on z.SUID = n.SUID and z.SCHLVL = n.SCHLEVEL and z.SCHSESS = n.SCHSESSION and z.SCHYEAR = n.SCHYEAR and z.STUID = n.STUID and n.SUBJCODE = '310' and n.TIMESEQ = d.TIMESEQ
    where z.schyear>= ? and z.classlvl= ? order by d.omclasslvl, z.classcode, z.classno

    2,
    我不太了解你的問題,這條SQL並沒有跟你做排名,而你看見的名次omclasslvl , 是經由WEBSAMS內的數據整合計算出來的結果而已。
    那些缺考學生設定了不排名次,那麼名次omclasslvl的數值是否空白呢?? 如果是,在EXCEL做SORTING就可以。

    如仍然有疑問請連絡校主,謝謝
     
    #2 edb-catherinewschan, 2020-01-14