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

1. ### humphrey Expand Collapse

文章:
28
讚:
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. ### edb-catherinewschan Expand Collapse

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

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

select

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

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. ### humphrey Expand Collapse

文章:
28
讚:
0
謝謝，問題解決了

#3 humphrey, 2021-03-05