SQL 標準分

本文由 bhp-kkh 在 2022-06-10 發表於 "WebSAMS 討論區" 討論區

  1. 55956193

    bhp-kkh
    Expand Collapse

    文章:
    14
    讚:
    0
    有沒有同工,可以用SQL/crystal report, 把學生的成績做到standardization..(即計算標準分)...?
     
    #1 bhp-kkh, 2022-06-10
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    255
    讚:
    0
    #2 edb-catherinewschan, 2022-06-13
    Last edited: 2022-06-14
  3. 55956193

    bhp-kkh
    Expand Collapse

    文章:
    14
    讚:
    0
    我學校的情況是這樣的,部份學科有分組,如果可以做到, 有3次考試

    抽到
    班別, 學號, 中文姓名, 中文分組(例如是2BC), 中文1102標準分, 中文1202標準分, 中文1302標準分,英文分組(例如是2BC), 英文1102標準分, 英文1202標準分, ....如此類推

    或退一步,不抽標準分也可以, 但抽分組唔太識
     
    #3 bhp-kkh, 2022-06-14
  4. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    255
    讚:
    0
    你好,

    請參考一下以下的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
     
    #4 edb-catherinewschan, 2022-06-16
    Last edited: 2022-06-16