用SQL抽取學生不同學年某一次考試的平均分及操行

本文由 發奮 在 2007-05-04 發表於 "WebSAMS 討論區" 討論區

  1. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0

    ptm-csc 君:

    請試試以下 SQL 語句。


    select
      c.classlvl as '班級',
      s.classname as '班別',
      c.classno as '班號',
      c.chname as '中文姓名',
      c.enname as '英文姓名',
      a1.sysscore as '中文',
      a1_02.sysscore as '中文-閱讀',
      a1_03.sysscore as '中文-寫作',
      a1_04.sysscore as '中文-默書',
      a1_06.sysscore as '中文-聆聽',
      a1_07.sysscore as '中文-說話',
      a2.sysscore as '英文',
      a2_02.sysscore as '英文-默書',
      a2_05.sysscore as '英文-讀寫',
      a2_06.sysscore as '英文-聆聽',
      a2_07.sysscore as '英文-說話',
      a3.sysscore as '數學',
      a4.sysscore as '常識',
      a5.SYSGRADECONVCOMPCODE as '音樂',
      a6.SYSGRADECONVCOMPCODE as '視藝',
      a7.SYSGRADECONVCOMPCODE as '體育',
      a8.SYSGRADECONVCOMPCODE as '普通話',
      a9.SYSGRADECONVCOMPCODE as '電腦',
      d1.syspercscore as '平均分',
      d1.omclasslvl 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_time t
      on t.TIMESEQ=?

    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 and
         d1.TIMESEQ=t.TIMESEQ

    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=t.timeseq
    left outer join tb_asr_subjcomassessdata a1_02
      on c.suid=a1_02.suid and c.stuid=a1_02.stuid and a1_02.subjcode='080' and
         a1_02.subjcompcode='02' and
         c.schyear=a1_02.schyear and c.schlvl=a1_02.schlevel and
         c.schsess=a1_02.schsession and c.classlvl=a1_02.classlevel and
         a1_02.timeseq=t.timeseq
    left outer join tb_asr_subjcomassessdata a1_03
      on c.suid=a1_03.suid and c.stuid=a1_03.stuid and a1_03.subjcode='080' and
         a1_03.subjcompcode='03' and
         c.schyear=a1_03.schyear and c.schlvl=a1_03.schlevel and
         c.schsess=a1_03.schsession and c.classlvl=a1_03.classlevel and
         a1_03.timeseq=t.timeseq
    left outer join tb_asr_subjcomassessdata a1_04
      on c.suid=a1_04.suid and c.stuid=a1_04.stuid and a1_04.subjcode='080' and
         a1_04.subjcompcode='04' and
         c.schyear=a1_04.schyear and c.schlvl=a1_04.schlevel and
         c.schsess=a1_04.schsession and c.classlvl=a1_04.classlevel and
         a1_04.timeseq=t.timeseq
    left outer join tb_asr_subjcomassessdata a1_06
      on c.suid=a1_06.suid and c.stuid=a1_06.stuid and a1_06.subjcode='080' and
         a1_06.subjcompcode='06' and
         c.schyear=a1_06.schyear and c.schlvl=a1_06.schlevel and
         c.schsess=a1_06.schsession and c.classlvl=a1_06.classlevel and
         a1_06.timeseq=t.timeseq
    left outer join tb_asr_subjcomassessdata a1_07
      on c.suid=a1_07.suid and c.stuid=a1_07.stuid and a1_07.subjcode='080' and
         a1_07.subjcompcode='07' and
         c.schyear=a1_07.schyear and c.schlvl=a1_07.schlevel and
         c.schsess=a1_07.schsession and c.classlvl=a1_07.classlevel and
         a1_07.timeseq=t.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=t.timeseq
    left outer join tb_asr_subjcomassessdata a2_02
      on c.suid=a2_02.suid and c.stuid=a2_02.stuid and a2_02.subjcode='165' and
         a2_02.subjcompcode='02' and
         c.schyear=a2_02.schyear and c.schlvl=a2_02.schlevel and
         c.schsess=a2_02.schsession and c.classlvl=a2_02.classlevel and
         a2_02.timeseq=t.timeseq
    left outer join tb_asr_subjcomassessdata a2_05
      on c.suid=a2_05.suid and c.stuid=a2_05.stuid and a2_05.subjcode='165' and
         a2_05.subjcompcode='05' and
         c.schyear=a2_05.schyear and c.schlvl=a2_05.schlevel and
         c.schsess=a2_05.schsession and c.classlvl=a2_05.classlevel and
         a2_05.timeseq=t.timeseq
    left outer join tb_asr_subjcomassessdata a2_06
      on c.suid=a2_06.suid and c.stuid=a2_06.stuid and a2_06.subjcode='165' and
         a2_06.subjcompcode='06' and
         c.schyear=a2_06.schyear and c.schlvl=a2_06.schlevel and
         c.schsess=a2_06.schsession and c.classlvl=a2_06.classlevel and
         a2_06.timeseq=t.timeseq
    left outer join tb_asr_subjcomassessdata a2_07
      on c.suid=a2_07.suid and c.stuid=a2_07.stuid and a2_07.subjcode='165' and
         a2_07.subjcompcode='07' and
         c.schyear=a2_07.schyear and c.schlvl=a2_07.schlevel and
         c.schsess=a2_07.schsession and c.classlvl=a2_07.classlevel and
         a2_07.timeseq=t.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=t.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=t.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=t.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=t.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=t.timeseq

    left outer join tb_asr_subjassessdata a8
      on c.suid=a8.suid and c.stuid=a8.stuid and a8.subjcode='350' and
         c.schyear=a8.schyear and c.schlvl=a8.schlevel and
         c.schsess=a8.schsession and c.classlvl=a8.classlevel and
         a8.timeseq=t.timeseq

    left outer join tb_asr_subjassessdata a9
      on c.suid=a9.suid and c.stuid=a9.stuid and a9.subjcode='115' and
         c.schyear=a9.schyear and c.schlvl=a9.schlevel and
         c.schsess=a9.schsession and c.classlvl=a9.classlevel and
         a9.timeseq=t.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=t.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=t.timeseq

    where c.schyear=? and c.classlvl=?
    order by  c.classcode, c.classno

    系統及資訊管理組

     
    #41 EDB-EddieKwan, 2007-12-19
  2. 10260874

    ptm-csc
    Expand Collapse

    文章:
    10
    讚:
    0
    成功了,謝謝!
     
    #42 ptm-csc, 2007-12-19