SLP Assessment Data for JUPAS Academic Performance

我校將利用學生的中五級期終考試、中六級的平時分及中六級的畢業試成績計算總分，並用作計算 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為主