我校將利用學生的中五級期終考試、中六級的平時分及中六級的畢業試成績計算總分,並用作計算 Percentile 以供 JUPAS Academic Performance 用,請問如何寫SQL 以得出以下結果: HKID,Last Name,First Name,Class,CLASSNO,Subject Code,SUBJECT,S5T2A3 System Score,S6T1A1 System Score,S6T1A3 System Score 謝謝。
老師請參考以下的 SQL 語句 或 THIS。 select b.hkid 'HKID', left(b.ENNAME, locate(b.ENNAME, ' ',1)-1) 'Last Name', substr(b.ENNAME, locate(b.ENNAME, ' ',1)+1) 'First Name', b.CLASSCODE 'Class', b.CLASSNO 'Class No', a.subjcode 'Subject Code', d.en_DES 'Subject', a2.SYSSCORE 'S5T2A3 System Score', a.SYSSCORE 'S6T1A1 System Score', a1.SYSSCORE 'S6T1A3 System Score' from wsadmin.VW_STU_LATESTSTUDENT b join tb_asr_subjassessdata a on a.suid = b.suid and a.stuid = b.stuid and a.schyear = b.schyear and a.TIMESEQ = 1101 join wsadmin.TB_HSE_COMMON d on a.SUID = d.SUID and d.TB_ID = 'SBJ' and a.SUBJCODE = d.CODE_ID left outer join tb_asr_subjassessdata a1 on a1.suid = a.suid and a1.stuid = a.stuid and a1.schyear = a.schyear and a1.SUBJCODE = a.SUBJCODE and a1.TIMESEQ = 1103 left outer join tb_asr_subjassessdata a2 on a2.suid = a.suid and a2.stuid = a.stuid and a2.schyear = a.schyear-1 and a2.SUBJCODE = a.SUBJCODE and a2.TIMESEQ = 1203 where b.CLASSLVL = 'S6' and b.SCHYEAR = ? order by b.SCHLVL, b.SCHSESS, b.CLASSLVL, b.CLASSCODE, b.CLASSNO, a.subjcode 註: subjects 是以s6t1a1為主