SQL 錯誤抽出上學年學生資料的班別及學號

本文由 chautc 在 2021-07-06 發表於 "WebSAMS 討論區" 討論區

  1. 10004315

    chautc
    Expand Collapse

    文章:
    66
    讚:
    0
    當輸入下列運算值後,SQL 錯誤抽出上學年(2019)學生資料的班別及學號,但所有其他資料正確。

    d.schyear=2020
    b.classlvl=S5
    d.timeseq=1000


    schyear Regno 班別 學號 姓名 enname 中文 英文 數學 通識 總分 平均分
    2020 2016xxxx 4A 17 xxx xxx 69 66 91 69 296 74



    select
    d.schyear, b.regno 'Regno',
    b.classcode as '班別', b.classno '學號', b.chname '姓名', b.enname,
    a1.sysscore as '中文', a2.sysscore as '英文', a3.sysscore as '數學', a4.sysscore as '通識',
    a1.sysscore+a2.sysscore+a3.sysscore+a4.sysscore as '總分',
    (a1.sysscore+a2.sysscore+a3.sysscore+a4.sysscore)/4 as '平均分'
    from tb_stu_student b
    left outer join tb_asr_studassessdata d
    on b.suid=d.suid and b.stuid=d.stuid
    left outer join tb_asr_subjassessdata a1
    on b.suid=a1.suid and b.stuid=a1.stuid and d.timeseq=a1.timeseq and
    d.schyear=a1.schyear and a1.subjcode='080'
    left outer join tb_asr_subjassessdata a2
    on b.suid=a2.suid and b.stuid=a2.stuid and d.timeseq=a2.timeseq and
    d.schyear=a2.schyear and a2.subjcode='165'
    left outer join tb_asr_subjassessdata a3
    on b.suid=a3.suid and b.stuid=a3.stuid and d.timeseq=a3.timeseq and
    d.schyear=a3.schyear and (a3.subjcode='914' or a3.subjcode='22S' or a3.subjcode='280' )
    left outer join tb_asr_subjassessdata a4
    on b.suid=a4.suid and b.stuid=a4.stuid and d.timeseq=a4.timeseq and
    d.schyear=a4.schyear and (a4.subjcode='265' or a4.subjcode='918')
    where
    d.schyear=? and
    b.classlvl=? and
    d.timeseq=?
    order by 平均分 desc, b.classcode, b.classno
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    211
    讚:
    0
    你好,

    請用 vw_stu_lateststudent
    代替 tb_stu_student,因為 vw_stu_lateststudent 是包含歷年的資料。

    select
    d.schyear, b.regno 'Regno',
    b.classcode as '班別', b.classno '學號', b.chname '姓名', b.enname,
    a1.sysscore as '中文', a2.sysscore as '英文', a3.sysscore as '數學', a4.sysscore as '通識',
    a1.sysscore+a2.sysscore+a3.sysscore+a4.sysscore as '總分',
    (a1.sysscore+a2.sysscore+a3.sysscore+a4.sysscore)/4 as '平均分'
    from wsadmin.vw_stu_lateststudent b
    left outer join wsadmin.tb_asr_studassessdata d
    on b.suid=d.suid and b.stuid=d.stuid and b.schyear=d.schyear
    left outer join wsadmin.tb_asr_subjassessdata a1
    on b.suid=a1.suid and b.stuid=a1.stuid and d.timeseq=a1.timeseq and
    d.schyear=a1.schyear and a1.subjcode='080'
    left outer join wsadmin.tb_asr_subjassessdata a2
    on b.suid=a2.suid and b.stuid=a2.stuid and d.timeseq=a2.timeseq and
    d.schyear=a2.schyear and a2.subjcode='165'
    left outer join wsadmin.tb_asr_subjassessdata a3
    on b.suid=a3.suid and b.stuid=a3.stuid and d.timeseq=a3.timeseq and
    d.schyear=a3.schyear and (a3.subjcode='914' or a3.subjcode='22S' or a3.subjcode='280' )
    left outer join wsadmin.tb_asr_subjassessdata a4
    on b.suid=a4.suid and b.stuid=a4.stuid and d.timeseq=a4.timeseq and
    d.schyear=a4.schyear and (a4.subjcode='265' or a4.subjcode='918')
    where
    d.schyear=? and
    b.classlvl=? and
    d.timeseq=?
    order by 平均分 desc, b.classcode, b.classno
     
    #2 edb-catherinewschan, 2021-07-06
    Last edited: 2021-07-06
  3. 10004315

    chautc
    Expand Collapse

    文章:
    66
    讚:
    0
    成功了,謝謝你!