SQL 抽取上學年成績與今學年成績進步最大的學生

本文由 jj-admin 在 2020-07-09 發表於 "WebSAMS 討論區" 討論區

  1. 11299843

    jj-admin
    Expand Collapse

    文章:
    1
    讚:
    0
    請問如何抽取上學年成績與今學年成績進步最大的學生
     
    #1 jj-admin, 2020-07-09
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    124
    讚:
    0
    你好,

    可以參考以下的SQL

    select
    a.CLASSCODE '班別',
    a.CLASSNO '學號',
    a.CHNAME '姓名',
    str(avg(b.syspercscore),5,2) 'b.schyear 總平均分',
    str(avg(c.syspercscore),5,2) 'c.schyear 總平均分',
    str((case when avg(b.syspercscore) is null then 0 else avg(b.syspercscore) end) - (case when avg(c.syspercscore) is null then 0 else avg(c.syspercscore) end),5,2) 'b.schyear 總平均分 - c.schyear 總平均分進步/退步'
    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 b.schyear=? and a.stuid=b.stuid and b.timeseq=?
    left outer join wsadmin.tb_asr_subjassessdata c
    on a.suid=c.suid and a.schlvl=c.schlevel and a.schsess=c.schsession and a.classlvl=c.classlevel and c.schyear=? and a.stuid=c.stuid and b.subjcode=c.subjcode and c.timeseq=?
    where b.sysscore is not null
    group by a.classcode, a.CLASSNO, a.chname
    order by a.classcode, a.CLASSNO, a.chname
     
    #2 edb-catherinewschan, 2020-07-13