SQL 如何可以取得各科分卷的全級排名(首五名)

本文由 maggiengai 在 2021-03-22 發表於 "WebSAMS 討論區" 討論區

  1. 55133621

    maggiengai
    Expand Collapse

    文章:
    2
    讚:
    0
    例如:中三科學可拆分為物理, 化學及生物嗎?
    高中物理有英文班及中文班, 可以拆開排名嗎?
     
    #1 maggiengai, 2021-03-22
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    你好,

    可以參考一下CDR ,在 主頁 > 模組資料 > 學生成績 > 常用SQL
    46. 抽取 學生科目總分及分卷分數

    在WHERE 句子中加入 科目級名次 b.omclasslvl , 看看是否附合你所要的。

    select
    a.schyear '學年',
    b.timeseq '考績',
    a.classlvl '級別',
    a.classcode '班別',
    a.classno '班號',
    a.regno,
    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=x.timeseq
    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<=5
    order by a.classlvl, a.classcode, a.classno, c.ch_des
     
    #2 edb-catherinewschan, 2021-03-23