SQL 可以提取非術科成績但不能提取術科CL的成績。

本文由 humphrey 在 2021-03-04 發表於 "WebSAMS 討論區" 討論區

  1. 10000322

    humphrey
    Expand Collapse

    文章:
    23
    讚:
    0
    以下SQL可以提取
    非術科CHIS成績但不能提取術科CL的成績。

    是什麼問題?

    select

    a.regno '學生編號',
    a.classcode '班別',
    a.classno '學號',
    a.enname 'ENG NAME',
    a.chname '學生姓名',
    b.sysscore 'CHIS',
    d.sysscore 'CL'


    from 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.SCHYEAR = b.SCHYEAR and a.STUID = b.STUID and b.SUBJCODE = '075' and b.TIMESEQ = ?

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA d on a.SUID = d.SUID and a.SCHLVL = d.SCHLEVEL and a.SCHSESS = d.SCHSESSION and a.SCHYEAR = d.SCHYEAR and a.STUID = d.STUID and d.SUBJCODE = '110' and d.TIMESEQ = b.TIMESEQ

    where a.schyear= ? and a.classlvl= ? order by a.classcode, a.classno
     
    #1 humphrey, 2021-03-04
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    179
    讚:
    0
    我可以抽到資料

    你先查看在相同考績,有沒有輸入分數。若然有,更改SQL作單一科目抽取,看看有沒有分數。

    select

    a.regno '學生編號',
    a.classlvl,
    a.classcode '班別',
    a.classno '學號',
    a.enname 'ENG NAME',
    a.chname '學生姓名',
    b.sysscore 'CHIS',
    d.sysscore 'CL'


    from wsadmin.vw_stu_lateststudent a

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA b on a.SUID = b.SUID and a.SCHYEAR = b.SCHYEAR and a.STUID = b.STUID and b.SUBJCODE = '080' and b.TIMESEQ = ?

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA d on a.SUID = d.SUID and a.SCHYEAR = d.SCHYEAR and a.STUID = d.STUID and d.SUBJCODE = '110' and d.TIMESEQ = b.TIMESEQ

    where a.schyear= ? and a.classlvl= ? order by a.classcode, a.classno
     
    #2 edb-catherinewschan, 2021-03-04
    Last edited: 2021-03-04
  3. 10000322

    humphrey
    Expand Collapse

    文章:
    23
    讚:
    0
    謝謝,問題解決了
     
    #3 humphrey, 2021-03-05