SQL 提取學生成績

本文由 tr-mussochan 在 2021-06-25 發表於 "WebSAMS 討論區" 討論區

  1. 57307132

    tr-mussochan
    Expand Collapse

    文章:
    3
    讚:
    0
    求一個sql,
    來提取本年度內分別T1 、T2、全年,

    1)小一至小六學生的各科分數(包括中文及英文分卷的分數)

    2)總平均分數

    3) 級名次及班名次

    4) 操行(紀律、盡責、禮貌、整潔)

    5) 遲到及缺席

    6) 功過(大功小功優點、大過小過缺點)

    感謝!
     
    #1 tr-mussochan, 2021-06-25
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    #2 edb-catherinewschan, 2021-06-29
  3. 57307132

    tr-mussochan
    Expand Collapse

    文章:
    3
    讚:
    0
    [FONT=Times New Roman, serif]如果想抽取學生性別及顯示[/FONT]中文及英文分卷(聽說讀寫)的分數,應如何修改?
    感激!

    select c.classlvl as '
    班級', s.classname as '班別', c.classno as '班號', c.chname as '中文姓名',c.enname as '英文姓名', a1.sysscore as '中文', a2.sysscore as '英文', a3.sysscore as '數學', a4.sysscore as '常識', a5.sysscore as '音樂', a6.sysscore as '視藝', a7.SYSGRADECONVCOMPCODE as '體育', a8.sysscore as '宗教', a9.SYSGRADECONVCOMPCODE as '電腦', d1.syspercscore as '平均分',d1.omclasslvl as '級名次', d1.omclass as '班名次', dc1.overcondgradeconversioncompcode as '操行', o.ABSENTDAY as '缺席日數', d1.COMMENTDESCCHI '整體評語' from vw_stu_lateststudent c left outer join TB_SCH_SCHCLASS s on s.SUID=c.SUID and s.SCHYEAR=c.SCHYEAR and s.SCHLEVEL=c.SCHLVL and s.SCHSESSION=c.SCHSESS and s.CLASSLEVEL=c.CLASSLVL and s.CLASSCODE=c.CLASSCODE left outer join tb_asr_studassessdata d1 on c.suid=d1.suid and c.stuid=d1.stuid and c.schyear=d1.schyear and c.schlvl=d1.schlevel and c.schsess=d1.schsession and c.classlvl=d1.classlevel left outer join tb_asr_subjassessdata a1 on c.suid=a1.suid and c.stuid=a1.stuid and a1.subjcode='080' and c.schyear=a1.schyear and c.schlvl=a1.schlevel and c.schsess=a1.schsession and c.classlvl=a1.classlevel and a1.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a2 on c.suid=a2.suid and c.stuid=a2.stuid and a2.subjcode='165' and c.schyear=a2.schyear and c.schlvl=a2.schlevel and c.schsess=a2.schsession and c.classlvl=a2.classlevel and a2.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a3 on c.suid=a3.suid and c.stuid=a3.stuid and a3.subjcode='280' and c.schyear=a3.schyear and c.schlvl=a3.schlevel and c.schsess=a3.schsession and c.classlvl=a3.classlevel and a3.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a4 on c.suid=a4.suid and c.stuid=a4.stuid and a4.subjcode='205' and c.schyear=a4.schyear and c.schlvl=a4.schlevel and c.schsess=a4.schsession and c.classlvl=a4.classlevel and a4.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a5 on c.suid=a5.suid and c.stuid=a5.stuid and a5.subjcode='300' and c.schyear=a5.schyear and c.schlvl=a3.schlevel and c.schsess=a5.schsession and c.classlvl=a5.classlevel and a5.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a6 on c.suid=a6.suid and c.stuid=a6.stuid and a6.subjcode='432' and c.schyear=a6.schyear and c.schlvl=a6.schlevel and c.schsess=a6.schsession and c.classlvl=a6.classlevel and a6.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a7 on c.suid=a3.suid and c.stuid=a7.stuid and a7.subjcode='310' and c.schyear=a7.schyear and c.schlvl=a7.schlevel and c.schsess=a7.schsession and c.classlvl=a7.classlevel and a7.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a8 on c.suid=a8.suid and c.stuid=a8.stuid and a8.subjcode='185' and c.schyear=a8.schyear and c.schlvl=a8.schlevel and c.schsess=a8.schsession and c.classlvl=a8.classlevel and a8.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a9 on c.suid=a9.suid and c.stuid=a9.stuid and a9.subjcode='991' and c.schyear=a9.schyear and c.schlvl=a9.schlevel and c.schsess=a9.schsession and c.classlvl=a9.classlevel and a9.timeseq=d1.timeseq left outer join tb_asr_studassessdata dc1 on c.suid=dc1.suid and c.stuid=dc1.stuid and c.schyear=dc1.schyear and c.schlvl=dc1.schlevel and c.schsess=dc1.schsession and c.classlvl=dc1.classlevel and dc1.timeseq=d1.timeseq left outer join TB_ASR_STUDMISCDATA o on o.suid=c.suid and o.stuid=c.stuid and o.schyear=c.schyear and o.schlevel=c.schlvl and o.schsession=c.schsess and o.classlevel=c.classlvl and o.timeseq=d1.timeseq where c.schyear=? and d1.timeseq=? and c.classlvl=? order by c.classcode, c.classno
     
    #3 tr-mussochan, 2021-06-30
  4. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    你好,

    性別,加上c.sex 這個欄位就可以。
    至於分卷,可以參考以下的SQL有Highlight的部份,再自己加上分卷編號。

    select
    c.stuid,
    c.classlvl as '班級',
    s.classname as '班別',
    c.classno as '班號',
    c.chname as '中文姓名',
    c.enname as '英文姓名',
    c.sex as '性別',
    a1.sysscore as '中文',
    e1.sysscore as '中文分卷1',
    a2.sysscore as '英文',
    f1.sysscore as '英文分卷1',
    a3.sysscore as '數學',
    a4.sysscore as '常識',
    a5.sysscore as '音樂',
    a6.sysscore as '視藝',
    a7.SYSGRADECONVCOMPCODE as '體育',
    a8.sysscore as '宗教',
    a9.SYSGRADECONVCOMPCODE as '電腦',
    d1.syspercscore as '平均分',
    d1.omclasslvl as '級名次',
    d1.omclass as '班名次',
    dc1.overcondgradeconversioncompcode as '操行',
    o.ABSENTDAY as '缺席日數',
    d1.COMMENTDESCCHI '整體評語'
    from wsadmin.vw_stu_lateststudent c
    left outer join wsadmin.TB_SCH_SCHCLASS s on s.SUID=c.SUID and s.SCHYEAR=c.SCHYEAR and s.SCHLEVEL=c.SCHLVL and s.SCHSESSION=c.SCHSESS and s.CLASSLEVEL=c.CLASSLVL and s.CLASSCODE=c.CLASSCODE
    left outer join wsadmin.tb_asr_studassessdata d1 on c.suid=d1.suid and c.stuid=d1.stuid and c.schyear=d1.schyear and c.schlvl=d1.schlevel and c.schsess=d1.schsession and c.classlvl=d1.classlevel
    left outer join wsadmin.tb_asr_subjassessdata a1 on c.suid=a1.suid and c.stuid=a1.stuid and a1.subjcode='080' and c.schyear=a1.schyear and c.schlvl=a1.schlevel and c.schsess=a1.schsession and c.classlvl=a1.classlevel and a1.timeseq=d1.timeseq
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e1 on a1.suid=e1.suid and a1.stuid=e1.stuid and a1.SUBJCODE=e1.SUBJCODE and e1.SUBJCOMPCODE='01' and e1.schyear=a1.schyear and e1.schlevel =a1.schlevel and e1.schsession =a1.schsession and e1.classlevel=a1.classlevel and e1.timeseq=d1.timeseq

    left outer join wsadmin.tb_asr_subjassessdata a2 on c.suid=a2.suid and c.stuid=a2.stuid and a2.subjcode='165' and c.schyear=a2.schyear and c.schlvl=a2.schlevel and c.schsess=a2.schsession and c.classlvl=a2.classlevel and a2.timeseq=d1.timeseq
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA f1 on a2.suid=f1.suid and a2.stuid=f1.stuid and a2.SUBJCODE=f1.SUBJCODE and f1.SUBJCOMPCODE='01' and f1.schyear=a2.schyear and f1.schlevel =a2.schlevel and f1.schsession =a2.schsession and f1.classlevel=a2.classlevel and f1.timeseq=d1.timeseq

    left outer join wsadmin.tb_asr_subjassessdata a3 on c.suid=a3.suid and c.stuid=a3.stuid and a3.subjcode='280' and c.schyear=a3.schyear and c.schlvl=a3.schlevel and c.schsess=a3.schsession and c.classlvl=a3.classlevel and a3.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjassessdata a4 on c.suid=a4.suid and c.stuid=a4.stuid and a4.subjcode='205' and c.schyear=a4.schyear and c.schlvl=a4.schlevel and c.schsess=a4.schsession and c.classlvl=a4.classlevel and a4.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjassessdata a5 on c.suid=a5.suid and c.stuid=a5.stuid and a5.subjcode='300' and c.schyear=a5.schyear and c.schlvl=a3.schlevel and c.schsess=a5.schsession and c.classlvl=a5.classlevel and a5.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjassessdata a6 on c.suid=a6.suid and c.stuid=a6.stuid and a6.subjcode='432' and c.schyear=a6.schyear and c.schlvl=a6.schlevel and c.schsess=a6.schsession and c.classlvl=a6.classlevel and a6.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjassessdata a7 on c.suid=a3.suid and c.stuid=a7.stuid and a7.subjcode='310' and c.schyear=a7.schyear and c.schlvl=a7.schlevel and c.schsess=a7.schsession and c.classlvl=a7.classlevel and a7.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjassessdata a8 on c.suid=a8.suid and c.stuid=a8.stuid and a8.subjcode='185' and c.schyear=a8.schyear and c.schlvl=a8.schlevel and c.schsess=a8.schsession and c.classlvl=a8.classlevel and a8.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjassessdata a9 on c.suid=a9.suid and c.stuid=a9.stuid and a9.subjcode='991' and c.schyear=a9.schyear and c.schlvl=a9.schlevel and c.schsess=a9.schsession and c.classlvl=a9.classlevel and a9.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_studassessdata dc1 on c.suid=dc1.suid and c.stuid=dc1.stuid and c.schyear=dc1.schyear and c.schlvl=dc1.schlevel and c.schsess=dc1.schsession and c.classlvl=dc1.classlevel and dc1.timeseq=d1.timeseq
    left outer join wsadmin.TB_ASR_STUDMISCDATA o on o.suid=c.suid and o.stuid=c.stuid and o.schyear=c.schyear and o.schlevel=c.schlvl and o.schsession=c.schsess and o.classlevel=c.classlvl and o.timeseq=d1.timeseq
    where c.schyear=? and d1.timeseq=? and c.classlvl=? order by c.classcode, c.classno
     
    #4 edb-catherinewschan, 2021-07-07