# SQL 跨年比較學生考試分數之差

1. ### tr-wong_sir Expand Collapse

文章:
64
讚:
0
本校原先已有一個sql用來比較同一年度同一學生的
上學期與下學期的考試分數之差

現希望有一個sql

可計算學生跨年度:
例如2019年度上學期考試(1100) 2018年度下學期考試(1200)

中英數常四科分數總和的平均分之差。

原有的sql 如下:

select
a.SCHYEAR '
學年',
c.TIMESEQ '
上學期測考',
a.classlvl'
級別',
a.CLASSCODE '
班別',
a.CLASSNO '
班號',
b.CHNAME '
姓名',
convert(numeric(10,2), avg(c.SYSSCORE)) '
測驗分',
convert(numeric(10,2), avg(d.SYSSCORE)) '
考試分',

convert(numeric(10,2), avg(d.SYSSCORE)-avg(c.SYSSCORE)) '
考試分-測驗分'

from VW_STU_LATESTSTUSCHREC a

join TB_STU_STUDENT b
on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = ?

left outer join TB_ASR_SUBJASSESSDATA c
on a.SUID = c.SUID and a.STUID = c.STUID and c.SUBJCODE in ('080','165','280','205') and
a.SCHYEAR = c.SCHYEAR and c.TIMESEQ = '1101'

left outer join TB_ASR_SUBJASSESSDATA d
on a.SUID = d.SUID and a.STUID = d.STUID and d.SUBJCODE in ('080','165','280','205') and
a.SCHYEAR = d.SCHYEAR and d.TIMESEQ = '1102'

where a.classlvl in ('p4','p5','p6') AND a.classno <50
group by a.SCHYEAR,c.TIMESEQ ,a.classlvl, a.CLASSCODE, a.CLASSNO, b.CHNAME
order by 9 desc

#1 tr-wong_sir, 2020-03-18
2. ### edb-rn Expand Collapse

文章:
207
讚:
0
老師，請參考以下，

select
a.SCHYEAR '學年',
d.TIMESEQ '上學期考試',
a.classlvl'級別',
a.CLASSCODE '班別',
a.CLASSNO '班號',
b.CHNAME '姓名',
c.SCHYEAR '1200_學年',
d.SCHYEAR '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_考試分'
from VW_STU_LATESTSTUSCHREC a
join TB_STU_STUDENT b
on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = ?
left outer join TB_ASR_SUBJASSESSDATA c
on a.SUID = c.SUID and a.STUID = c.STUID and c.SUBJCODE in ('080','165','280','205') and
(a.SCHYEAR-1 = c.SCHYEAR) and c.TIMESEQ = '1200'
left outer join TB_ASR_SUBJASSESSDATA d
on a.SUID = d.SUID and a.STUID = d.STUID and d.SUBJCODE in ('080','165','280','205') and
a.SCHYEAR = d.SCHYEAR and d.TIMESEQ = '1100'
where a.classlvl in ('p4','p5','p6') AND a.classno <50
group by a.SCHYEAR,d.TIMESEQ ,a.classlvl, a.CLASSCODE, a.CLASSNO, b.CHNAME, c.SCHYEAR, d.SCHYEAR
order by 11 desc

3. ### tr-wong_sir Expand Collapse

文章:
64
讚:
0

謝謝協助

可否在上表

顯示
2018年度及2019年度，中、英、數、常各科的分數

#3 tr-wong_sir, 2020-03-20
4. ### tr-wong_sir Expand Collapse

文章:
64
讚:
0

#4 tr-wong_sir, 2020-03-20
5. ### edb-rn Expand Collapse

文章:
207
讚:
0
老師，請參考以下，

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_考試分'
from VW_STU_LATESTSTUSCHREC a
join TB_STU_STUDENT b
on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = ?
left outer join TB_ASR_SUBJASSESSDATA c
on a.SUID = c.SUID and a.STUID = c.STUID and c.SUBJCODE in ('080','165','280','205') and
(a.SCHYEAR-1 = c.SCHYEAR) and c.TIMESEQ = '1200'
left outer join TB_ASR_SUBJASSESSDATA c1
on a.SUID = c1.SUID and a.STUID = c1.STUID and c1.SUBJCODE ='080' and
(a.SCHYEAR-1 = c1.SCHYEAR) and c1.TIMESEQ = '1200'
left outer join TB_ASR_SUBJASSESSDATA c2
on a.SUID = c2.SUID and a.STUID = c2.STUID and c2.SUBJCODE ='165' and
(a.SCHYEAR-1 = c2.SCHYEAR) and c2.TIMESEQ = '1200'
left outer join TB_ASR_SUBJASSESSDATA c3
on a.SUID = c3.SUID and a.STUID = c3.STUID and c3.SUBJCODE ='280' and
(a.SCHYEAR-1 = c3.SCHYEAR) and c3.TIMESEQ = '1200'
left outer join TB_ASR_SUBJASSESSDATA c4
on a.SUID = c4.SUID and a.STUID = c4.STUID and c4.SUBJCODE ='205' and
(a.SCHYEAR-1 = c4.SCHYEAR) and c4.TIMESEQ = '1200'

left outer join TB_ASR_SUBJASSESSDATA d
on a.SUID = d.SUID and a.STUID = d.STUID and d.SUBJCODE in ('080','165','280','205') and
a.SCHYEAR = d.SCHYEAR and d.TIMESEQ = '1100'
left outer join TB_ASR_SUBJASSESSDATA d1
on a.SUID = d1.SUID and a.STUID = d1.STUID and d1.SUBJCODE ='080' and
a.SCHYEAR = d1.SCHYEAR and d1.TIMESEQ = '1100'
left outer join TB_ASR_SUBJASSESSDATA d2
on a.SUID = d2.SUID and a.STUID = d2.STUID and d2.SUBJCODE ='165' and
a.SCHYEAR = d2.SCHYEAR and d2.TIMESEQ = '1100'
left outer join TB_ASR_SUBJASSESSDATA d3
on a.SUID = d3.SUID and a.STUID = d3.STUID and d3.SUBJCODE ='280' and
a.SCHYEAR = d3.SCHYEAR and d3.TIMESEQ = '1100'
left outer join TB_ASR_SUBJASSESSDATA d4
on a.SUID = d4.SUID and a.STUID = d4.STUID and d4.SUBJCODE ='205' and
a.SCHYEAR = d4.SCHYEAR and d4.TIMESEQ = '1100'

where a.classlvl in ('p4','p5','p6') AND a.classno <50
group by a.SCHYEAR,d.TIMESEQ ,a.classlvl, a.CLASSCODE, a.CLASSNO, b.CHNAME, c.SCHYEAR, d.SCHYEAR,
c1.SYSSCORE,c2.SYSSCORE,c3.SYSSCORE,c4.SYSSCORE,d1.SYSSCORE,d2.SYSSCORE,d3.SYSSCORE,d4.SYSSCORE
order by 19 desc

文章:
64
讚:
0
謝謝協助
已能取得名單