如何用SQL提取每班中文(分卷01)、英文(分卷01)、數學及常識的合格率和平均分呢?

本文由 tr-airhang 在 2010-07-03 發表於 "WebSAMS 討論區" 討論區

  1. 55545669

    tr-airhang
    Expand Collapse

    文章:
    1
    讚:
    0
    因本校P5及P6用等級,每次統計成績也要人手計算,如合格分數是60分,請問如何用SQL提取每班中文(分卷01)、英文(分卷01)、數學及常識的合格率和平均分呢?
     
    #1 tr-airhang, 2010-07-03
  2. 56424333

    edb-stsim2
    Expand Collapse

    文章:
    258
    讚:
    0
    select<BR>&nbsp;&nbsp; a.classcode '班別',<BR>&nbsp;&nbsp; a.classno '學號',<BR>&nbsp;&nbsp; a.chname '姓名',<BR>&nbsp;&nbsp; e.CH_DES '科目名稱',<BR>&nbsp;&nbsp; c.subjcompcode '分卷編號',<BR>&nbsp;&nbsp; b.sysscore '科目分數',<BR>&nbsp;&nbsp; c.sysscore '分卷分數'<BR>from vw_stu_lateststudent a<BR>left outer join tb_asr_subjassessdata b<BR>&nbsp;&nbsp; on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear and a.schlvl=b.schlevel and a.schsess=b.schsession and a.classlvl=b.classlevel<BR>left outer join tb_asr_subjcomassessdata c<BR>&nbsp;&nbsp; on a.suid=c.suid and a.stuid=c.stuid and a.schyear=c.schyear and a.schlvl=c.schlevel and a.schsess=c.schsession and a.classlvl=c.classlevel and b.timeseq=c.timeseq and b.subjcode=c.subjcode<BR>left outer join TB_HSE_COMMON e<BR>&nbsp;&nbsp; on a.SUID=e.SUID and e.TB_ID='SBJ' and e.CODE_ID=b.subjcode<BR>where a.schyear=? and b.timeseq=? and b.subjcode in ('080', '165', '280', '205') and a.classlvl in ('P5','P6')<BR>order by a.classcode, a.classno, b.subjcode, c.subjcompcode
     
    #2 edb-stsim2, 2010-07-05
  3. 55545669

    tr-airhang
    Expand Collapse

    文章:
    1
    讚:
    0
    不好意思,可否提取每班中文(分卷01)、英文(分卷01)、數學及常識的合格率和平均分呢? 謝謝。<br />
    e.g.<br />
    <br />
    5A 最高分 最低分 合格率 平均分<br />
    中文(分卷01)<br />
    英文(分卷01)<br />
    數學<br />
    常識<br />
    --------------------------------------------------------------------<br />
    <br />
    5B 最高分 最低分 合格率 平均分<br />
    中文(分卷01)<br />
    英文(分卷01)<br />
    數學<br />
    常識<br />
    --------------------------------------------------------------------
     
    #3 tr-airhang, 2010-07-06
  4. 56127239

    EDB-Wayne
    Expand Collapse

    文章:
    0
    讚:
    0
    其實可考慮使用系統中的報表"R-ASR077班總積分及等級表(節省紙張)"。這份報表除了列出學生各科目/科目分卷的成績之外,還可列出該科目/科目分卷於該班的合格人數/合格率、班別平均分、最高分、最低分及標準差。
     
    #4 EDB-Wayne, 2010-07-06
  5. 55545669

    tr-airhang
    Expand Collapse

    文章:
    1
    讚:
    0
    謝謝回覆,但本校五、六年級用百分比去分等級,因比websams的報表並不能顯示合格率,如合格分數是60分,想問如何用SQL提取合格率和平均分呢?謝謝。
     
    #5 tr-airhang, 2010-07-06
  6. 56127239

    EDB-Wayne
    Expand Collapse

    文章:
    0
    讚:
    0

    請試試:

    select
       a.classcode '班別',
       e.ch_des '科目',
       d.ch_des '科目分卷',
       max(c.sysscore) '最高分',
       min(c.sysscore) '最低分',
       str(round(avg(c.sysscore), 2), 10, 2) '平均分',
       str(round(sum((case when g.absolutegrademark>=f.absolutegrademark then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' '合格率'
    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 and
          ((c.subjcode='080' and c.subjcompcode='01') or (c.subjcode='165' and c.subjcompcode='01'))
    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'
    left outer join tb_asr_gradeconversioncomp f
       on a.suid=f.suid and a.schyear=f.schyear and c.sysgradeconvseq=f.gradeconversionseq and f.passinggradeind='Y'
    left outer join tb_asr_gradeconversioncomp g
       on a.suid=g.suid and a.schyear=g.schyear and c.sysgradeconvseq=g.gradeconversionseq and c.sysgradeconvcompcode=g.gradeconversioncompcode
    where a.schyear=? and a.classlvl=?
    group by a.classcode, c.subjcode, e.ch_des, d.ch_des

    union all

    select
       a.classcode '班別',
       e.ch_des '科目',
       null '科目分卷',
       max(c.sysscore) '最高分',
       min(c.sysscore) '最低分',
       str(round(avg(c.sysscore), 2), 10, 2) '平均分',
       str(round(sum((case when g.absolutegrademark>=f.absolutegrademark then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' '合格率'
    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 and
          c.subjcode in ('280', '205')
    left outer join tb_hse_common e
       on a.suid=e.suid and c.subjcode=e.code_id and e.tb_id='SBJ'
    left outer join tb_asr_gradeconversioncomp f
       on a.suid=f.suid and a.schyear=f.schyear and c.sysgradeconvseq=f.gradeconversionseq and f.passinggradeind='Y'
    left outer join tb_asr_gradeconversioncomp g
       on a.suid=g.suid and a.schyear=g.schyear and c.sysgradeconvseq=g.gradeconversionseq and c.sysgradeconvcompcode=g.gradeconversioncompcode
    where a.schyear=? and a.classlvl=?
    group by a.classcode, c.subjcode, e.ch_des

    order by 1,2

    參數:b.timeseq=考績(t1a1=1101, t2=1200, annual=1000)、a.schyear=學年(2009)、a.classlvl=級別(p1=小一、s1=中一)

    參數需要輸入兩次。

     
    #6 EDB-Wayne, 2010-07-06
  7. 55545669

    tr-airhang
    Expand Collapse

    文章:
    1
    讚:
    0
    謝謝你,已嘗試,但合格率不正確,想請你再幫忙一下,Thank you.
     
    #7 tr-airhang, 2010-07-06
  8. 56127239

    EDB-Wayne
    Expand Collapse

    文章:
    0
    讚:
    0

    請問若把count(c.stuid)改為count(a.stuid),合格率有否改變?結果是否正確?

     
    #8 EDB-Wayne, 2010-07-07
  9. 55545669

    tr-airhang
    Expand Collapse

    文章:
    1
    讚:
    0
    合格率沒有改變,結果依然不正確,未知是否在SAMS Global Grade Table 2(assessed by grade)內,我們用百分比而不是等級分數,而合格等級也定為E,所以合格率全是100%。<br />
    <br />
    這句:<br />
    str(round(sum((case when g.absolutegrademark>=f.absolutegrademark then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' '合格率'<br />
    <br />
    可否改做: *(抱謙本人不懂SQL,請見諒)*<br />
    str(round(sum((case when g.裝住中文分卷01table的分數>=60 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' '合格率'<br />
    <br />
    謝謝。
     
    #9 tr-airhang, 2010-07-07
  10. 56127239

    EDB-Wayne
    Expand Collapse

    文章:
    0
    讚:
    0

    請試試:

    select
       a.classcode '班別',
       e.ch_des '科目',
       d.ch_des '科目分卷',
       max(c.sysscore) '最高分',
       min(c.sysscore) '最低分',
       str(round(avg(c.sysscore), 2), 10, 2) '平均分',
       str(round(sum((case when c.sysscore >= 60.0 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' '合格率'
    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 and
          ((c.subjcode='080' and c.subjcompcode='01') or (c.subjcode='165' and c.subjcompcode='01'))
    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 '班別',
       e.ch_des '科目',
       null '科目分卷',
       max(c.sysscore) '最高分',
       min(c.sysscore) '最低分',
       str(round(avg(c.sysscore), 2), 10, 2) '平均分',
       str(round(sum((case when c.sysscore >= 60.0 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' '合格率'
    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 and
          c.subjcode in ('280', '205')
    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

    order by 1,2

     
    #10 EDB-Wayne, 2010-07-07
  11. 55545669

    tr-airhang
    Expand Collapse

    文章:
    1
    讚:
    0
    已成功了,很感謝你的幫忙,謝謝。
     
    #11 tr-airhang, 2010-07-08