學生成績 SQL 統計各班中英數常(包括分卷)各成績等級的人數

本文由 nickname-847140 在 2021-11-23 發表於 "WebSAMS 討論區" 討論區

  1. 59338523

    nickname-847140
    Expand Collapse

    文章:
    2
    讚:
    0
    如果想利用 SQL 統計各班中英數常(包括分卷)各成績等級(ABCDE)的人數,
    (A+ A- 歸 A,B+ B- 歸 B etc),
    請問應如何編寫?
    謝謝.
     
    #1 nickname-847140, 2021-11-23
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    217
    讚:
    0
    你好,

    可以試試以下的SQL

    Grade 我預設有A-F , 而考績(timeseq)就預定為1000 即年終, 如有需要可以自行修改。

    Select
    b.timeseq,
    a.classcode,
    c.ch_des 'subject',
    (case when substring(b.SYSGRADECONVCOMPCODE,1,1)='A' then 'A' when substring(b.SYSGRADECONVCOMPCODE,1,1)='B' then 'B' when substring(b.SYSGRADECONVCOMPCODE,1,1)='C' then 'C' when substring(b.SYSGRADECONVCOMPCODE,1,1)='D' then 'D' when substring(b.SYSGRADECONVCOMPCODE,1,1)='E' then 'E' when substring(b.SYSGRADECONVCOMPCODE,1,1)='F' then 'F' else '--' end) 'grade_sub',
    d2.ch_des 'subject_com',
    (case when substring(d1.SYSGRADECONVCOMPCODE,1,1)='A' then 'A' when substring(d1.SYSGRADECONVCOMPCODE,1,1)='B' then 'B' when substring(d1.SYSGRADECONVCOMPCODE,1,1)='C' then 'C' when substring(d1.SYSGRADECONVCOMPCODE,1,1)='D' then 'D' when substring(d1.SYSGRADECONVCOMPCODE,1,1)='E' then 'E' when substring(d1.SYSGRADECONVCOMPCODE,1,1)='F' then 'F' else '--' end) 'grade_subcom',
    count(case when substring(d1.SYSGRADECONVCOMPCODE,1,1)='A' then 'A' when substring(d1.SYSGRADECONVCOMPCODE,1,1)='B' then 'B' when substring(d1.SYSGRADECONVCOMPCODE,1,1)='C' then 'C' when substring(d1.SYSGRADECONVCOMPCODE,1,1)='D' then 'D' when substring(d1.SYSGRADECONVCOMPCODE,1,1)='E' then 'E' when substring(d1.SYSGRADECONVCOMPCODE,1,1)='F' then 'F' else '--' end) 'grade_subcom_count'
    from wsadmin.vw_stu_lateststudent a
    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.classlvl=b.classlevel and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=1000
    left outer join wsadmin.tb_hse_common c on a.suid=c.suid and b.subjcode=c.code_id and c.tb_id='SBJ'
    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 b.SUBJCODE = d1.SUBJCODE and b.TIMESEQ = d1.TIMESEQ
    left outer join wsadmin.TB_HSE_SBJCMP d2 on d1.SUID = d2.SUID and d1.SUBJCODE = d2.SBJ_CODE and d1.SUBJCOMPCODE = d2.CODE_ID
    left outer join wsadmin.TB_STU_STUSUBJ e on a.suid=e.suid and a.stuid=e.stuid and b.SUBJCODE = e.SUBJCODE and a.STUSCHRECID=e.STUSCHRECID
    where a.schyear=?
    group BY b.timeseq, a.classcode, c.ch_des, grade_sub, d2.ch_des, grade_subcom
    order by a.classcode
     
    #2 edb-catherinewschan, 2021-11-25
  3. 59338523

    nickname-847140
    Expand Collapse

    文章:
    2
    讚:
    0
    謝謝你
     
    #3 nickname-847140, 2021-11-29