學生成績 每級、每班、每科既頭三名的Report/SQL。

本文由 Mr.LEUNG 梁志傑老師 在 2022-06-14 發表於 "WebSAMS 討論區" 討論區

標籤:
  1. 58925373

    Mr.LEUNG 梁志傑老師
    Expand Collapse

    文章:
    3
    讚:
    0
    中學,想列出每級、每班、每科的頭三名,請問有現成的Report或SQL參考句子?
     
    #1 Mr.LEUNG 梁志傑老師, 2022-06-14
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    240
    讚:
    0
    你好,

    請參考以下的SQL, 是篩選 科目級名次 或 科目班名次 的頭3名學生資料:

    select
    a.schyear '學年',
    b.timeseq '考績',
    a.classlvl '級別',
    a.classcode '班別',
    a.classno '班號',
    a.chname '姓名',
    a.enname 'Name',
    a.sex '性別',
    a1.omclasslvl '全年級名次',
    a1.omclass '全年班名次',
    a1.syspercscore '全年總平均分',
    c.ch_des '科目',
    c.en_des 'Subject',
    b.sysscore '成績',
    b.sysgradeconvcompcode '等級',
    d2.ch_des '分卷科目',
    d1.sysscore '分卷成績',
    b.omclasslvl '科目級名次',
    b.omclass '科目班名次'
    from wsadmin.vw_stu_lateststudent a
    join wsadmin.tb_asr_time x
    on x.timeseq = ?
    left outer join wsadmin.tb_asr_studassessdata a1
    on a.suid=a1.suid and a.schlvl=a1.schlevel and a.schsess=a1.schsession and a.schyear=a1.schyear and a.stuid=a1.stuid and a1.timeseq=1000
    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=x.timeseq
    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=? and (b.omclasslvl <=3 or b.omclass <=3)
    order by a.classlvl, a.classcode, a.classno, c.ch_des
     
    #2 edb-catherinewschan, 2022-06-14
  3. 58925373

    Mr.LEUNG 梁志傑老師
    Expand Collapse

    文章:
    3
    讚:
    0
    謝回覆,再問另一個案:

    全級分為兩個科目組別,除了用R-ASR076-C報告外,想問依科目組別找頭三名的SQL參考句子?
     
    #3 Mr.LEUNG 梁志傑老師, 2022-06-15
  4. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    240
    讚:
    0
    你好,

    可以參考以下的SQL:

    已改用了科目組別的名次,再篩選了頭3名學生。

    select * from (
    select
    a.schyear,
    b.timeseq,
    a.classlevel,
    a.classcode,
    c.classno,
    c.chname,
    b.SUBJCODE,
    d.ch_des 'subjname',
    null 'SUBJCOMPCODE',
    null 'SUBJCOMPNAME',
    b.sysscore,
    b.SYSPERCSCORE,
    b.OMSUBJGRP,
    cast(b.OMSUBJGRP as char)+'/'+cast(b.OMSUBJGRPBASE as char) as 'OMBYSUBJGRP',
    a.SUBJGROUP
    from
    wsadmin.tb_asr_subjassessdata b
    left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME a
    on a.suid=b.suid and a.schlevel=b.schlevel and a.schsession=b.schsession and a.classlevel=b.classlevel
    and a.schyear=b.schyear and a.stuid=b.stuid and a.timeseq=b.timeseq and a.subjcode=b.subjcode and a.SUBJCOMP is null
    left outer join wsadmin.vw_stu_lateststudent c
    on c.suid=b.suid and c.schlvl=b.schlevel and c.schsess=b.schsession and c.classlvl=b.classlevel and c.schyear=b.schyear and c.stuid=b.stuid
    left outer join wsadmin.tb_hse_common d
    on b.suid=d.suid and b.subjcode=d.code_id and d.tb_id='SBJ'
    where a.schyear=? and b.classlevel=? and a.timeseq=?
    UNION
    select
    a.schyear,
    b.timeseq,
    a.classlevel,
    a.classcode,
    c.classno,
    c.chname,
    b.SUBJCODE,
    d.ch_des 'subjname',
    b.SUBJCOMPCODE,
    e.ch_des 'SUBJCOMPNAME',
    b.sysscore,
    b.SYSPERCSCORE,
    b.OMSUBJGRP,
    cast(b.OMSUBJGRP as char)+'/'+cast(b.OMSUBJGRPBASE as char) as 'OMBYSUBJGRP',
    a.SUBJGROUP
    from
    wsadmin.TB_ASR_SUBJCOMASSESSDATA b
    left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME a
    on a.suid=b.suid and a.schlevel=b.schlevel and a.schsession=b.schsession and a.classlevel=b.classlevel
    and a.schyear=b.schyear and a.stuid=b.stuid and a.timeseq=b.timeseq and a.subjcode=b.subjcode and a.SUBJCOMP=b.SUBJCOMPCODE
    left outer join wsadmin.vw_stu_lateststudent c
    on c.suid=b.suid and c.schlvl=b.schlevel and c.schsess=b.schsession and c.classlvl=b.classlevel and c.schyear=b.schyear and c.stuid=b.stuid
    left outer join wsadmin.tb_hse_common d
    on b.suid=d.suid and b.subjcode=d.code_id and d.tb_id='SBJ'
    left outer join wsadmin.TB_HSE_SBJCMP e
    on b.SUID = e.SUID and b.SUBJCODE = e.SBJ_CODE and b.SUBJCOMPCODE = e.CODE_ID
    where a.schyear=? and b.classlevel=? and a.timeseq=?
    ) as T
    where T.OMSUBJGRP<=3
    ORDER BY
    T.SUBJGROUP, T.SUBJCODE, T.SUBJCOMPCODE, T.OMSUBJGRP, T.classcode, T.classno, T.subjcode, T.subjcompcode
     
    #4 edb-catherinewschan, 2022-06-16
    Last edited: 2022-06-16
  5. 58925373

    Mr.LEUNG 梁志傑老師
    Expand Collapse

    文章:
    3
    讚:
    0
    謝回覆!

    再問提取總分、績點的SQL句子。

    另外,以下兩個情況的SQL:

    1.以績點代替平均分排名
    2.先計績點,同分再以平均分排名

    謝謝!
     
    #5 Mr.LEUNG 梁志傑老師, 2022-06-17