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

請教
為何下面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

 

你好，

請留意最尾一個欄位名稱之後，不用加豆號。
另外，從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

 

感謝,成功了，如果想一次抽P1-P6,
CLASSLVL應該怎樣寫?

 

你好，

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

where a.SCHYEAR= ? and a.CLASSLVL=? <=== 刪除紅色部份
order by a.CLASSCODE, a.CLASSCODE, a.CLASSNO

 

OK , OK , thx a lot