# SQL 比較今學年上學期(S4 T1)及上學年下學期(S3 T2)中英數通的standard score

1. ### JOHNTSE3 Expand Collapse

文章:
2
讚:
0
請問如何比較今學年上學期(S4 T1)及上學年下學期(S3 T2)中英數通的standard score? Thank you!

#1 JOHNTSE3, 2021-04-12
2. ### edb-catherinewschan Expand Collapse

文章:
207
讚:
0
你好，

可以參考以下SQL

select
a.SCHYEAR '學年',
d.TIMESEQ '上學期考試',
a.classlvl'級別',
a.CLASSCODE '班別',
a.CLASSNO '班號',
b.CHNAME '姓名',
c.SCHYEAR '1200_學年',
c1.SYSSCORE '1200_中文',
c2.SYSSCORE '1200_英文',
c3.SYSSCORE '1200_數學',
c4.SYSSCORE '1200_通識',
d.SCHYEAR '1100_學年',
d1.SYSSCORE '1100_中文',
d2.SYSSCORE '1100_英文',
d3.SYSSCORE '1100_數學',
d4.SYSSCORE '1100_通識',
convert(numeric(10,2), avg(c.SYSSCORE)) '1200_考試分',
convert(numeric(10,2), avg(d.SYSSCORE)) '1100_考試分',
convert(numeric(10,2), avg(d.SYSSCORE)-avg(c.SYSSCORE)) '1100_考試分-1200_考試分'
on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = ?
on a.SUID = c.SUID and a.STUID = c.STUID and c.SUBJCODE in ('080','165','280','265') and
(a.SCHYEAR-1 = c.SCHYEAR) and c.TIMESEQ = '1200'
on a.SUID = c1.SUID and a.STUID = c1.STUID and c1.SUBJCODE ='080' and
(a.SCHYEAR-1 = c1.SCHYEAR) and c1.TIMESEQ = '1200'
on a.SUID = c2.SUID and a.STUID = c2.STUID and c2.SUBJCODE ='165' and
(a.SCHYEAR-1 = c2.SCHYEAR) and c2.TIMESEQ = '1200'
on a.SUID = c3.SUID and a.STUID = c3.STUID and c3.SUBJCODE ='280' and
(a.SCHYEAR-1 = c3.SCHYEAR) and c3.TIMESEQ = '1200'
on a.SUID = c4.SUID and a.STUID = c4.STUID and c4.SUBJCODE ='265' and
(a.SCHYEAR-1 = c4.SCHYEAR) and c4.TIMESEQ = '1200'
on a.SUID = d.SUID and a.STUID = d.STUID and d.SUBJCODE in ('080','165','280','265') and
a.SCHYEAR = d.SCHYEAR and d.TIMESEQ = '1100'
on a.SUID = d1.SUID and a.STUID = d1.STUID and d1.SUBJCODE ='080' and
a.SCHYEAR = d1.SCHYEAR and d1.TIMESEQ = '1100'
on a.SUID = d2.SUID and a.STUID = d2.STUID and d2.SUBJCODE ='165' and
a.SCHYEAR = d2.SCHYEAR and d2.TIMESEQ = '1100'