SQL 求SQL 抽取每班學生考試成績資料

本文由 t-hong 在 2022-07-28 發表於 "WebSAMS 討論區" 討論區

  1. 55497624

    t-hong
    Expand Collapse

    文章:
    9
    讚:
    0
    請問如何利用SQL抽取學生的成績資料?
    包括

    班別
    學號
    姓名
    性別
    T1平均分
    T2平均分
    T1總分
    T2總分
    T2操行
    T1中文分
    T1英文分
    T1數學分
    T1常識分
    T2中文分
    T2英文分
    T2數學分
    T2常識分

    謝謝!
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    250
    讚:
    0
    你好,

    沒有T1、T2總分, 只得平均分。請參考以下的SQL。

    select
    a.classcode '班別',
    a.classno '班號',
    a.enname '英文姓名',
    a.chname '中文姓名',
    a.sex '性別',

    b.syspercscore 'T1 總平均分',
    d.syspercscore 'T2 總平均分',
    f.syspercscore '全年總平均分',


    g.overcondgradeconversioncompcode 'T1 操行',
    h.overcondgradeconversioncompcode 'T2 操行',

    i.sysscore 'T1中文總分',
    j.sysscore 'T1英文總分',
    k.sysscore 'T1數學總分',
    l.sysscore 'T1常識總分',

    m.sysscore 'T2中文總分',
    n.sysscore 'T2英文總分',
    o.sysscore 'T2數學總分',
    p.sysscore 'T2常識總分'

    from wsadmin.vw_stu_lateststudent a
    left outer join wsadmin.tb_asr_studassessdata b
    on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear and b.timeseq=1100
    left outer join wsadmin.tb_asr_studassessdata d
    on a.suid=d.suid and a.stuid=d.stuid and a.schyear=d.schyear and d.timeseq=1200
    left outer join wsadmin.tb_asr_studassessdata f
    on a.suid=f.suid and a.stuid=f.stuid and a.schyear=f.schyear and f.timeseq=1000
    left outer join wsadmin.tb_asr_studassessdata g
    on a.suid=g.suid and a.stuid=g.stuid and a.schyear=g.schyear and g.timeseq=1100
    left outer join wsadmin.tb_asr_studassessdata h
    on a.suid=h.suid and a.stuid=h.stuid and a.schyear=h.schyear and h.timeseq=1200

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA i
    on a.SUID=i.SUID and a.SCHYEAR=i.SCHYEAR and a.STUID=i.STUID and i.SUBJCODE='080' and i.TIMESEQ=1100
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA j
    on a.SUID=j.SUID and a.SCHYEAR=j.SCHYEAR and a.STUID=j.STUID and j.SUBJCODE='165' and j.TIMESEQ=1100
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA k
    on a.SUID=k.SUID and a.SCHYEAR=k.SCHYEAR and a.STUID=k.STUID and k.SUBJCODE='280' and k.TIMESEQ=1100
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA l
    on a.SUID=l.SUID and a.SCHYEAR=l.SCHYEAR and a.STUID=l.STUID and l.SUBJCODE='205' and l.TIMESEQ=1100

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA m
    on a.SUID=m.SUID and a.SCHYEAR=m.SCHYEAR and a.STUID=m.STUID and m.SUBJCODE='080' and m.TIMESEQ=1200
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA n
    on a.SUID=n.SUID and a.SCHYEAR=n.SCHYEAR and a.STUID=n.STUID and n.SUBJCODE='165' and n.TIMESEQ=1200
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA o
    on a.SUID=o.SUID and a.SCHYEAR=o.SCHYEAR and a.STUID=o.STUID and o.SUBJCODE='280' and o.TIMESEQ=1200
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA p
    on a.SUID=p.SUID and a.SCHYEAR=p.SCHYEAR and a.STUID=p.STUID and p.SUBJCODE='205' and p.TIMESEQ=1200

    where a.schyear=? and a.classlvl=?
    order by a.classcode, a.classno, a.enname
     
    #2 edb-catherinewschan, 2022-07-28