SQL 未能抽出術科等級的積分

本文由 chautc 在 2021-07-07 發表於 "WebSAMS 討論區" 討論區

  1. 10004315

    chautc
    Expand Collapse

    文章:
    66
    讚:
    0
    下列SQL 未能抽出術科等級的積分,可以加入等級的科目嗎? 謝謝



    select
    b.timeseq '考績', a.classcode '班別', a.classno '班號', a.chname '姓名', a.enname 'Name', c.en_des 'Subject', c.ch_des '科目', b.sysscore '成績', d2.ch_des '分卷科目', d1.sysscore '分卷成績', (case when e.CROSSSUBJGRP is null then (select list(h.name_chi, ', ') from wsadmin.TB_SCH_CLSSUBJTCHR g join wsadmin.VW_ASR_STAFF h on a.SUID = h.SUID and g.STAFFCODE = h.STAFFCODE where a.SUID = g.SUID and a.SCHYEAR = g.SCHYEAR and a.SCHLVL = g.SCHLEVEL and a.schsess = g.SCHSESSION and a.classlvl = g.CLASSLEVEL and a.CLASSCODE = g.CLASSCODE and b.SUBJCODE = g.SUBJCODE and b.MOI = g.MOI) else (select list(j.name_chi, ', ') from wsadmin.TB_SCH_DSGSUBJTCHR i join wsadmin.VW_ASR_STAFF j on a.SUID = j.SUID and i.STAFFCODE = j.STAFFCODE where a.SUID = i.SUID and a.SCHYEAR = i.SCHYEAR and a.SCHLVL = i.SCHLEVEL and a.schsess = i.SCHSESSION and e.CROSSSUBJCODE=i.DIVSUBJCODE and e.CROSSSUBJGRP=i.SUBJGRPCODE) end) '任教老師', (case when e.CROSSSUBJGRP is null then (select list(h.name_eng, ', ') from wsadmin.TB_SCH_CLSSUBJTCHR g join wsadmin.VW_ASR_STAFF h on a.SUID = h.SUID and g.STAFFCODE = h.STAFFCODE where a.SUID = g.SUID and a.SCHYEAR = g.SCHYEAR and a.SCHLVL = g.SCHLEVEL and a.schsess = g.SCHSESSION and a.classlvl = g.CLASSLEVEL and a.CLASSCODE = g.CLASSCODE and b.SUBJCODE = g.SUBJCODE and b.MOI = g.MOI) else (select list(j.name_eng, ', ') from wsadmin.TB_SCH_DSGSUBJTCHR i join wsadmin.VW_ASR_STAFF j on a.SUID = j.SUID and i.STAFFCODE = j.STAFFCODE where a.SUID = i.SUID and a.SCHYEAR = i.SCHYEAR and a.SCHLVL = i.SCHLEVEL and a.schsess = i.SCHSESSION and e.CROSSSUBJCODE=i.DIVSUBJCODE and e.CROSSSUBJGRP=i.SUBJGRPCODE) end) 'Teacher' from vw_stu_lateststudent a left outer join tb_asr_subjassessdata b on a.suid=b.suid and a.schlvl=b.schlevel and a.schsess=b.schsession and a.classlvl=b.classlevel and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=? left outer join tb_hse_common c on a.suid=c.suid and b.subjcode=c.code_id and c.tb_id='SBJ' left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d1 on a.SUID = d1.SUID and a.SCHLVL = d1.SCHLEVEL and a.SCHSESS = d1.SCHSESSION and a.SCHYEAR = d1.SCHYEAR and a.STUID = d1.STUID and b.SUBJCODE = d1.SUBJCODE and b.TIMESEQ = d1.TIMESEQ left outer join wsadmin.TB_HSE_SBJCMP d2 on d1.SUID = d2.SUID and d1.SUBJCODE = d2.SBJ_CODE and d1.SUBJCOMPCODE = d2.CODE_ID left outer join wsadmin.TB_STU_STUSUBJ e on a.suid=e.suid and a.stuid=e.stuid and b.SUBJCODE = e.SUBJCODE and a.STUSCHRECID=e.STUSCHRECID where a.schyear=? order by a.classlvl, a.classcode, a.classno, c.ch_des
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    你好,

    你意思是想出分數的 grade 嗎??
    可以加入SYSGRADECONVCOMPCODE的欄位就可以了。


    select
    b.timeseq '考績',
    a.classcode '班別',
    a.classno '班號',
    a.chname '姓名',
    a.enname 'Name',
    c.en_des 'Subject',
    c.ch_des '科目',
    b.sysscore '成績',
    b.SYSGRADECONVCOMPCODE '等級',
    d2.ch_des '分卷科目',
    d1.sysscore '分卷成績',
    d1.SYSGRADECONVCOMPCODE '分卷等級',
    (case when e.CROSSSUBJGRP is null then
    (select list(h.name_chi, ', ') from wsadmin.TB_SCH_CLSSUBJTCHR g
    join wsadmin.VW_ASR_STAFF h on a.SUID = h.SUID and g.STAFFCODE = h.STAFFCODE
    where a.SUID = g.SUID and a.SCHYEAR = g.SCHYEAR and a.SCHLVL = g.SCHLEVEL and a.schsess = g.SCHSESSION and a.classlvl = g.CLASSLEVEL and a.CLASSCODE = g.CLASSCODE and b.SUBJCODE = g.SUBJCODE and b.MOI = g.MOI)
    else
    (select list(j.name_chi, ', ') from wsadmin.TB_SCH_DSGSUBJTCHR i
    join wsadmin.VW_ASR_STAFF j on a.SUID = j.SUID and i.STAFFCODE = j.STAFFCODE
    where a.SUID = i.SUID and a.SCHYEAR = i.SCHYEAR and a.SCHLVL = i.SCHLEVEL and a.schsess = i.SCHSESSION and e.CROSSSUBJCODE=i.DIVSUBJCODE and e.CROSSSUBJGRP=i.SUBJGRPCODE) end) '任教老師',
    (case when e.CROSSSUBJGRP is null then (select list(h.name_eng, ', ')
    from wsadmin.TB_SCH_CLSSUBJTCHR g
    join wsadmin.VW_ASR_STAFF h on a.SUID = h.SUID and g.STAFFCODE = h.STAFFCODE
    where a.SUID = g.SUID and a.SCHYEAR = g.SCHYEAR and a.SCHLVL = g.SCHLEVEL and a.schsess = g.SCHSESSION and a.classlvl = g.CLASSLEVEL and a.CLASSCODE = g.CLASSCODE and b.SUBJCODE = g.SUBJCODE and b.MOI = g.MOI)
    else
    (select list(j.name_eng, ', ') from wsadmin.TB_SCH_DSGSUBJTCHR i
    join wsadmin.VW_ASR_STAFF j on a.SUID = j.SUID and i.STAFFCODE = j.STAFFCODE
    where a.SUID = i.SUID and a.SCHYEAR = i.SCHYEAR and a.SCHLVL = i.SCHLEVEL and a.schsess = i.SCHSESSION and e.CROSSSUBJCODE=i.DIVSUBJCODE and e.CROSSSUBJGRP=i.SUBJGRPCODE) end) 'Teacher'
    from wsadmin.vw_stu_lateststudent a
    left outer join wsadmin.tb_asr_subjassessdata b on a.suid=b.suid and a.schlvl=b.schlevel and a.schsess=b.schsession and a.classlvl=b.classlevel and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=?
    left outer join wsadmin.tb_hse_common c on a.suid=c.suid and b.subjcode=c.code_id and c.tb_id='SBJ'
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d1 on a.SUID = d1.SUID and a.SCHLVL = d1.SCHLEVEL and a.SCHSESS = d1.SCHSESSION and a.SCHYEAR = d1.SCHYEAR and a.STUID = d1.STUID and b.SUBJCODE = d1.SUBJCODE and b.TIMESEQ = d1.TIMESEQ
    left outer join wsadmin.TB_HSE_SBJCMP d2 on d1.SUID = d2.SUID and d1.SUBJCODE = d2.SBJ_CODE and d1.SUBJCOMPCODE = d2.CODE_ID
    left outer join wsadmin.TB_STU_STUSUBJ e on a.suid=e.suid and a.stuid=e.stuid and b.SUBJCODE = e.SUBJCODE and a.STUSCHRECID=e.STUSCHRECID
    where a.schyear=?
    order by a.classlvl, a.classcode, a.classno, c.ch_des
     
    #2 edb-catherinewschan, 2021-07-07
    Last edited: 2021-07-07
  3. 10004315

    chautc
    Expand Collapse

    文章:
    66
    讚:
    0
    是的,謝謝你。另外,中文及英文科分卷科目的分數輸出排列可以是這樣列出嗎?
    卷一
    卷二
    卷三
    卷四
     
  4. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    你好,

    SQL在排序方面,是在最尾ORDER BY控制。
    試在原本的ORDER BY句子加上。。。
    order by a.classlvl, a.classcode, a.classno, c.ch_des, d2.ch_des
     
    #4 edb-catherinewschan, 2021-07-08
  5. 10004315

    chautc
    Expand Collapse

    文章:
    66
    讚:
    0
    未成功,中文及英文科分卷科目的分數輸出顯示都是這樣列出:
    卷一
    卷三
    卷二
    卷四
     
  6. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    你好,

    試試改用subjcompcode去排序
    order by a.classlvl, a.classcode, a.classno, c.ch_des, subjcompcode
     
    #6 edb-catherinewschan, 2021-07-09
  7. 10004315

    chautc
    Expand Collapse

    文章:
    66
    讚:
    0
    成功了!謝謝!