# 用SQL抽取學生不同學年某一次考試的平均分及操行

1. ### 發奮 Expand Collapse

文章:
72
讚:
0

本人使用上面提供的SQL,如下:

select
c.classlvl as '班級', s.classname as '班別', c.classno as '班號', c.chname as '中文姓名', c.enname as '英文姓名',
a1.sysscore as '中文', a2.sysscore as '英文', a3.sysscore as '數學', a4.sysscore as '常識',
d1.syspercscore as '平均分', d1.omclasslvl as '級名次',

o.ABSENTDAY as '缺席日數',

d1.COMMENTDESCCHI '整體評語'

from vw_stu_lateststudent c

left outer join TB_SCH_SCHCLASS s
on s.SUID=c.SUID and s.SCHYEAR=c1.SCHYEAR and
s.SCHLEVEL=c.SCHLVL and s.SCHSESSION=c.SCHSESS and
s.CLASSLEVEL=c1.CLASSLVL and s.CLASSCODE=c.CLASSCODE

left outer join tb_asr_studassessdata d1
on c.suid=d1.suid and c.stuid=d1.stuid and
c1.schyear=d1.schyear and c.schlvl=d1.schlevel and
c.schsess=d1.schsession and c1.classlvl=d1.classlevel

left outer join tb_asr_subjassessdata a1
on c.suid=a1.suid and c.stuid=a1.stuid and a1.subjcode='080' and
c1.schyear=a1.schyear and c.schlvl=a1.schlevel and
c.schsess=a1.schsession and c1.classlvl=a1.classlevel and
a1.timeseq=d1.timeseq
left outer join tb_asr_subjassessdata a2
on c.suid=a2.suid and c.stuid=a2.stuid and a2.subjcode='165' and
c1.schyear=a2.schyear and c.schlvl=a2.schlevel and
c.schsess=a2.schsession and c1.classlvl=a2.classlevel and
a2.timeseq=d1.timeseq
left outer join tb_asr_subjassessdata a3
on c.suid=a3.suid and c.stuid=a3.stuid and a3.subjcode='280' and
c1.schyear=a3.schyear and c.schlvl=a3.schlevel and
c.schsess=a3.schsession and c1.classlvl=a3.classlevel and
a3.timeseq=d1.timeseq

left outer join tb_asr_subjassessdata a4
on c.suid=a4.suid and c.stuid=a4.stuid and a4.subjcode='205' and
c1.schyear=a4.schyear and c.schlvl=a4.schlevel and
c.schsess=a4.schsession and c1.classlvl=a4.classlevel and
a4.timeseq=d1.timeseq

left outer join tb_asr_studassessdata dc1
on c.suid=dc1.suid and c.stuid=dc1.stuid and
c1.schyear=dc1.schyear and c.schlvl=dc1.schlevel and
c.schsess=dc1.schsession and c1.classlvl=dc1.classlevel and
dc1.timeseq=d1.timeseq

left outer join TB_ASR_STUDMISCDATA o
on o.suid=c.suid and o.stuid=c.stuid and
o.schyear=c1.schyear and o.schlevel=c.schlvl and
o.schsession=c.schsess and o.classlevel=c1classlvl and
o.timeseq=d1.timeseq

where c1.schyear=? and d1.timeseq=? and c1.classlvl=?
order by  c.classcode, c.classno

left outer join TB_SCH_SCHCLASS s
on s.SUID=c.SUID and s.SCHYEAR=c2.SCHYEAR and
s.SCHLEVEL=c.SCHLVL and s.SCHSESSION=c.SCHSESS and
s.CLASSLEVEL=c2.CLASSLVL and s.CLASSCODE=c.CLASSCODE

left outer join tb_asr_studassessdata d1
on c.suid=d1.suid and c.stuid=d1.stuid and
c2.schyear=d1.schyear and c.schlvl=d1.schlevel and
c.schsess=d1.schsession and c2.classlvl=d1.classlevel

left outer join tb_asr_subjassessdata a1
on c.suid=a1.suid and c.stuid=a1.stuid and a1.subjcode='080' and
c2.schyear=a1.schyear and c.schlvl=a1.schlevel and
c.schsess=a1.schsession and c2.classlvl=a1.classlevel and
a1.timeseq=d2.timeseq
left outer join tb_asr_subjassessdata a2
on c.suid=a2.suid and c.stuid=a2.stuid and a2.subjcode='165' and
c2.schyear=a2.schyear and c.schlvl=a2.schlevel and
c.schsess=a2.schsession and c2.classlvl=a2.classlevel and
a2.timeseq=d2.timeseq
left outer join tb_asr_subjassessdata a3
on c.suid=a3.suid and c.stuid=a3.stuid and a3.subjcode='280' and
c2.schyear=a3.schyear and c.schlvl=a3.schlevel and
c.schsess=a3.schsession and c2.classlvl=a3.classlevel and
a3.timeseq=d2.timeseq

left outer join tb_asr_subjassessdata a4
on c.suid=a4.suid and c.stuid=a4.stuid and a4.subjcode='205' and
c2.schyear=a4.schyear and c.schlvl=a4.schlevel and
c.schsess=a4.schsession and c2.classlvl=a4.classlevel and
a4.timeseq=d2.timeseq

left outer join tb_asr_studassessdata dc1
on c.suid=dc1.suid and c.stuid=dc1.stuid and
c2.schyear=dc1.schyear and c.schlvl=dc1.schlevel and
c.schsess=dc1.schsession and c2.classlvl=dc1.classlevel and
dc1.timeseq=d2.timeseq

left outer join TB_ASR_STUDMISCDATA o
on o.suid=c.suid and o.stuid=c.stuid and
o.schyear=c2.schyear and o.schlevel=c.schlvl and
o.schsession=c.schsess and o.classlevel=c2.classlvl and
o.timeseq=d2.timeseq

where c2.schyear=? and d2.timeseq=? and c2.classlvl=?
order by  c.classcode, c.classno

2. ### EDB-EddieKwan Expand Collapse

文章:
0
讚:
0
<FONT color=#006600>tr-howaikin君：<BR><BR>先前所指出的語法問題，閣下似乎尚未修正，是嗎？</FONT><BR><FONT color=#006600><BR>系統及資訊管理組</FONT><FONT color=#000000> </FONT>

#22 EDB-EddieKwan, 2007-05-09
3. ### 發奮 Expand Collapse

文章:
72
讚:
0

已嘗試多個修訂方法,但可能不認識修改有關語句,故還是出現"E-16053 ： SQL語句不正確",煩請賜教.

4. ### EDB-EddieKwan Expand Collapse

文章:
0
讚:
0

tr-howaikin君：

閣下是否仍想寫一條SQL語句來同時顯示兩個不同學年某一考績的主科成績？理論上是可以的，但比較繁複。
比較簡單的做法是，將上述的SQL語句執行兩次，每次輸入不同年份和學期，就能夠提取閣下所需要的資料。

系統及資訊管理組

#24 EDB-EddieKwan, 2007-05-09
5. ### 發奮 Expand Collapse

文章:
72
讚:
0
前者會今老師較方便挑選合適的學生,如果可以的話,希望用一表去顯示,但只需顯示中英數三主科成績,級名次及操行,謝謝!

6. ### 發奮 Expand Collapse

文章:
72
讚:
0
補充:因每學年開始,各級學生均會再次被編排於不同班別上課,故班主任會因應學生於兩個學年平均分及主科成績挑選模範生及進步生,故用一表<FONT color=#000000>顯示兩個不同學年某一考績的成績會方便一些,不用翻查過多的報表及從報表中找學生.</FONT>

7. ### EDB-EddieKwan Expand Collapse

文章:
0
讚:
0

tr-howaikin君：

閣下可以試試以下的 SQL 語句。

select
c.classlvl as '班級',

(select classname from TB_SCH_SCHCLASS
where SUID=c.SUID and SCHYEAR=c.SCHYEAR and
SCHLEVEL=c.SCHLVL and SCHSESSION=c.SCHSESS and
CLASSLEVEL=c.CLASSLVL and CLASSCODE=c.CLASSCODE
) as '班別',

c.classno as '班號',
c.chname as '中文姓名',
c.enname as '英文姓名',

(select sysscore from tb_asr_subjassessdata
where suid=c.suid and stuid=c.stuid and
schyear=c.schyear and schlevel=c.schlvl and
schsession=c.schsess and classlevel=c.classlvl and
timeseq=d1.timeseq and subjcode='080'
) as '中文(1)',

(select sysscore from tb_asr_subjassessdata
where suid=c2.suid and stuid=c2.stuid and
schyear=c2.schyear and schlevel=c2.schlvl and
schsession=c2.schsess and classlevel=c2.classlvl and
timeseq=d2.timeseq and subjcode='080'
) as '中文(2)',

(select sysscore from tb_asr_subjassessdata
where suid=c.suid and stuid=c.stuid and
schyear=c.schyear and schlevel=c.schlvl and
schsession=c.schsess and classlevel=c.classlvl and
timeseq=d1.timeseq and subjcode='165'
) as '英文(1)',

(select sysscore from tb_asr_subjassessdata
where suid=c2.suid and stuid=c2.stuid and
schyear=c2.schyear and schlevel=c2.schlvl and
schsession=c2.schsess and classlevel=c2.classlvl and
timeseq=d2.timeseq and subjcode='165'
) as '英文(2)',

(select sysscore from tb_asr_subjassessdata
where suid=c.suid and stuid=c.stuid and
schyear=c.schyear and schlevel=c.schlvl and
schsession=c.schsess and classlevel=c.classlvl and
timeseq=d1.timeseq and subjcode='280'
) as '數學(1)',

(select sysscore from tb_asr_subjassessdata
where suid=c2.suid and stuid=c2.stuid and
schyear=c2.schyear and schlevel=c2.schlvl and
schsession=c2.schsess and classlevel=c2.classlvl and
timeseq=d2.timeseq and subjcode='280'
) as '數學(2)',

d1.syspercscore as '平均分(1)',
d2.syspercscore as '平均分(2)',
d1.omclasslvl as '級名次(1)',
d2.omclasslvl as '級名次(2)',

where suid=c.suid and stuid=c.stuid and
schyear=c.schyear and schlevel=c.schlvl and
schsession=c.schsess and classlevel=c.classlvl and
timeseq=d1.timeseq
) as '操行(1)',

where suid=c2.suid and stuid=c2.stuid and
schyear=c2.schyear and schlevel=c2.schlvl and
schsession=c2.schsess and classlevel=c2.classlvl and
timeseq=d2.timeseq
) as '操行(2)'

from vw_stu_lateststudent c

left outer join VW_STU_LATESTSTUDENT c2
on c2.SUID=c.SUID and c2.STUID=c.STUID

left outer join tb_asr_studassessdata d1
on c.suid=d1.suid and c.stuid=d1.stuid and
c.schyear=d1.schyear and c.schlvl=d1.schlevel and
c.schsess=d1.schsession and c.classlvl=d1.classlevel

left outer join tb_asr_studassessdata d2
on c2.suid=d2.suid and c2.stuid=d2.stuid and
c2.schyear=d2.schyear and c2.schlvl=d2.schlevel and
c2.schsess=d2.schsession and c2.classlvl=d2.classlevel

where c.SCHYEAR=? and d1.TIMESEQ=? and
c2.SCHYEAR=? and d2.TIMESEQ=?
order by c.SCHLVL, c.SCHSESS, c.CLASSLVL, c.classcode, c.classno

系統及資訊管理組

#27 EDB-EddieKwan, 2007-05-10

文章:
72
讚:
0
謝謝!

9. ### RITA, SIU FUNG YING Expand Collapse

文章:
0
讚:
0

不知為何，我用此sql抽取t2a1成績時，仍然不能列出中英文科分卷成績，是否因為默書等分卷是以等級輸入有關？我已設定t2a1的分卷是考核的，不知哪兒出了問題？請指教！

select
c.classlvl as '班級',
s.classname as '班別',
c.classno as '班號',
c.chname as '中文姓名',
c.enname as '英文姓名',
a1.sysscore as '中文',
a1_02.sysscore as '中文-閱讀',
a1_03.sysscore as '中文-寫作',
a1_04.sysscore as '中文-默書',
a1_06.sysscore as '中文-聆聽',
a1_07.sysscore as '中文-說話',
a2.sysscore as '英文',
a2_02.sysscore as '英文-默書',
a2_05.sysscore as '英文-讀寫',
a2_06.sysscore as '英文-聆聽',
a2_07.sysscore as '英文-說話',
a3.sysscore as '數學',
a4.sysscore as '常識',
d1.syspercscore as '平均分',
d1.omclasslvl as '級名次',
o.ABSENTDAY as '缺席日數',
d1.COMMENTDESCCHI '整體評語'

from vw_stu_lateststudent c

left outer join TB_SCH_SCHCLASS s
on s.SUID=c.SUID and s.SCHYEAR=c.SCHYEAR and
s.SCHLEVEL=c.SCHLVL and s.SCHSESSION=c.SCHSESS and
s.CLASSLEVEL=c.CLASSLVL and s.CLASSCODE=c.CLASSCODE

left outer join tb_asr_studassessdata d1
on c.suid=d1.suid and c.stuid=d1.stuid and
c.schyear=d1.schyear and c.schlvl=d1.schlevel and
c.schsess=d1.schsession and c.classlvl=d1.classlevel

left outer join tb_asr_subjassessdata a1
on c.suid=a1.suid and c.stuid=a1.stuid and a1.subjcode='080' and
c.schyear=a1.schyear and c.schlvl=a1.schlevel and
c.schsess=a1.schsession and c.classlvl=a1.classlevel and
a1.timeseq=d1.timeseq
left outer join tb_asr_subjcomassessdata a1_02
on c.suid=a1_02.suid and c.stuid=a1_02.stuid and a1_02.subjcode='080' and
a1_02.subjcompcode='02' and
c.schyear=a1_02.schyear and c.schlvl=a1_02.schlevel and
c.schsess=a1_02.schsession and c.classlvl=a1_02.classlevel and
a1_02.timeseq=d1.timeseq
left outer join tb_asr_subjcomassessdata a1_03
on c.suid=a1_03.suid and c.stuid=a1_03.stuid and a1_03.subjcode='080' and
a1_03.subjcompcode='03' and
c.schyear=a1_03.schyear and c.schlvl=a1_03.schlevel and
c.schsess=a1_03.schsession and c.classlvl=a1_03.classlevel and
a1_03.timeseq=d1.timeseq
left outer join tb_asr_subjcomassessdata a1_04
on c.suid=a1_04.suid and c.stuid=a1_04.stuid and a1_04.subjcode='080' and
a1_04.subjcompcode='04' and
c.schyear=a1_04.schyear and c.schlvl=a1_04.schlevel and
c.schsess=a1_04.schsession and c.classlvl=a1_04.classlevel and
a1_04.timeseq=d1.timeseq
left outer join tb_asr_subjcomassessdata a1_06
on c.suid=a1_06.suid and c.stuid=a1_06.stuid and a1_06.subjcode='080' and
a1_06.subjcompcode='06' and
c.schyear=a1_06.schyear and c.schlvl=a1_06.schlevel and
c.schsess=a1_06.schsession and c.classlvl=a1_06.classlevel and
a1_06.timeseq=d1.timeseq
left outer join tb_asr_subjcomassessdata a1_07
on c.suid=a1_07.suid and c.stuid=a1_07.stuid and a1_07.subjcode='080' and
a1_07.subjcompcode='07' and
c.schyear=a1_07.schyear and c.schlvl=a1_07.schlevel and
c.schsess=a1_07.schsession and c.classlvl=a1_07.classlevel and
a1_07.timeseq=d1.timeseq
left outer join tb_asr_subjassessdata a2
on c.suid=a2.suid and c.stuid=a2.stuid and a2.subjcode='165' and
c.schyear=a2.schyear and c.schlvl=a2.schlevel and
c.schsess=a2.schsession and c.classlvl=a2.classlevel and
a2.timeseq=d1.timeseq
left outer join tb_asr_subjcomassessdata a2_02
on c.suid=a2_02.suid and c.stuid=a2_02.stuid and a2_02.subjcode='165' and
a2_02.subjcompcode='02' and
c.schyear=a2_02.schyear and c.schlvl=a2_02.schlevel and
c.schsess=a2_02.schsession and c.classlvl=a2_02.classlevel and
a2_02.timeseq=d1.timeseq
left outer join tb_asr_subjcomassessdata a2_05
on c.suid=a2_05.suid and c.stuid=a2_05.stuid and a2_05.subjcode='165' and
a2_05.subjcompcode='05' and
c.schyear=a2_05.schyear and c.schlvl=a2_05.schlevel and
c.schsess=a2_05.schsession and c.classlvl=a2_05.classlevel and
a2_05.timeseq=d1.timeseq
left outer join tb_asr_subjcomassessdata a2_06
on c.suid=a2_06.suid and c.stuid=a2_06.stuid and a2_06.subjcode='165' and
a2_06.subjcompcode='06' and
c.schyear=a2_06.schyear and c.schlvl=a2_06.schlevel and
c.schsess=a2_06.schsession and c.classlvl=a2_06.classlevel and
a2_06.timeseq=d1.timeseq
left outer join tb_asr_subjcomassessdata a2_07
on c.suid=a2_07.suid and c.stuid=a2_07.stuid and a2_07.subjcode='165' and
a2_07.subjcompcode='07' and
c.schyear=a2_07.schyear and c.schlvl=a2_07.schlevel and
c.schsess=a2_07.schsession and c.classlvl=a2_07.classlevel and
a2_07.timeseq=d1.timeseq
left outer join tb_asr_subjassessdata a3
on c.suid=a3.suid and c.stuid=a3.stuid and a3.subjcode='280' and
c.schyear=a3.schyear and c.schlvl=a3.schlevel and
c.schsess=a3.schsession and c.classlvl=a3.classlevel and
a3.timeseq=d1.timeseq

left outer join tb_asr_subjassessdata a4
on c.suid=a4.suid and c.stuid=a4.stuid and a4.subjcode='205' and
c.schyear=a4.schyear and c.schlvl=a4.schlevel and
c.schsess=a4.schsession and c.classlvl=a4.classlevel and
a4.timeseq=d1.timeseq

left outer join tb_asr_subjassessdata a5
on c.suid=a5.suid and c.stuid=a5.stuid and a5.subjcode='300' and
c.schyear=a5.schyear and c.schlvl=a3.schlevel and
c.schsess=a5.schsession and c.classlvl=a5.classlevel and
a5.timeseq=d1.timeseq

left outer join tb_asr_subjassessdata a6
on c.suid=a6.suid and c.stuid=a6.stuid and a6.subjcode='432' and
c.schyear=a6.schyear and c.schlvl=a6.schlevel and
c.schsess=a6.schsession and c.classlvl=a6.classlevel and
a6.timeseq=d1.timeseq

left outer join tb_asr_subjassessdata a7
on c.suid=a3.suid and c.stuid=a7.stuid and a7.subjcode='310' and
c.schyear=a7.schyear and c.schlvl=a7.schlevel and
c.schsess=a7.schsession and c.classlvl=a7.classlevel and
a7.timeseq=d1.timeseq

left outer join tb_asr_subjassessdata a8
on c.suid=a8.suid and c.stuid=a8.stuid and a8.subjcode='350' and
c.schyear=a8.schyear and c.schlvl=a8.schlevel and
c.schsess=a8.schsession and c.classlvl=a8.classlevel and
a8.timeseq=d1.timeseq

left outer join tb_asr_subjassessdata a9
on c.suid=a9.suid and c.stuid=a9.stuid and a9.subjcode='115' and
c.schyear=a9.schyear and c.schlvl=a9.schlevel and
c.schsess=a9.schsession and c.classlvl=a9.classlevel and
a9.timeseq=d1.timeseq

left outer join tb_asr_studassessdata dc1
on c.suid=dc1.suid and c.stuid=dc1.stuid and
c.schyear=dc1.schyear and c.schlvl=dc1.schlevel and
c.schsess=dc1.schsession and c.classlvl=dc1.classlevel and
dc1.timeseq=d1.timeseq

left outer join TB_ASR_STUDMISCDATA o
on o.suid=c.suid and o.stuid=c.stuid and
o.schyear=c.schyear and o.schlevel=c.schlvl and
o.schsession=c.schsess and o.classlevel=c.classlvl and
o.timeseq=d1.timeseq

where c.schyear=? and d1.timeseq=? and c.classlvl=?
order by  c.classcode, c.classno

#29 RITA, SIU FUNG YING, 2007-05-11
10. ### EDB-EddieKwan Expand Collapse

文章:
0
讚:
0

淮山杞子君：

對。此語句只能提取分數，不能提取等級。

系統及資訊管理組

#30 EDB-EddieKwan, 2007-05-11
11. ### RITA, SIU FUNG YING Expand Collapse

文章:
0
讚:
0
謝謝你的回覆。請問有否方法可以修改到齪sql能夠抽取以等級評分的科目分卷？

#31 RITA, SIU FUNG YING, 2007-05-12
12. ### EDB-EddieKwan Expand Collapse

文章:
0
讚:
0
<FONT color=#006600>淮山杞子君：<BR><BR>將有關科目或分卷成績顯示欄位由 <FONT color=#0000ff>sysscore</FONT> 改為 <FONT color=#0000ff>SYSGRADECONVCOMPCODE</FONT> 便可。<BR><BR>系統及資訊管理組</FONT><FONT color=#0000ff><FONT color=#000000> </FONT></FONT>

#32 EDB-EddieKwan, 2007-05-16
13. ### RITA, SIU FUNG YING Expand Collapse

文章:
0
讚:
0
已經成功了，謝謝你的解答。

#33 RITA, SIU FUNG YING, 2007-05-19
14. ### ptm-csc Expand Collapse

文章:
10
讚:
0
上述SQL是否只能抽取數據整合後的成績呢？

#34 ptm-csc, 2007-12-19
15. ### EDB-EddieKwan Expand Collapse

文章:
0
讚:
0
<FONT color=#006600>ptm-csc 君：<BR><BR>是的。<BR><BR>系統及資訊管理組</FONT>

#35 EDB-EddieKwan, 2007-12-19
16. ### ptm-csc Expand Collapse

文章:
10
讚:
0

sorry,

那麼有沒有辦法不整合數據也能抽取成績呢？

謝謝！

#36 ptm-csc, 2007-12-19
17. ### EDB-EddieKwan Expand Collapse

文章:
0
讚:
0
<FONT color=#006600>ptm-csc 君：<BR><BR>抱歉，誤會了你上一題的意思。<BR>其實無關數據合併完成與否，成績都是隨時可以提取的。<BR><BR>系統及資訊管理組</FONT>

#37 EDB-EddieKwan, 2007-12-19
18. ### ptm-csc Expand Collapse

文章:
10
讚:
0
只是…用上述的sql抽取結果只有整合數據的資料……

#38 ptm-csc, 2007-12-19
19. ### EDB-Jeff Tong Expand Collapse

文章:
0
讚:
0
閣下的意思是不是所有分數都沒有顯示，包括科目分卷。

#39 EDB-Jeff Tong, 2007-12-19
20. ### ptm-csc Expand Collapse

文章:
10
讚:
0

是的，

我只有做中一及中三的數據整合，

抽取的結果只有中一及三的資料。

#40 ptm-csc, 2007-12-19