SQL 標準分

有沒有同工，可以用SQL/crystal report, 把學生的成績做到standardization..(即計算標準分)...？

 

你好,

以往有計算標準分的提問，給你參考一下：

https://forum.hkedcity.net/index.php?threads/分卷-標準分.85804/

請先參考一下，謝謝。

 

我學校的情況是這樣的，部份學科有分組，如果可以做到, 有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