# SQL 計算某幾科總分的進步生sql

1. ### KC Expand Collapse

文章:
22
讚:
0
你好:
因停課關係我校只想計算上,下學期中英數常主科總分進步2分及操行是B以上的進步生,想請教如何寫SQL?謝謝

2. ### edb-catherinewschan Expand Collapse

文章:
124
讚:
0
你好

可以參考以下SQL

select
a.classcode,
a.classno,
a.chname,
d.ch_des,
b.omclass '1100',
c.omclass '1200',
(b.omclass - c.omclass) 'Diff'
on a.suid=b.suid and a.schlvl=b.schlevel and a.schsess=b.schsession and a.classlvl=b.classlevel and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=1100 and b.subjcode in ('080', '165', '280', '205')
on a.suid=c.suid and a.schlvl=c.schlevel and a.schsess=c.schsession and a.classlvl=c.classlevel and a.schyear=c.schyear and a.stuid=c.stuid and b.subjcode=c.subjcode and c.timeseq=1200
on a.suid=d.suid and b.subjcode=d.code_id and d.tb_id='SBJ'
on a.SUID = e.SUID and a.STUID = e.STUID and a.SCHYEAR = e.SCHYEAR and e.TIMESEQ = 1100 and e.overcondgradeconversioncompcode in ('A','A+', 'A-', 'B+')
on a.SUID = f.SUID and a.STUID = f.STUID and a.SCHYEAR = f.SCHYEAR and f.TIMESEQ = 1200 and f.overcondgradeconversioncompcode in ('A','A+', 'A-', 'B+')
where a.schyear=? and a.classlvl=? and (Diff is not null and Diff>0)
order by a.schlvl, a.schsess, a.classlvl, a.classcode, d.ch_des, 7 desc, a.classno

參數：a.schyear=學年(e.g.2009)、a.classlvl=級別(s1=中一、p1=小一)

#2 edb-catherinewschan, 2020-06-02
3. ### KC Expand Collapse

文章:
22
讚:
0
想請教如只計算中，英文科某幾份分卷（如中文:01, 02), 英文(01. 03, 04)及數常總分，應如何改寫？

4. ### edb-catherinewschan Expand Collapse

文章:
124
讚:
0
你好,

只需要把
1, tb_asr_subjassessdata 改為 tb_asr_subjcomassessdata
2, 再加上 "and b.subjcompcode in ('01','02')" 的分卷代碼條件

select
a.classcode,
a.classno,
a.chname,
d.ch_des,
b.omclass '1100',
c.omclass '1200',
(b.omclass - c.omclass) 'Diff'