學生成績 抽取有特定排序的英文科成績

本文由 Daniel 在 2015-08-25 發表於 "WebSAMS 討論區" 討論區

  1. 55056512

    Daniel
    Expand Collapse

    文章:
    66
    讚:
    0
    下面的SQL抽取英文科總分、卷一和卷二分數,請問該如何修改,才能抽取以下3個檔案:

    (1) 以英文科總分排序
      E.g 總分
    1B 10 Chan Tai Man 陳大文 100
    1A 22 Chan Siu Man 陳小文 98
    ...

    (2) 以英文科卷一排序  
      E.g 卷一分
    1C 8 ... ... 100
    1D 9 ... ... 99

    (3)以英文科卷二排序
      E.g 卷二分
    1A 18 ... ... 99
    1D 19 ... ... 97

    謝謝幫忙!
    Daniel

    ******************************************************************

    select
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    a.ENNAME '英文姓名',
    a.CHNAME '中文姓名',
    c.sysscore 'English total marks',
    d.sysscore 'Paper 1',
    e.sysscore 'Paper 2' from wsadmin.VW_STU_LATESTSTUDENT a

    left outer join tb_asr_time b on b.timeseq=?

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA c
    on a.SUID = c.SUID and a.SCHLVL = c.SCHLEVEL and a.SCHSESS = c.SCHSESSION and a.SCHYEAR = c.SCHYEAR and a.STUID = c.STUID and c.SUBJCODE = '165' and b.TIMESEQ = c.TIMESEQ

    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d on a.SUID = d.SUID and a.SCHLVL = d.SCHLEVEL and a.SCHSESS = d.SCHSESSION and a.SCHYEAR = d.SCHYEAR and a.STUID = d.STUID and d.SUBJCODE = '165' and d.SUBJCOMPCODE = '01' and c.TIMESEQ = d.TIMESEQ

    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e on a.SUID = e.SUID and a.SCHLVL = e.SCHLEVEL and a.SCHSESS = e.SCHSESSION and a.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.SUBJCODE = '165' and e.SUBJCOMPCODE = '02' and c.TIMESEQ = e.TIMESEQ

    where a.schyear = ? and a.classlvl=?
    order by a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO
     
  2. 57678715

    edb-escm
    Expand Collapse

    文章:
    434
    讚:
    0
    老師請參考以下SQL:
    1.select
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    a.ENNAME '英文姓名',
    a.CHNAME '中文姓名',
    c.sysscore 'English total marks'
    from wsadmin.VW_STU_LATESTSTUDENT a

    left outer join tb_asr_time b on b.timeseq=?

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA c
    on a.SUID = c.SUID and a.SCHLVL = c.SCHLEVEL and a.SCHSESS = c.SCHSESSION and a.SCHYEAR = c.SCHYEAR and a.STUID = c.STUID and c.SUBJCODE = '165' and b.TIMESEQ = c.TIMESEQ

    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d on a.SUID = d.SUID and a.SCHLVL = d.SCHLEVEL and a.SCHSESS = d.SCHSESSION and a.SCHYEAR = d.SCHYEAR and a.STUID = d.STUID and d.SUBJCODE = '165' and d.SUBJCOMPCODE = '01' and c.TIMESEQ = d.TIMESEQ

    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e on a.SUID = e.SUID and a.SCHLVL = e.SCHLEVEL and a.SCHSESS = e.SCHSESSION and a.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.SUBJCODE = '165' and e.SUBJCOMPCODE = '02' and c.TIMESEQ = e.TIMESEQ

    where a.schyear = ? and a.classlvl=?
    order by c.sysscore DESC

    2.select
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    a.ENNAME '英文姓名',
    a.CHNAME '中文姓名',
    d.sysscore 'Paper 1'
    from wsadmin.VW_STU_LATESTSTUDENT a

    left outer join tb_asr_time b on b.timeseq=?

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA c
    on a.SUID = c.SUID and a.SCHLVL = c.SCHLEVEL and a.SCHSESS = c.SCHSESSION and a.SCHYEAR = c.SCHYEAR and a.STUID = c.STUID and c.SUBJCODE = '165' and b.TIMESEQ = c.TIMESEQ

    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d on a.SUID = d.SUID and a.SCHLVL = d.SCHLEVEL and a.SCHSESS = d.SCHSESSION and a.SCHYEAR = d.SCHYEAR and a.STUID = d.STUID and d.SUBJCODE = '165' and d.SUBJCOMPCODE = '01' and c.TIMESEQ = d.TIMESEQ

    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e on a.SUID = e.SUID and a.SCHLVL = e.SCHLEVEL and a.SCHSESS = e.SCHSESSION and a.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.SUBJCODE = '165' and e.SUBJCOMPCODE = '02' and c.TIMESEQ = e.TIMESEQ

    where a.schyear = ? and a.classlvl=?
    order by d.sysscore DESC

    3.select
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    a.ENNAME '英文姓名',
    a.CHNAME '中文姓名',
    e.sysscore 'Paper 2'
    from wsadmin.VW_STU_LATESTSTUDENT a

    left outer join tb_asr_time b on b.timeseq=?

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA c
    on a.SUID = c.SUID and a.SCHLVL = c.SCHLEVEL and a.SCHSESS = c.SCHSESSION and a.SCHYEAR = c.SCHYEAR and a.STUID = c.STUID and c.SUBJCODE = '165' and b.TIMESEQ = c.TIMESEQ

    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d on a.SUID = d.SUID and a.SCHLVL = d.SCHLEVEL and a.SCHSESS = d.SCHSESSION and a.SCHYEAR = d.SCHYEAR and a.STUID = d.STUID and d.SUBJCODE = '165' and d.SUBJCOMPCODE = '01' and c.TIMESEQ = d.TIMESEQ

    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e on a.SUID = e.SUID and a.SCHLVL = e.SCHLEVEL and a.SCHSESS = e.SCHSESSION and a.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.SUBJCODE = '165' and e.SUBJCOMPCODE = '02' and c.TIMESEQ = e.TIMESEQ

    where a.schyear = ? and a.classlvl=?
    order by e.sysscore DESC
     
    #2 edb-escm, 2015-08-25