SQL 增加多一欄顯示不同卷別的滿分分數

本文由 Mr.Stone 在 2021-07-08 發表於 "WebSAMS 討論區" 討論區

  1. 10218215

    Mr.Stone
    Expand Collapse

    文章:
    25
    讚:
    0
    我在之前的帖裡,找到計算及格率的sql,寫法如下。我發覺這寫法是以50分為及格分數。但我們有些科的滿分是100,有些是200或300,這方式出的及格率未能參考。

    1. 因比較急用,簡單起見,若以下code可以多一欄顯示每科卷的滿分分數(例︰科目滿分︰200),已可解決我部份問題,主要想看看有沒有一些班別是沒有人過一半分數的。
    2. 下一步,可否代修改以下編碼,按每卷滿分的50%來計算及格率?

    感謝。

    Select
    a.classcode '班別',
    a.subj '科目',
    a.subjcomp '科目分卷',
    a.highest '最高分',
    a.lowest '最低分',
    a.avge '平均分',
    a.pass '合格率'
    from(
    select
    a.classcode 'classcode',
    e.ch_des 'subj',
    d.ch_des 'subjcomp',
    max(c.sysscore) 'highest',
    min(c.sysscore) 'lowest',
    str(round(avg(c.sysscore), 2), 10, 2) 'avge',
    str(round(sum((case when c.sysscore >= 50.0 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' 'pass'
    from vw_stu_lateststudent a
    join tb_asr_time b
    on b.timeseq=?
    join tb_asr_subjcomassessdata c
    on a.suid=c.suid and a.stuid=c.stuid and a.schlvl=c.schlevel and a.schsess=c.schsession and
    a.schyear=c.schyear and a.classlvl=c.classlevel and c.timeseq=b.timeseq
    left outer join tb_hse_sbjcmp d
    on a.suid=d.suid and c.subjcode=d.sbj_code and c.subjcompcode=d.code_id
    left outer join tb_hse_common e
    on a.suid=e.suid and c.subjcode=e.code_id and e.tb_id='SBJ'
    where a.schyear=? and a.classlvl=?
    group by a.classcode, c.subjcode, e.ch_des, d.ch_des
    union all
    select
    a.classcode 'classcode',
    e.ch_des 'subj',
    null 'subjcomp',
    max(c.sysscore) 'highest',
    min(c.sysscore) 'lowest',
    str(round(avg(c.sysscore), 2), 10, 2) 'avge',
    str(round(sum((case when c.sysscore >= 50.0 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' 'pass'
    from vw_stu_lateststudent a
    join tb_asr_time b
    on b.timeseq=?
    join tb_asr_subjassessdata c
    on a.suid=c.suid and a.stuid=c.stuid and a.schlvl=c.schlevel and a.schsess=c.schsession and
    a.schyear=c.schyear and a.classlvl=c.classlevel and c.timeseq=b.timeseq
    left outer join tb_hse_common e
    on a.suid=e.suid and c.subjcode=e.code_id and e.tb_id='SBJ'
    where a.schyear=? and a.classlvl=?
    group by a.classcode, c.subjcode, e.ch_des) a
    order by 1,2,3
     
    #1 Mr.Stone, 2021-07-08
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    你好,

    我加插了FULLSCORE的資料,而計算方面改了用FULLSCORE的50%
    你可以參考一下:

    Select
    a.classcode '班別',
    a.subj '科目',
    a.subjcomp '科目分卷',
    a.fullscore '科目滿分',
    a.highest '最高分',
    a.lowest '最低分',
    a.avge '平均分',
    a.pass '合格率'
    from(
    select
    a.classcode 'classcode',
    e.ch_des 'subj',
    d.ch_des 'subjcomp',
    d1.fullscore,
    max(c.sysscore) 'highest',
    min(c.sysscore) 'lowest',
    str(round(avg(c.sysscore), 2), 10, 2) 'avge',
    str(round(sum((case when c.sysscore >= d1.fullscore*0.5 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' 'pass'
    from wsadmin.vw_stu_lateststudent a
    join wsadmin.tb_asr_time b
    on b.timeseq= ?
    join wsadmin.tb_asr_subjcomassessdata c
    on a.suid=c.suid and a.stuid=c.stuid and a.schlvl=c.schlevel and a.schsess=c.schsession and
    a.schyear=c.schyear and a.classlvl=c.classlevel and c.timeseq=b.timeseq
    left outer join wsadmin.tb_asr_clslvlsubjsetting d1 on c.suid=d1.suid and c.schlevel=d1.schlevel and c.schsession=d1.schsession
    and c.classlevel=d1.classlevel and c.schyear=d1.schyear and d1.timeseq=c.timeseq and d1.subjcode=c.subjcode and d1.moi=c.moi

    left outer join wsadmin.tb_hse_sbjcmp d
    on a.suid=d.suid and c.subjcode=d.sbj_code and c.subjcompcode=d.code_id
    left outer join wsadmin.tb_hse_common e
    on a.suid=e.suid and c.subjcode=e.code_id and e.tb_id='SBJ'
    where a.schyear=? and a.classlvl=?
    group by a.classcode, c.subjcode, e.ch_des, d.ch_des, d1.fullscore
    union all
    select
    a.classcode 'classcode',
    e.ch_des 'subj',
    null 'subjcomp',
    d1.fullscore,
    max(c.sysscore) 'highest',
    min(c.sysscore) 'lowest',
    str(round(avg(c.sysscore), 2), 10, 2) 'avge',
    str(round(sum((case when c.sysscore >= d1.fullscore*0.5 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' 'pass'
    from wsadmin.vw_stu_lateststudent a
    join wsadmin.tb_asr_time b
    on b.timeseq= ?
    join wsadmin.tb_asr_subjassessdata c
    on a.suid=c.suid and a.stuid=c.stuid and a.schlvl=c.schlevel and a.schsess=c.schsession and
    a.schyear=c.schyear and a.classlvl=c.classlevel and c.timeseq=b.timeseq
    left outer join wsadmin.tb_asr_clslvlsubjsetting d1 on c.suid=d1.suid and c.schlevel=d1.schlevel and c.schsession=d1.schsession
    and c.classlevel=d1.classlevel and c.schyear=d1.schyear and d1.timeseq=c.timeseq and d1.subjcode=c.subjcode and d1.moi=c.moi

    left outer join wsadmin.wsadmin.tb_hse_common e
    on a.suid=e.suid and c.subjcode=e.code_id and e.tb_id='SBJ'
    where a.schyear=? and a.classlvl=?
    group by a.classcode, c.subjcode, e.ch_des, d1.fullscore) a
    order by 1,2,3
     
    #2 edb-catherinewschan, 2021-07-09
    Last edited: 2021-07-09
  3. 10218215

    Mr.Stone
    Expand Collapse

    文章:
    25
    讚:
    0
    顯示SQL錯誤呢(見附圖),無法執行。
     

    附件文件:

    #3 Mr.Stone, 2021-07-12
  4. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    試試以下的SQL,內容跟上面一樣...

    Select
    a.classcode '班別',
    a.subj '科目',
    a.subjcomp '科目分卷',
    a.fullscore '科目滿分',
    a.highest '最高分',
    a.lowest '最低分',
    a.avge '平均分',
    a.pass '合格率'
    from(
    select
    a.classcode 'classcode',
    e.ch_des 'subj',
    d.ch_des 'subjcomp',
    d1.fullscore,
    max(c.sysscore) 'highest',
    min(c.sysscore) 'lowest',
    str(round(avg(c.sysscore), 2), 10, 2) 'avge',
    str(round(sum((case when c.sysscore >= d1.fullscore*0.5 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' 'pass'
    from vw_stu_lateststudent a
    join tb_asr_time b
    on b.timeseq=?
    join tb_asr_subjcomassessdata c
    on a.suid=c.suid and a.stuid=c.stuid and a.schlvl=c.schlevel and a.schsess=c.schsession and
    a.schyear=c.schyear and a.classlvl=c.classlevel and c.timeseq=b.timeseq
    left outer join wsadmin.tb_asr_clslvlsubjsetting d1 on c.suid=d1.suid and c.schlevel=d1.schlevel and c.schsession=d1.schsession
    and c.classlevel=d1.classlevel and c.schyear=d1.schyear and d1.timeseq=c.timeseq and d1.subjcode=c.subjcode and d1.moi=c.moi
    left outer join tb_hse_sbjcmp d
    on a.suid=d.suid and c.subjcode=d.sbj_code and c.subjcompcode=d.code_id
    left outer join tb_hse_common e
    on a.suid=e.suid and c.subjcode=e.code_id and e.tb_id='SBJ'
    where a.schyear=? and a.classlvl=?
    group by a.classcode, c.subjcode, e.ch_des, d.ch_des, d1.fullscore
    union all
    select
    a.classcode 'classcode',
    e.ch_des 'subj',
    null 'subjcomp',
    d1.fullscore,
    max(c.sysscore) 'highest',
    min(c.sysscore) 'lowest',
    str(round(avg(c.sysscore), 2), 10, 2) 'avge',
    str(round(sum((case when c.sysscore >= d1.fullscore*0.5 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' 'pass'
    from vw_stu_lateststudent a
    join tb_asr_time b
    on b.timeseq=?
    join tb_asr_subjassessdata c
    on a.suid=c.suid and a.stuid=c.stuid and a.schlvl=c.schlevel and a.schsess=c.schsession and
    a.schyear=c.schyear and a.classlvl=c.classlevel and c.timeseq=b.timeseq
    left outer join wsadmin.tb_asr_clslvlsubjsetting d1 on c.suid=d1.suid and c.schlevel=d1.schlevel and c.schsession=d1.schsession
    and c.classlevel=d1.classlevel and c.schyear=d1.schyear and d1.timeseq=c.timeseq and d1.subjcode=c.subjcode and d1.moi=c.moi
    left outer join tb_hse_common e
    on a.suid=e.suid and c.subjcode=e.code_id and e.tb_id='SBJ'
    where a.schyear=? and a.classlvl=?
    group by a.classcode, c.subjcode, e.ch_des, d1.fullscore) a
    order by 1,2,3
     
    #4 edb-catherinewschan, 2021-07-12