SQL 如何利用SQL尋找學生的升班資料

本文由 tr-wyyu 在 2020-06-04 發表於 "WebSAMS 討論區" 討論區

  1. 55072044

    tr-wyyu
    Expand Collapse

    文章:
    14
    讚:
    0
    請教各位,

    如何利用SQL在WebSAMS追查學生的中、英、數成績及升班資料?

    現時用的SQL如下:

    select
    a.classcode '班別',
    a.classno '學號',
    a.enname '學生英文姓名',
    a.chname '學生姓名',
    a.sex '性別',
    e.sysscore '中文分數',
    f.sysscore '英文分數',
    g.sysscore '數學分數',
    (case when sum(i.SYSPERCSCORE) <> 0 then sum(i.SYSPERCSCORE) / 3 else 0 end) '平均分'

    from wsadmin.vw_stu_lateststudent a
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA e on a.SUID = e.SUID and a.SCHLVL = e.SCHLEVEL and a.SCHSESS = e.SCHSESSION
    and a.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.SUBJCODE = '080' and e.TIMESEQ = '1000'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA f on a.SUID = f.SUID and a.SCHLVL = f.SCHLEVEL and a.SCHSESS = f.SCHSESSION
    and a.SCHYEAR = f.SCHYEAR and a.STUID = f.STUID and f.SUBJCODE = '165' and f.TIMESEQ = '1000'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA g on a.SUID = g.SUID and a.SCHLVL = g.SCHLEVEL and a.SCHSESS = g.SCHSESSION
    and a.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and g.SUBJCODE = '280' and g.TIMESEQ = '1000'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA i
    on a.SUID=i.SUID and a.SCHYEAR=i.SCHYEAR and a.STUID=i.STUID and i.SUBJCODE in ('080','165','280') and i.TIMESEQ=1000

    where a.schyear= ? and a.classlvl= ?
    group by a.classcode, a.classno, a.enname, a.chname,a.sex,e.sysscore, f.sysscore, g.sysscore
    order by a.classcode, a.classno


    Leo
     
    #1 tr-wyyu, 2020-06-04
    Last edited: 2020-06-04
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    107
    讚:
    0
    你好,

    升班資料是否顯示下一年度的班別呢???
    我加了升班資料的欄位, 並顯示出下一年的班別... ..

    你可以參考一下以下的SQL

    select
    a.schyear,
    a.classcode '班別',
    a.classno '學號',
    a.enname '學生英文姓名',
    a.chname '學生姓名',
    a.sex '性別',
    e.sysscore '中文分數',
    f.sysscore '英文分數',
    g.sysscore '數學分數',
    (case when sum(i.SYSPERCSCORE) <> 0 then sum(i.SYSPERCSCORE) / 3 else 0 end) '平均分',
    (select j.classcode from wsadmin.tb_stu_stuschrec j where j.stuid=a.stuid and schyear=a.schyear+1) as '升班資料'

    from wsadmin.vw_stu_lateststudent a
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA e on a.SUID = e.SUID and a.SCHLVL = e.SCHLEVEL and a.SCHSESS = e.SCHSESSION
    and a.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.SUBJCODE = '080' and e.TIMESEQ = '1000'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA f on a.SUID = f.SUID and a.SCHLVL = f.SCHLEVEL and a.SCHSESS = f.SCHSESSION
    and a.SCHYEAR = f.SCHYEAR and a.STUID = f.STUID and f.SUBJCODE = '165' and f.TIMESEQ = '1000'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA g on a.SUID = g.SUID and a.SCHLVL = g.SCHLEVEL and a.SCHSESS = g.SCHSESSION
    and a.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and g.SUBJCODE = '280' and g.TIMESEQ = '1000'

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA i
    on a.SUID=i.SUID and a.SCHYEAR=i.SCHYEAR and a.STUID=i.STUID and i.SUBJCODE in ('080','165','280') and i.TIMESEQ=1000
    where a.schyear=? and a.classlvl= ?
    group by a.stuid, a.schyear, a.classcode, a.classno, a.enname, a.chname,a.sex,e.sysscore, f.sysscore, g.sysscore
    order by a.classcode, a.classno
     
    #2 edb-catherinewschan, 2020-06-08