我學校的情況是這樣的,部份學科有分組,如果可以做到, 有3次考試 抽到 班別, 學號, 中文姓名, 中文分組(例如是2BC), 中文1102標準分, 中文1202標準分, 中文1302標準分,英文分組(例如是2BC), 英文1102標準分, 英文1202標準分, ....如此類推 或退一步,不抽標準分也可以, 但抽分組唔太識
你好, 請參考一下以下的SQL: 由於沒有寫明是什麼類別的分組,所以把科目組和跨班別科目組 一併附加上去,請查看 分組名稱 和 科目類別 的欄位。 另外,每科都有不同的組名稱,所以不能橫向列出每個學期考績。需要分3次抽取,例如1101, 1102... select a.schyear, b.timeseq, a.classlevel, a.classcode, c.classno, c.chname, b.SUBJCODE, d.ch_des 'subjname', null 'SUBJCOMPCODE', null 'SUBJCOMPNAME', b.sysscore, b.SYSPERCSCORE, (case when a.SUBJGROUP is null then trim(cast(a.CROSSCLSGRP as char)+' '+cast(a.CROSSCLSSUBGRP as char)) else a.SUBJGROUP end) as '分組名稱', (case when a.CLSSUBJTYPE = 'C' then '必修' when a.CLSSUBJTYPE = 'E' then '選修' when a.CLSSUBJTYPE = 'G' then '科目組別' else '跨班科目' end) '科目類別' from wsadmin.tb_asr_subjassessdata b left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME a on a.suid=b.suid and a.schlevel=b.schlevel and a.schsession=b.schsession and a.classlevel=b.classlevel and a.schyear=b.schyear and a.stuid=b.stuid and a.timeseq=b.timeseq and a.subjcode=b.subjcode and a.SUBJCOMP is null left outer join wsadmin.vw_stu_lateststudent c on c.suid=b.suid and c.schlvl=b.schlevel and c.schsess=b.schsession and c.classlvl=b.classlevel and c.schyear=b.schyear and c.stuid=b.stuid left outer join wsadmin.tb_hse_common d on b.suid=d.suid and b.subjcode=d.code_id and d.tb_id='SBJ' where a.schyear=? and b.classlevel=? and a.timeseq=? UNION select a.schyear, b.timeseq, a.classlevel, a.classcode, c.classno, c.chname, b.SUBJCODE, d.ch_des 'subjname', b.SUBJCOMPCODE, e.ch_des 'SUBJCOMPNAME', b.sysscore, b.SYSPERCSCORE, (case when a.SUBJGROUP is null then trim(cast(a.CROSSCLSGRP as char)+' '+cast(a.CROSSCLSSUBGRP as char)) else a.SUBJGROUP end) as '分組名稱', (case when a.CLSSUBJTYPE = 'C' then '必修' when a.CLSSUBJTYPE = 'E' then '選修' when a.CLSSUBJTYPE = 'G' then '科目組別' else '跨班科目' end) '科目類別' from wsadmin.TB_ASR_SUBJCOMASSESSDATA b left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME a on a.suid=b.suid and a.schlevel=b.schlevel and a.schsession=b.schsession and a.classlevel=b.classlevel and a.schyear=b.schyear and a.stuid=b.stuid and a.timeseq=b.timeseq and a.subjcode=b.subjcode and a.SUBJCOMP=b.SUBJCOMPCODE left outer join wsadmin.vw_stu_lateststudent c on c.suid=b.suid and c.schlvl=b.schlevel and c.schsess=b.schsession and c.classlvl=b.classlevel and c.schyear=b.schyear and c.stuid=b.stuid left outer join wsadmin.tb_hse_common d on b.suid=d.suid and b.subjcode=d.code_id and d.tb_id='SBJ' left outer join wsadmin.TB_HSE_SBJCMP e on b.SUID = e.SUID and b.SUBJCODE = e.SBJ_CODE and b.SUBJCOMPCODE = e.CODE_ID where a.schyear=? and b.classlevel=? and a.timeseq=? order by 3,4,5,7,9