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

1. 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. 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 '平均分'
on b.suid=d.suid and b.stuid=d.stuid and b.schyear=d.schyear
on b.suid=a1.suid and b.stuid=a1.stuid and d.timeseq=a1.timeseq and
d.schyear=a1.schyear and a1.subjcode='080'
on b.suid=a2.suid and b.stuid=a2.stuid and d.timeseq=a2.timeseq and
d.schyear=a2.schyear and a2.subjcode='165'
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' )
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

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