# 學生成績 抽取有特定排序的英文科成績

1. ### Daniel Expand Collapse

文章:
66
讚:
0
下面的SQL抽取英文科總分、卷一和卷二分數，請問該如何修改，才能抽取以下３個檔案：

(1) 以英文科總分排序
E.g 總分
1B 10 Chan Tai Man 陳大文 100
1A 22 Chan Siu Man 陳小文 98
...

(2) 以英文科卷一排序
E.g 卷一分
1C 8 ... ... 100
1D 9 ... ... 99

(3)以英文科卷二排序
E.g 卷二分
1A 18 ... ... 99
1D 19 ... ... 97

謝謝幫忙！
Daniel

******************************************************************

select
a.CLASSCODE '班別',
a.CLASSNO '班號',
a.ENNAME '英文姓名',
a.CHNAME '中文姓名',
c.sysscore 'English total marks',
d.sysscore 'Paper 1',
e.sysscore 'Paper 2' from wsadmin.VW_STU_LATESTSTUDENT a

left outer join tb_asr_time b on b.timeseq=?

on a.SUID = c.SUID and a.SCHLVL = c.SCHLEVEL and a.SCHSESS = c.SCHSESSION and a.SCHYEAR = c.SCHYEAR and a.STUID = c.STUID and c.SUBJCODE = '165' and b.TIMESEQ = c.TIMESEQ

left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d on a.SUID = d.SUID and a.SCHLVL = d.SCHLEVEL and a.SCHSESS = d.SCHSESSION and a.SCHYEAR = d.SCHYEAR and a.STUID = d.STUID and d.SUBJCODE = '165' and d.SUBJCOMPCODE = '01' and c.TIMESEQ = d.TIMESEQ

left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e on a.SUID = e.SUID and a.SCHLVL = e.SCHLEVEL and a.SCHSESS = e.SCHSESSION and a.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.SUBJCODE = '165' and e.SUBJCOMPCODE = '02' and c.TIMESEQ = e.TIMESEQ

where a.schyear = ? and a.classlvl=?
order by a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO

2. ### edb-escm Expand Collapse

文章:
434
讚:
0
老師請參考以下SQL:
1.select
a.CLASSCODE '班別',
a.CLASSNO '班號',
a.ENNAME '英文姓名',
a.CHNAME '中文姓名',
c.sysscore 'English total marks'

left outer join tb_asr_time b on b.timeseq=?

on a.SUID = c.SUID and a.SCHLVL = c.SCHLEVEL and a.SCHSESS = c.SCHSESSION and a.SCHYEAR = c.SCHYEAR and a.STUID = c.STUID and c.SUBJCODE = '165' and b.TIMESEQ = c.TIMESEQ

left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d on a.SUID = d.SUID and a.SCHLVL = d.SCHLEVEL and a.SCHSESS = d.SCHSESSION and a.SCHYEAR = d.SCHYEAR and a.STUID = d.STUID and d.SUBJCODE = '165' and d.SUBJCOMPCODE = '01' and c.TIMESEQ = d.TIMESEQ

left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e on a.SUID = e.SUID and a.SCHLVL = e.SCHLEVEL and a.SCHSESS = e.SCHSESSION and a.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.SUBJCODE = '165' and e.SUBJCOMPCODE = '02' and c.TIMESEQ = e.TIMESEQ

where a.schyear = ? and a.classlvl=?
order by c.sysscore DESC

2.select
a.CLASSCODE '班別',
a.CLASSNO '班號',
a.ENNAME '英文姓名',
a.CHNAME '中文姓名',
d.sysscore 'Paper 1'

left outer join tb_asr_time b on b.timeseq=?

on a.SUID = c.SUID and a.SCHLVL = c.SCHLEVEL and a.SCHSESS = c.SCHSESSION and a.SCHYEAR = c.SCHYEAR and a.STUID = c.STUID and c.SUBJCODE = '165' and b.TIMESEQ = c.TIMESEQ

left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d on a.SUID = d.SUID and a.SCHLVL = d.SCHLEVEL and a.SCHSESS = d.SCHSESSION and a.SCHYEAR = d.SCHYEAR and a.STUID = d.STUID and d.SUBJCODE = '165' and d.SUBJCOMPCODE = '01' and c.TIMESEQ = d.TIMESEQ

left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e on a.SUID = e.SUID and a.SCHLVL = e.SCHLEVEL and a.SCHSESS = e.SCHSESSION and a.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.SUBJCODE = '165' and e.SUBJCOMPCODE = '02' and c.TIMESEQ = e.TIMESEQ

where a.schyear = ? and a.classlvl=?
order by d.sysscore DESC

3.select
a.CLASSCODE '班別',
a.CLASSNO '班號',
a.ENNAME '英文姓名',
a.CHNAME '中文姓名',
e.sysscore 'Paper 2'

left outer join tb_asr_time b on b.timeseq=?

on a.SUID = c.SUID and a.SCHLVL = c.SCHLEVEL and a.SCHSESS = c.SCHSESSION and a.SCHYEAR = c.SCHYEAR and a.STUID = c.STUID and c.SUBJCODE = '165' and b.TIMESEQ = c.TIMESEQ

left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d on a.SUID = d.SUID and a.SCHLVL = d.SCHLEVEL and a.SCHSESS = d.SCHSESSION and a.SCHYEAR = d.SCHYEAR and a.STUID = d.STUID and d.SUBJCODE = '165' and d.SUBJCOMPCODE = '01' and c.TIMESEQ = d.TIMESEQ

left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e on a.SUID = e.SUID and a.SCHLVL = e.SCHLEVEL and a.SCHSESS = e.SCHSESSION and a.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.SUBJCODE = '165' and e.SUBJCOMPCODE = '02' and c.TIMESEQ = e.TIMESEQ

where a.schyear = ? and a.classlvl=?
order by e.sysscore DESC

#2 edb-escm, 2015-08-25