學生資料 學生各考績的缺席及遲到資料

本文由 Fiona 在 2021-06-17 發表於 "WebSAMS 討論區" 討論區

  1. 55040725

    Fiona
    Expand Collapse

    文章:
    7
    讚:
    0
    本校的缺席及遲到資料是每考段統計後人手輸入的,想提取全校學生本年度下列的出席及遲到資料的SQL,請賜教:

    學生註冊編號(Regno)
    班級
    班別
    學號
    性別
    學生姓名(中文)
    學生姓名(英文)
    (T1)缺席次數
    (T2)缺席次數
    (T3)缺席次數
    (T4)缺席次數
    (年終)缺席總次數
    (T1)遲到次數
    (T2)遲到次數
    (T3)遲到次數
    (T4)遲到次數
    (年終)遲到總次數
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    你好,

    可以參考 CDR 內的常用SQL,學生成績模組,
    第17條 抽取 學生全年中英數通識成績 及 上下學期的操行成績等
    再把不適用的刪除,再加上抽取T3、T4及年終的SQL語句

    select
    a.regno '學生註冊編號(Regno)',
    a.classlvl '班級',
    a.classcode '班別',
    a.classcode '學號',
    a.sex '性別',
    a.chname '學生姓名(中文)',
    a.enname '學生姓名(英文)',
    isnull(q.ADJABSENTDAY, q.ABSENTDAY) 'T1 缺席',
    isnull(r.ADJABSENTDAY, r.ABSENTDAY) 'T2 缺席',
    isnull(s.ADJABSENTDAY, s.ABSENTDAY) 'T3 缺席',
    isnull(t.ADJABSENTDAY, t.ABSENTDAY) 'T4 缺席',
    isnull(z.ADJABSENTDAY, z.ABSENTDAY) '(年終)缺席總次數',

    isnull(q.ADJLATEDAY, q.LATEDAY) 'T1 遲到',
    isnull(r.ADJLATEDAY, r.LATEDAY) 'T2 遲到',
    isnull(s.ADJLATEDAY, s.LATEDAY) 'T3 遲到',
    isnull(t.ADJLATEDAY, t.LATEDAY) 'T4 遲到',
    isnull(z.ADJLATEDAY, z.LATEDAY) '(年終)遲到總次數'

    from wsadmin.vw_stu_lateststudent a
    left outer join wsadmin.TB_HSE_COMMON b
    on a.SUID=b.SUID and b.CODE_ID=a.SCHHOUSE and b.TB_ID='SCHHUS'
    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
    left outer join wsadmin.TB_ASR_STUDMISCDATA s
    on a.suid=s.suid and a.stuid=s.stuid and a.schyear=s.schyear and s.schlevel=a.schlvl and s.schsession=a.schsess and s.classlevel=a.classlvl and s.timeseq=1300
    left outer join wsadmin.TB_ASR_STUDMISCDATA t
    on a.suid=t.suid and a.stuid=t.stuid and a.schyear=t.schyear and t.schlevel=a.schlvl and t.schsession=a.schsess and t.classlevel=a.classlvl and t.timeseq=1400
    left outer join wsadmin.TB_ASR_STUDMISCDATA z
    on a.suid=z.suid and a.stuid=z.stuid and a.schyear=z.schyear and z.schlevel=a.schlvl and z.schsession=a.schsess and z.classlevel=a.classlvl and z.timeseq=1000

    where a.schyear=? and a.classlvl=?
    order by 1,2
     
    #2 edb-catherinewschan, 2021-06-18