SQL 提取學生級名次及缺席遲到紀錄及以往就讀學校紀錄

本文由 香港真光中學小學部 在 2022-01-05 發表於 "WebSAMS 討論區" 討論區

  1. 10422499

    香港真光中學小學部
    Expand Collapse

    文章:
    1
    讚:
    0
    你好:
    想請教如要一次過抽取以下資料(每一學期計),SQL應怎樣做?
    學生姓名,班別,學號,班名次,級名次,操行,缺席次數,遲到次數,以前就讀的學校

    謝謝
     
    #1 香港真光中學小學部, 2022-01-05
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    224
    讚:
    0
    你好

    可以參考一下以下的SQL, 可以分別抽取T1和T2的操行和缺席

    select
    a.chname '姓名',
    a.classcode '班別',
    a.classno '學號',
    d.omclass '全年班名次',
    d.omclasslvl '全年級名次',
    d.syspercscore '全年平均分',
    d1.overcondgradeconversioncompcode 'T1 操行',
    d2.overcondgradeconversioncompcode 'T2 操行',
    isnull(q.ADJABSENTDAY, q.ABSENTDAY) 'T1 缺席',
    isnull(r.ADJABSENTDAY, r.ABSENTDAY) 'T2 缺席',
    isnull(q.ADJLATEDAY, q.LATEDAY) 'T1 遲到',
    isnull(r.ADJLATEDAY, r.LATEDAY) 'T2 遲到',
    a.SCHFROM '以前就讀學校'
    from wsadmin.vw_stu_lateststudent a
    left outer join wsadmin.tb_asr_studassessdata d
    on a.suid=d.suid and a.stuid=d.stuid and d.schyear=a.schyear and d.timeseq=1000
    left outer join wsadmin.tb_asr_studassessdata d1
    on a.suid=d1.suid and a.stuid=d1.stuid and d1.schyear=a.schyear and d1.timeseq=1100
    left outer join wsadmin.tb_asr_studassessdata d2
    on a.suid=d2.suid and a.stuid=d2.stuid and d2.schyear=a.schyear and d2.timeseq=1200
    left outer join wsadmin.TB_ASR_STUDMISCDATA q
    on a.suid=q.suid and a.stuid=q.stuid and a.schyear=q.schyear and q.schlevel=a.schlvl and q.schsession=a.schsess and q.classlevel=a.classlvl and q.timeseq=1100
    left outer join wsadmin.TB_ASR_STUDMISCDATA r
    on a.suid=r.suid and a.stuid=r.stuid and a.schyear=r.schyear and r.schlevel=a.schlvl and r.schsession=a.schsess and r.classlevel=a.classlvl and r.timeseq=1200
    where a.schyear=? and a.classlvl=?
    order by 1,2
     
    #2 edb-catherinewschan, 2022-01-06