學生成績 請求 SQL (提取某一學期的學生成績模組內的獎懲遲缺操行列表)

本文由 CHAN San Toi 在 2021-02-08 發表於 "WebSAMS 討論區" 討論區

  1. 55043023

    CHAN San Toi
    Expand Collapse

    文章:
    1
    讚:
    0
    求教:希望利用資料選取功能以下資料列表

    全校學生
    某一學期的學生成績模組內
    班別、班號、姓名(中)、姓名(英)、操行、評語、優點、小功、大功、缺點、小過、大過、缺席日數、遲到次數
     
    #1 CHAN San Toi, 2021-02-08
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    178
    讚:
    0
    你好,

    在CDR內都有一些SQL可以參考, 你可以參考一下以下的SQL

    17. 抽取 學生全年中英數通識成績 及 上下學期的操行成績等
     
    #2 edb-catherinewschan, 2021-02-10
  3. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    178
    讚:
    0
    select
    a.classcode '
    班別',
    a.chname '
    姓名',
    b.CH_DES '
    社別',
    e.sysscore '
    中文',
    f.sysscore '
    英文',
    g.sysscore '
    數學',
    h.sysscore '
    通識',
    d.syspercscore '
    全年平均分',
    d.omclasslvl '
    全年排名',
    d1.overcondgradeconversioncompcode 'T1
    操行',
    d2.overcondgradeconversioncompcode 'T2
    操行',
    r.ADJLVL1MERIT 'T2
    獎勵級別1',
    r.ADJLVL2MERIT 'T2
    獎勵級別2',
    r.ADJLVL3MERIT 'T2
    獎勵級別3',
    r.ADJLVL4MERIT 'T2
    獎勵級別4',
    r.ADJLVL5MERIT 'T2
    獎勵級別5',
    r.ADJLVL1DEMERIT 'T2
    懲罰級別1',
    r.ADJLVL2DEMERIT 'T2
    懲罰級別2',
    r.ADJLVL3DEMERIT 'T2
    懲罰級別3',
    r.ADJLVL4DEMERIT 'T2
    懲罰級別4',
    r.ADJLVL5DEMERIT 'T2
    懲罰級別5',
    isnull(q.ADJABSENTDAY, q.ABSENTDAY) 'T1
    缺席',
    isnull(r.ADJABSENTDAY, r.ABSENTDAY) 'T2
    缺席',
    isnull(q.ADJLATEDAY, q.LATEDAY) 'T1
    遲到',
    isnull(r.ADJLATEDAY, r.LATEDAY) 'T2
    遲到',
    d2.COMMENTDESCENG 'T2
    評語'
    from vw_stu_lateststudent a
    left outer join TB_HSE_COMMON b
    on a.SUID=b.SUID and b.CODE_ID=a.SCHHOUSE and b.TB_ID='SCHHUS'
    left outer join 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 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 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_SUBJASSESSDATA e
    on a.SUID = e.SUID and a.STUID = e.STUID and a.SCHYEAR = e.SCHYEAR and e.TIMESEQ = 1000 and e.SUBJCODE = '080'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA f
    on a.SUID = f.SUID and a.STUID = f.STUID and a.SCHYEAR = f.SCHYEAR and f.TIMESEQ = 1000 and f.SUBJCODE = '165'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA g
    on a.SUID = g.SUID and a.STUID = g.STUID and a.SCHYEAR = g.SCHYEAR and g.TIMESEQ = 1000 and g.SUBJCODE = '280'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA h
    on a.SUID = h.SUID and a.STUID = h.STUID and a.SCHYEAR = h.SCHYEAR and h.TIMESEQ = 1000 and h.SUBJCODE = '265'
    left outer join 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 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
     
    #3 edb-catherinewschan, 2021-02-10