SQL 求中英文分卷的SQL

本文由 hylamsir 在 2023-02-14 發表於 "WebSAMS 討論區" 討論區

  1. 55073149

    hylamsir
    Expand Collapse

    文章:
    12
    讚:
    0
    我已知
    中、英文卷的SUBJCODE和分卷的代碼
    SUBJCODE 080 165,280,205
    CD中默CL中應ED英默GE英應RW英閱寫

    嘗試了以下sql,但不能成功,可以救下我嗎?
    (SQL 錯誤: Exception:SQL Anywhere Error -143: Column 'SUBJCOMPCODE' not found)

    select
    c.CH_DES '科目',
    b.CLASSlvl '級別',
    a.OMCLASSLVL '級名次',
    b.CLASSCODE '班別',
    b.CLASSNO '班號',
    b.regno,
    b.CHNAME '中文姓名',
    b.ENNAME '英文姓名',
    a.OMCLASS '班名次',
    a.SYSSCORE


    from wsadmin.TB_ASR_SUBJASSESSDATA a
    join wsadmin.VW_STU_LATESTSTUDENT b
    on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = b.SCHYEAR and a.SCHYEAR = ? and a.TIMESEQ =? and a.SUBJCODE = ? and a.SUBJCOMPCODE = ?

    join wsadmin.TB_HSE_COMMON c
    on a.SUID = c.SUID and c.TB_ID = 'SBJ' and a.SUBJCODE = c.CODE_ID
    order by b.CLASSlvl, b.CLASSCODE, b.CLASSNO
     
    #1 hylamsir, 2023-02-14
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    310
    讚:
    0
    你好,

    你顯示ERROR MESSAGE "Column 'SUBJCOMPCODE' not found" 是很合理,因為你的SQL沒有JOIN科目分卷的TABLE,即是 TB_ASR_SUBJCOMASSESSDATA

    你可以參考CDR內ASR模組的常用SQL,即是 主頁 > 模組資料 > 學生成績 > 常用SQL
    https://cdr.websams.edb.gov.hk/模組資料/學生成績/常用SQL/

    尋找題目 20. 抽取 各科成績(包括分卷) 及 相關任教老師


    之後刪除任教老師的部份,再加上顯示名次的欄位和加插抽取分卷資料的欄位.....

    select
    b.timeseq '考績',
    a.classlvl '班級',
    a.classcode '班別',
    a.classno '班號',
    a.chname '姓名',
    a.enname 'Name',
    c.en_des 'Subject',
    c.ch_des '科目',
    b.sysscore '成績',
    b.OMCLASS '科目班名次',
    b.OMCLASSLVL '科目級名次',

    d2.ch_des '分卷科目',
    d1.sysscore '分卷成績',
    d1.OMCLASS '分卷班名次',
    d1.OMCLASSLVL '分卷級名次'


    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=?
    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.subjcode=? and d1.SUBJCOMPCODE=?
    order by a.classlvl, a.classcode, a.classno, c.ch_des
     
    #2 edb-catherinewschan, 2023-02-16
    Last edited: 2023-02-16
  3. 55073149

    hylamsir
    Expand Collapse

    文章:
    12
    讚:
    0
    成功了,十分感謝!
     
    #3 hylamsir, 2023-02-20