SQL sql,抽取考試成績分數

本文由 tr-wong_sir 在 2021-06-17 發表於 "WebSAMS 討論區" 討論區

  1. 55019624

    tr-wong_sir
    Expand Collapse

    文章:
    68
    讚:
    0
    求一個sql,
    來提取本年度內ATA2、ATA4 及 ATA6 的3個考試內,

    1)小一至小六學生的各科分數(包括中文及英文分卷的分數)

    2)總分數

    3) 級名次及班名次

    謝!
     
    #1 tr-wong_sir, 2021-06-17
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    211
    讚:
    0
    可以參考 CDR 內的常用SQL,學生成績模組,
    46. 抽取 學生科目總分及分卷分數

    SQL用TIMESEQ,而不是用 ATA,所以請自行輸入ATA2、ATA4及ATA6的數值

    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
    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=?
    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=a1.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=?
    order by a.classlvl, a.classcode, a.classno, c.ch_des
     
    #2 edb-catherinewschan, 2021-06-18