SQL 抽取各級學生在不同測考的所有科目分數、平均分、班名紙及級名紙的SQL

本文由 Frankie 在 2019-06-26 發表於 "WebSAMS 討論區" 討論區

  1. 10362662

    Frankie
    Expand Collapse

    文章:
    28
    讚:
    0
    想抽取各級學生在不同測考中的所有科目分數、平均分、班名紙及級名紙的SQL (包括學號、中、英文姓名、各科成績、平均分、班名紙及級名紙等資料)。謝謝!
     
    #1 Frankie, 2019-06-26
  2. 58416300

    edb-gma
    Expand Collapse

    文章:
    25
    讚:
    0
    #2 edb-gma, 2019-06-27
  3. 10362662

    Frankie
    Expand Collapse

    文章:
    28
    讚:
    0
    唔好意思,因未能在 CDR 找到其中一個SQL合本校適用。本校現欲抽取中四至中六級學生於某評核 (如T2A3) 的所有科目的分數,並以ROW的形式顯示中文姓名、中文、英文、數學、數學延伸、所有修讀的選修科、音樂、美術、體育、平均分、班名次、級名次)等資料。謝謝!
     
    #3 Frankie, 2019-07-08
  4. 58685934

    edb-ds
    Expand Collapse

    文章:
    19
    讚:
    0
    select
    a.regno '學生編號',
    a.classcode '班別',
    a.classno '班號',
    a.chname '姓名',
    g4.sysscore '中文',
    i.sysscore '英文',
    j.sysscore '數學T1',
    l.sysscore '通識',
    ll.sysscore '數學-延伸',
    m.sysscore '生物',
    n.sysscore '化學',
    o.sysscore '經濟',
    p.sysscore '視藝',
    q.sysscore '音樂',
    r.sysscore '體育',
    d1.syspercscore as '平均分',
    d1.omclasslvl as '級名次',
    d1.omclass as '班名次'
    from wsadmin.vw_stu_lateststudent a
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA g4
    on a.SUID = g4.SUID and a.STUID = g4.STUID and a.SCHYEAR = g4.SCHYEAR and g4.TIMESEQ = 1203 and g4.SUBJCODE = '80'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA i
    on a.SUID = i.SUID and a.STUID = i.STUID and a.SCHYEAR = i.SCHYEAR and i.TIMESEQ = 1203 and i.SUBJCODE = '165'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA j
    on a.SUID = j.SUID and a.STUID = j.STUID and a.SCHYEAR = j.SCHYEAR and j.TIMESEQ = 1203 and j.SUBJCODE = '22S'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA l
    on a.SUID = l.SUID and a.STUID = l.STUID and a.SCHYEAR = l.SCHYEAR and l.TIMESEQ = 1203 and l.SUBJCODE = '265'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA ll
    on a.SUID = ll.SUID and a.STUID = ll.STUID and a.SCHYEAR = ll.SCHYEAR and ll.TIMESEQ = 1203 and ll.SUBJCODE = '23S' //可任意修改選修科目的代碼表
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA m
    on a.SUID = m.SUID and a.STUID = m.STUID and a.SCHYEAR = m.SCHYEAR and m.TIMESEQ = 1203 and m.SUBJCODE = '045' //可任意修改選修科目的代碼表
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA n
    on a.SUID = n.SUID and a.STUID = n.STUID and a.SCHYEAR = n.SCHYEAR and n.TIMESEQ = 1203 and n.SUBJCODE = '070' //可任意修改選修科目的代碼表
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA o
    on a.SUID = o.SUID and a.STUID = o.STUID and a.SCHYEAR = o.SCHYEAR and o.TIMESEQ = 1203 and o.SUBJCODE = '135' //可任意修改選修科目的代碼表
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA p
    on a.SUID = o.SUID and a.STUID = o.STUID and a.SCHYEAR = o.SCHYEAR and o.TIMESEQ = 1203 and p.SUBJCODE = '432' //視藝
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA q
    on a.SUID = q.SUID and a.STUID = q.STUID and a.SCHYEAR = q.SCHYEAR and q.TIMESEQ = 1203 and q.SUBJCODE = '300' //音樂
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA r
    on a.SUID = r.SUID and a.STUID = r.STUID and a.SCHYEAR = r.SCHYEAR and o.TIMESEQ = 1203 and r.SUBJCODE = '310' //體育
    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_studassessdata d1
    on a.suid=d1.suid and a.stuid=d1.stuid and
    a.schyear=d1.schyear and a.schlvl=d1.schlevel and
    a.schsess=d1.schsession and a.classlvl=d1.classlevel and d1.TIMESEQ = 1203
    where a.schyear=? and a.classlvl=? //可改成S4 , S5 和 S6
    group by a.regno,a.classcode,a.classno,a.chname,
    g4.sysscore,i.sysscore,j.sysscore,l.sysscore,ll.sysscore,m.sysscore,n.sysscore,o.sysscore,p.sysscore,q.sysscore,r.sysscore,d1.syspercscore ,d1.omclasslvl, d1.omclass
    order by a.classcode,a.classno asc
     
    #4 edb-ds, 2019-07-15 , 12:04 下午
    Last edited: 2019-07-16 , 2:52 下午