1. 「WebSAMS 討論區」停止運作 Closure of ‘WebSAMS Forum’

    「教城討論區」包括「WebSAMS 討論區」將於2024年3月1日正式停用。若您需要備份內容,請於上述日期前自行儲存。感謝您的理解及支持!如有任何查詢,歡迎電郵至info@edcity.hk與我們聯絡。

    EdCity Forums, including ‘WebSAMS Forum’, will be inaccessible as of 1 March 2024. Please make sure to save any discussions, threads, or content that you would like to keep before this date.

    Thank you for your understanding and continued support. For any inquiries, please feel free to contact us at info@edcity.hk.

    排除通知

學生成績 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

    文章:
    322
    讚:
    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