如何抽以下資料: classcode, classno, chname, T1A1 中國語文(卷一), T1A2 中國語文(卷一), 中文進步分數, 中文進步百分比, T1A1 英國語文(卷一), T1A2 英國語文(卷一), 英文進步分數, 英文進步百分比, T1A1 數學測驗, T1A2 數學考試, 數學進步分數, 數學進步百分比, T1A1 常識測驗, T1A2 常識考試, 常識進步分數, 常識進步百分比,
請老師參考以下的 SQL 句子： select a.classcode '班別', a.classno '學號', a.chname '中文姓名', b1.sysscore 'T1A1 中國語文(卷一)', b2.sysscore 'T1A2 中國語文(卷一)', CAST(b1.sysscore AS DECIMAL)- CAST(b2.sysscore AS DECIMAL) '中文進步分數', CAST(b1.syspercscore AS DECIMAL)- CAST(b2.syspercscore AS DECIMAL) '中文進步百分比', c1.sysscore 'T1A1 英國語文(卷一)', c2.sysscore 'T1A2 英國語文(卷一)', CAST(c1.sysscore AS DECIMAL)- CAST(c2.sysscore AS DECIMAL) '英文進步分數', CAST(c1.syspercscore AS DECIMAL)- CAST(c2.syspercscore AS DECIMAL) '英文進步百分比', d1.sysscore 'T1A1 數學', d2.sysscore 'T1A2 數學', CAST(d1.sysscore AS DECIMAL)- CAST(d2.sysscore AS DECIMAL) '數學進步分數', CAST(d1.syspercscore AS DECIMAL)- CAST(d2.syspercscore AS DECIMAL) '數學進步百分比', e1.sysscore 'T1A1 常識', e2.sysscore 'T1A2 常識', CAST(e1.sysscore AS DECIMAL)- CAST(e2.sysscore AS DECIMAL) '常識進步分數', CAST(e1.syspercscore AS DECIMAL)- CAST(e2.syspercscore AS DECIMAL) '常識進步百分比' from vw_stu_lateststudent a left outer join tb_asr_subjassessdata b1 on a.suid=b1.suid and a.schlvl=b1.schlevel and a.schsess=b1.schsession and a.classlvl=b1.classlevel and a.schyear=b1.schyear and a.stuid=b1.stuid and b1.timeseq=1101 and b1.subjcode in ('080') left outer join tb_asr_subjassessdata b2 on a.suid=b2.suid and a.schlvl=b2.schlevel and a.schsess=b2.schsession and a.classlvl=b2.classlevel and a.schyear=b2.schyear and a.stuid=b2.stuid and b2.timeseq=1102 and b2.subjcode in ('080') left outer join tb_asr_subjassessdata c1 on a.suid=c1.suid and a.schlvl=c1.schlevel and a.schsess=c1.schsession and a.classlvl=c1.classlevel and a.schyear=c1.schyear and a.stuid=c1.stuid and c1.timeseq=1101 and c1.subjcode in ('165') left outer join tb_asr_subjassessdata c2 on a.suid=c2.suid and a.schlvl=c2.schlevel and a.schsess=c2.schsession and a.classlvl=c2.classlevel and a.schyear=c2.schyear and a.stuid=c2.stuid and c2.timeseq=1102 and c2.subjcode in ('165') left outer join tb_asr_subjassessdata d1 on a.suid=d1.suid and a.schlvl=d1.schlevel and a.schsess=d1.schsession and a.classlvl=d1.classlevel and a.schyear=d1.schyear and a.stuid=d1.stuid and d1.timeseq=1101 and d1.subjcode in ('280') left outer join tb_asr_subjassessdata d2 on a.suid=d2.suid and a.schlvl=d2.schlevel and a.schsess=d2.schsession and a.classlvl=d2.classlevel and a.schyear=d2.schyear and a.stuid=d2.stuid and d2.timeseq=1102 and d2.subjcode in ('280') left outer join tb_asr_subjassessdata e1 on a.suid=e1.suid and a.schlvl=e1.schlevel and a.schsess=e1.schsession and a.classlvl=e1.classlevel and a.schyear=e1.schyear and a.stuid=e1.stuid and e1.timeseq=1101 and e1.subjcode in ('205') left outer join tb_asr_subjassessdata e2 on a.suid=e2.suid and a.schlvl=e2.schlevel and a.schsess=e2.schsession and a.classlvl=e2.classlevel and a.schyear=e2.schyear and a.stuid=e2.stuid and e2.timeseq=1102 and e2.subjcode in ('205') where a.schyear=? order by a.schlvl, a.schsess, a.classlvl, a.classcode, a.classno
你這是出中英文主科總分, 而不是出卷一(語文分卷), 所以改了彼是如下: select a.classcode '班別', a.classno '學號', a.chname '中文姓名', b1.sysscore 'T1A1 中國語文(卷一)', b2.sysscore 'T1A2 中國語文(卷一)', CAST(b2.sysscore AS INT)- CAST(b1.sysscore AS INT) '中文進步分數', ((CAST(b2.sysscore AS FLOAT)- CAST(b1.sysscore AS FLOAT))/ CAST(b1.sysscore AS FLOAT)*100) '中文進步百分比', c1.sysscore 'T1A1 英國語文(卷一)', c2.sysscore 'T1A2 英國語文(卷一)', CAST(c2.sysscore AS INT)- CAST(c1.sysscore AS INT) '英文進步分數', ((CAST(c2.sysscore AS FLOAT)- CAST(c1.sysscore AS FLOAT))/ CAST(c1.sysscore AS FLOAT)*100) '英文進步百分比', d1.sysscore 'T1A1 數學', d2.sysscore 'T1A2 數學', CAST(d2.sysscore AS INT)- CAST(d1.sysscore AS INT) '數學進步分數', ((CAST(d2.sysscore AS FLOAT)- CAST(d1.sysscore AS FLOAT))/ CAST(d1.sysscore AS FLOAT)*100) '數學進步百分比', e1.sysscore 'T1A1 常識', e2.sysscore 'T1A2 常識', CAST(e1.sysscore AS INT)- CAST(e2.sysscore AS INT) '常識進步分數', ((CAST(e2.sysscore AS FLOAT)- CAST(e1.sysscore AS FLOAT))/ CAST(e1.sysscore AS FLOAT)*100) '常識進步百分比' from vw_stu_lateststudent a left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA b1 on a.suid=b1.suid and a.schlvl=b1.schlevel and a.schsess=b1.schsession and a.classlvl=b1.classlevel and a.schyear=b1.schyear and a.stuid=b1.stuid and b1.timeseq=1101 and b1.SUBJCOMPCODE = '01' and b1.subjcode in ('080') left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA b2 on a.suid=b2.suid and a.schlvl=b2.schlevel and a.schsess=b2.schsession and a.classlvl=b2.classlevel and a.schyear=b2.schyear and a.stuid=b2.stuid and b2.timeseq=1102 and b2.SUBJCOMPCODE = '01' and b2.subjcode in ('080') left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA c1 on a.suid=c1.suid and a.schlvl=c1.schlevel and a.schsess=c1.schsession and a.classlvl=c1.classlevel and a.schyear=c1.schyear and a.stuid=c1.stuid and c1.timeseq=1101 and c1.SUBJCOMPCODE = '01' and c1.subjcode in ('165') left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA c2 on a.suid=c2.suid and a.schlvl=c2.schlevel and a.schsess=c2.schsession and a.classlvl=c2.classlevel and a.schyear=c2.schyear and a.stuid=c2.stuid and c2.timeseq=1102 and c2.SUBJCOMPCODE = '01' and c2.subjcode in ('165') left outer join tb_asr_subjassessdata d1 on a.suid=d1.suid and a.schlvl=d1.schlevel and a.schsess=d1.schsession and a.classlvl=d1.classlevel and a.schyear=d1.schyear and a.stuid=d1.stuid and d1.timeseq=1101 and d1.subjcode in ('280') left outer join tb_asr_subjassessdata d2 on a.suid=d2.suid and a.schlvl=d2.schlevel and a.schsess=d2.schsession and a.classlvl=d2.classlevel and a.schyear=d2.schyear and a.stuid=d2.stuid and d2.timeseq=1102 and d2.subjcode in ('280') left outer join tb_asr_subjassessdata e1 on a.suid=e1.suid and a.schlvl=e1.schlevel and a.schsess=e1.schsession and a.classlvl=e1.classlevel and a.schyear=e1.schyear and a.stuid=e1.stuid and e1.timeseq=1101 and e1.subjcode in ('205') left outer join tb_asr_subjassessdata e2 on a.suid=e2.suid and a.schlvl=e2.schlevel and a.schsess=e2.schsession and a.classlvl=e2.classlevel and a.schyear=e2.schyear and a.stuid=e2.stuid and e2.timeseq=1102 and e2.subjcode in ('205') where a.schyear=? order by a.schlvl, a.schsess, a.classlvl, a.classcode, a.classno