SQL 請教抽取中英數常分數SQL

本文由 little_leung 在 2023-02-18 發表於 "WebSAMS 討論區" 討論區

  1. 55990029

    little_leung
    Expand Collapse

    文章:
    4
    讚:
    0
    請教
    為何下面SQL 語句不正確?

    select
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    a.CHNAME '姓名',
    c.SYSSCORE '中文',
    d.SYSSCORE '英文',
    e.SYSSCORE '數學',
    f.SYSSCORE '常識',
    from wsadmin.vw_stu_lateststudent a
    join wsadmin.tb_asr_time x
    on x.timeseq=?

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA c
    on a.SUID=c.SUID and a.STUID=c.STUID and a.SCHYEAR=c.SCHYEAR and c.TIMESEQ=x.timeseq
    and c.SUBJCODE='080'

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

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA e
    on a.SUID=e.SUID and a.STUID=e.STUID and a.SCHYEAR=e.SCHYEAR and e.TIMESEQ= x.TIMESEQ and e.SUBJCODE='280’

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA f
    on a.SUID=f.SUID and a.STUID=f.STUID and a.SCHYEAR=f.SCHYEAR and f.TIMESEQ= x.TIMESEQ and f.SUBJCODE='205’

    where a.SCHYEAR= ? and a.CLASSLVL=?
    order by a.CLASSCODE, a.CLASSCODE, a.CLASSNO
     
    #1 little_leung, 2023-02-18
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    302
    讚:
    0
    你好,

    請留意最尾一個欄位名稱之後,不用加豆號。
    另外,從MS WORD 檔複製出來的SQL,請留意Quotation mark( ' ) <== 因為WORD 檔的( ’ ),有可能跟SQL的( ' ) 不同,所以COPY了出來之後也要修改一下。

    e.SUBJCODE= ' 280 ’ <=== 根據你以上提供的SQL,你可以看到左手邊的Quotation mark,根右手邊的Quotation mark 有少少分別。


    select
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    a.CHNAME '姓名',
    c.SYSSCORE '中文',
    d.SYSSCORE '英文',
    e.SYSSCORE '數學',
    f.SYSSCORE '常識'
    from wsadmin.vw_stu_lateststudent a
    join wsadmin.tb_asr_time x
    on x.timeseq=?

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA c
    on a.SUID=c.SUID and a.STUID=c.STUID and a.SCHYEAR=c.SCHYEAR and c.TIMESEQ=x.timeseq
    and c.SUBJCODE= '080'

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

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA e
    on a.SUID=e.SUID and a.STUID=e.STUID and a.SCHYEAR=e.SCHYEAR and e.TIMESEQ= x.TIMESEQ and e.SUBJCODE= '280'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA f
    on a.SUID=f.SUID and a.STUID=f.STUID and a.SCHYEAR=f.SCHYEAR and f.TIMESEQ= x.TIMESEQ and f.SUBJCODE= '205'

    where a.SCHYEAR= ? and a.CLASSLVL=?
    order by a.CLASSCODE, a.CLASSCODE, a.CLASSNO
     
    #2 edb-catherinewschan, 2023-02-20
    Last edited: 2023-02-21
  3. 55990029

    little_leung
    Expand Collapse

    文章:
    4
    讚:
    0
    感謝,成功了,如果想一次抽P1-P6,
    CLASSLVL應該怎樣寫?
     
    #3 little_leung, 2023-02-22
  4. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    302
    讚:
    0
    你好,

    如果想抽一至六年級,在WHERE 的句子中刪除 a.CLASSLVL=? 就可以了,因為不用要求用戶輸入級別。

    where a.SCHYEAR= ? and a.CLASSLVL=? <=== 刪除紅色部份
    order by a.CLASSCODE, a.CLASSCODE, a.CLASSNO
     
    #4 edb-catherinewschan, 2023-02-23
  5. 55990029

    little_leung
    Expand Collapse

    文章:
    4
    讚:
    0
    OK , OK , thx a lot
     
    #5 little_leung, 2023-02-23