SQL 想知道SQL如何可以查詢每班(今年學號班別)上年度英文科分數及排名

本文由 Nick 在 2021-09-02 發表於 "WebSAMS 討論區" 討論區

  1. 55019125

    Nick
    Expand Collapse

    文章:
    14
    讚:
    0
    想知道如何用SQL查詢每班(今年學號班別)上年度英文科分數及排名
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    213
    讚:
    0
    請試試以下的SQL


    select
    a.classcode,
    a.classno,
    a.chname,
    d.ch_des,
    d.en_des,
    b.sysscore,
    b.omclass 'LastYear OM'
    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=a.schyear-1 and a.stuid=b.stuid and b.timeseq=? and b.subjcode in ('165')
    left outer join wsadmin.tb_hse_common d
    on a.suid=d.suid and b.subjcode=d.code_id and d.tb_id='SBJ'
    where a.schyear=? and a.classlvl=?
    order by a.schlvl, a.schsess, a.classlvl, a.classcode, d.ch_des, a.classno
     
    #2 edb-catherinewschan, 2021-09-03