SQL SQL中加入跨班分組組別名稱

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

  1. 55956193

    bhp-kkh
    Expand Collapse

    文章:
    14
    讚:
    0
    想在以下的SQL中,再加入跨班分組(例如英文2A,2B兩班,其實分了2A1,2B1,2AB3組)的組別名稱,有無辦法?

    select
    b.timeseq '考績',
    a.classlvl,
    a.classcode,
    a.classno,
    a.chname,
    c.ch_des,
    b.sysscore '成績',
    b.omclasslvl '排名'
    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'
    where a.schyear=? and b.sysscore>-1
    order by a.classlvl, c.ch_des, b.omclasslvl
     
    #1 bhp-kkh, 2022-08-04
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    254
    讚:
    0
    加插了分組名稱 , 試試以下的SQL

    select
    b.timeseq '考績',
    a.classlvl,
    a.classcode,
    a.classno,
    a.chname,
    c.ch_des,
    (case when d.SUBJGROUP is null then trim(cast(d.CROSSCLSGRP as char)+' '+cast(d.CROSSCLSSUBGRP as char)) else d.SUBJGROUP end) as '分組名稱',
    b.sysscore '成績',
    b.omclasslvl '排名'
    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=1101
    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.VW_ASR_CURSTUDSUBJTIME d
    on d.suid=b.suid and d.schlevel=b.schlevel and d.schsession=b.schsession and d.classlevel=b.classlevel
    and d.schyear=b.schyear and d.stuid=b.stuid and d.timeseq=b.timeseq and d.subjcode=b.subjcode and d.SUBJCOMP is null

    where a.schyear = ? and b.sysscore>-1
    order by a.classlvl, c.ch_des, b.omclasslvl
     
    #2 edb-catherinewschan, 2022-08-05