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

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

  1. 55002479

    發奮
    Expand Collapse

    文章:
    72
    讚:
    0
    請問如何用SQL抽取學生不同學年某一次考試(如2006學年的T1及2005學年T1)的平均分及操行同列於一表內,依本學年學生就讀的級別顯示(顯示學生本學生就讀的班別,學號及中英文姓名,謝謝!
     
  2. 55002479

    發奮
    Expand Collapse

    文章:
    72
    讚:
    0
    對不起,如能於上表加上該次考試的級名次會更佳,謝謝!
     
  3. 55169025

    EDB-Chong
    Expand Collapse

    文章:
    0
    讚:
    0

    tr-howaikin,

    可參考以下SQL,並根據閣下需要而修改﹕

    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.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_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='350' 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='115' 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

    EMB-HMCHONG

     
    #3 EDB-Chong, 2007-05-07
  4. 55002479

    發奮
    Expand Collapse

    文章:
    72
    讚:
    0

    上面的SQL只能抽取某一學年某一考績的成績,如要同時顯示兩個不同的學年某一考績(如2006學年的T1及2005學年T1)的成績應如何處理?

    只需顯示平均分,級名次,操行及曾留級的學年就足夠,謝謝!

     
  5. 55169025

    EDB-Chong
    Expand Collapse

    文章:
    0
    讚:
    0

    tr-howaikin:

    請參考是否合用﹕

      c.classlvl '級別',
      s.classname '班別',
      c.classno '學號',
      c.enname '英文姓名',
      c.chname '中文姓名',
      d1.SYSPERCSCORE as '平均分 (1)', d1.OMCLASSLVL ,
    d1. OVERCONDGRADECONVERSIONCOMPCODE,
      d2.SYSPERCSCORE as '平均分 (2)', d2.OMCLASSLVL ,
    d2. OVERCONDGRADECONVERSIONCOMPCODE
      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 vw_stu_lateststudent c1
      on c1.suid=c.suid and c1.stuid=c.stuid and
         c1.SCHYEAR=?

    left outer join tb_asr_studassessdata d1
      on d1.suid=c1.suid and d1.stuid=c1.stuid and
         d1.schlevel=c1.schlvl and d1.schsession=c1.schsess and
         d1.classlevel=c1.classlvl and
         d1.SCHYEAR=c1.SCHYEAR and d1.TIMESEQ=?

    left outer join vw_stu_lateststudent c2
      on c2.suid=c.suid and c2.stuid=c.stuid and
         c2.SCHYEAR=?

    left outer join tb_asr_studassessdata d2
      on d2.suid=c2.suid and d2.stuid=c2.stuid and
         d2.schlevel=c2.schlvl and d2.schsession=c2.schsess and
         d2.classlevel=c2.classlvl and
         d2.SCHYEAR=c2.SCHYEAR and d2.TIMESEQ=?

    where c.SCHYEAR=?
    order by c.suid, c.schlvl, c.schsess, c.classlvl, c.classcode, c.classno

    EMB-HMCHONG

     
    #5 EDB-Chong, 2007-05-07
  6. 55038527

    RITA, SIU FUNG YING
    Expand Collapse

    文章:
    0
    讚:
    0

    我正需要這樣的sql呢,不好意思,如果想加入抽取中英文科分卷,如寫作、閱讀卷,及班名次,請問sql應怎樣修改? 謝謝指教!

     
    #6 RITA, SIU FUNG YING, 2007-05-07
  7. 55002479

    發奮
    Expand Collapse

    文章:
    72
    讚:
    0
    謝謝你的幫忙,但可否多加一欄顯示曾留級學生之留級年份,謝謝!
     
  8. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0
    <FONT color=#006600>淮山杞子君:<BR><BR>如需分卷成績,分卷代碼就要寫進 SQL 語句才行。<BR>請閣下提供學校中、英文科各分卷的代碼。<BR><BR>系統及資訊管理組</FONT>
     
    #8 EDB-EddieKwan, 2007-05-08
  9. 55002479

    發奮
    Expand Collapse

    文章:
    72
    讚:
    0

    如本校的分卷是:

    080 中國語文

    --- 02 中文閱讀

    --- 03 中文寫作

    --- 04 中文默書

    --- 06 中文聆聽

    --- 07 中文說話

    165 英國語文

    --- 02 英文默書

    --- 05 英文讀寫

    --- 06 英文聆聽

    --- 07 英文說話

    280 數學

     
  10. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0

    tr-howaikin君:

    加入留級年份的 SQL 語句如下:

    select
      c.classlvl '級別',
      s.classname '班別',
      c.classno '學號',
      c.enname '英文姓名',
      c.chname '中文姓名',
      d1.SYSPERCSCORE '平均分(1)',
      d1.OMCLASSLVL '班名次(1)',
      d1. OVERCONDGRADECONVERSIONCOMPCODE '操行(1)',
      d2.SYSPERCSCORE '平均分(2)',
      d2.OMCLASSLVL '班名次(2)',
      d2. OVERCONDGRADECONVERSIONCOMPCODE '操行(2)',

      (select list(v2.SCHYEAR)
       from TB_STU_STUSCHREC v1
       join TB_STU_STUSCHREC v2
         on v2.SUID=v1.SUID and v2.STUID=v1.STUID and
            v1.STATUS=1 and v2.SCHYEAR=v1.SCHYEAR+1
       where v1.SUID=c.SUID and v1.STUID=c.STUID) '留級年份'
     
      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 vw_stu_lateststudent c1
      on c1.suid=c.suid and c1.stuid=c.stuid and
         c1.SCHYEAR=?

    left outer join tb_asr_studassessdata d1
      on d1.suid=c1.suid and d1.stuid=c1.stuid and
         d1.schlevel=c1.schlvl and d1.schsession=c1.schsess and
         d1.classlevel=c1.classlvl and
         d1.SCHYEAR=c1.SCHYEAR and d1.TIMESEQ=?

    left outer join vw_stu_lateststudent c2
      on c2.suid=c.suid and c2.stuid=c.stuid and
         c2.SCHYEAR=?

    left outer join tb_asr_studassessdata d2
      on d2.suid=c2.suid and d2.stuid=c2.stuid and
         d2.schlevel=c2.schlvl and d2.schsession=c2.schsess and
         d2.classlevel=c2.classlvl and
         d2.SCHYEAR=c2.SCHYEAR and d2.TIMESEQ=?

    where c.SCHYEAR=?
    order by c.suid, c.schlvl, c.schsess, c.classlvl, c.classcode, c.classno

    系統及資訊管理組

     
    #10 EDB-EddieKwan, 2007-05-08
  11. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0

    tr-howaikin君:

    按貴校的設定,加入分卷成績的 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_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_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=d1.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=d1.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=d1.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=d1.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=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_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=d1.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=d1.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=d1.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=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='350' 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='115' 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


    系統及資訊管理組

     
    #11 EDB-EddieKwan, 2007-05-08
  12. 55002479

    發奮
    Expand Collapse

    文章:
    72
    讚:
    0
    如果要同時顯示兩個不同的學年某一考績(如2006學年的T1及2005學年T1)各主科(中英數)分卷的成績又如何?
     
  13. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0
    <FONT color=#006600>tr-howaikin君:<BR><BR>閣下只需將上述的 SQL 語句以不同參數執行一次,便能提取所需資料,對否?<BR><BR>系統及資訊管理組</FONT>
     
    #13 EDB-EddieKwan, 2007-05-08
  14. 55002479

    發奮
    Expand Collapse

    文章:
    72
    讚:
    0

    請問是哪些不同參數?

     
  15. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0
    <FONT color=#0000ff><FONT color=#006600>tr-howaikin君:<BR><BR>就是 SQL 裡的 </FONT>c.schyear, d1.timeseq&nbsp;<FONT color=#006600>和</FONT> c.classlvl</FONT><FONT color=#006600> 。<BR><BR>系統及資訊管理組</FONT><FONT color=#000000> </FONT>
     
    #15 EDB-EddieKwan, 2007-05-08
  16. 55002479

    發奮
    Expand Collapse

    文章:
    72
    讚:
    0
    謝謝!
     
  17. 55002479

    發奮
    Expand Collapse

    文章:
    72
    讚:
    0

    用以下的SQL顯示錯誤,是哪兒用錯語法?

    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 '常識',
      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=c1.SCHYEAR and
         s.SCHLEVEL=c.SCHLVL and s.SCHSESSION=c.SCHSESS and
         s.CLASSLEVEL=c1.CLASSLVL and s.CLASSCODE=c.CLASSCODE

    left outer join tb_asr_studassessdata d1
      on c.suid=d1.suid and c.stuid=d1.stuid and
         c1.schyear=d1.schyear and c.schlvl=d1.schlevel and
         c.schsess=d1.schsession and c1.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
         c1.schyear=a1.schyear and c.schlvl=a1.schlevel and
         c.schsess=a1.schsession and c1.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
         c1.schyear=a2.schyear and c.schlvl=a2.schlevel and
         c.schsess=a2.schsession and c1.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
         c1.schyear=a3.schyear and c.schlvl=a3.schlevel and
         c.schsess=a3.schsession and c1.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
         c1.schyear=a4.schyear and c.schlvl=a4.schlevel and
         c.schsess=a4.schsession and c1.classlvl=a4.classlevel and
         a4.timeseq=d1.timeseq

    left outer join tb_asr_studassessdata dc1
      on c.suid=dc1.suid and c.stuid=dc1.stuid and
         c1.schyear=dc1.schyear and c.schlvl=dc1.schlevel and
         c.schsess=dc1.schsession and c1.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=c1.schyear and o.schlevel=c.schlvl and
         o.schsession=c.schsess and o.classlevel=c1classlvl and
         o.timeseq=d1.timeseq


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

    left outer join TB_SCH_SCHCLASS s
      on s.SUID=c.SUID and s.SCHYEAR=c2.SCHYEAR and
         s.SCHLEVEL=c.SCHLVL and s.SCHSESSION=c.SCHSESS and
         s.CLASSLEVEL=c2.CLASSLVL and s.CLASSCODE=c.CLASSCODE

    left outer join tb_asr_studassessdata d1
      on c.suid=d1.suid and c.stuid=d1.stuid and
         c2.schyear=d1.schyear and c.schlvl=d1.schlevel and
         c.schsess=d1.schsession and c2.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
         c2.schyear=a1.schyear and c.schlvl=a1.schlevel and
         c.schsess=a1.schsession and c2.classlvl=a1.classlevel and
         a1.timeseq=d2.timeseq
    left outer join tb_asr_subjassessdata a2
      on c.suid=a2.suid and c.stuid=a2.stuid and a2.subjcode='165' and
         c2.schyear=a2.schyear and c.schlvl=a2.schlevel and
         c.schsess=a2.schsession and c2.classlvl=a2.classlevel and
         a2.timeseq=d2.timeseq
    left outer join tb_asr_subjassessdata a3
      on c.suid=a3.suid and c.stuid=a3.stuid and a3.subjcode='280' and
         c2.schyear=a3.schyear and c.schlvl=a3.schlevel and
         c.schsess=a3.schsession and c2.classlvl=a3.classlevel and
         a3.timeseq=d2.timeseq

    left outer join tb_asr_subjassessdata a4
      on c.suid=a4.suid and c.stuid=a4.stuid and a4.subjcode='205' and
         c2.schyear=a4.schyear and c.schlvl=a4.schlevel and
         c.schsess=a4.schsession and c2.classlvl=a4.classlevel and
         a4.timeseq=d2.timeseq

    left outer join tb_asr_studassessdata dc1
      on c.suid=dc1.suid and c.stuid=dc1.stuid and
         c2.schyear=dc1.schyear and c.schlvl=dc1.schlevel and
         c.schsess=dc1.schsession and c2.classlvl=dc1.classlevel and
         dc1.timeseq=d2.timeseq

    left outer join TB_ASR_STUDMISCDATA o
      on o.suid=c.suid and o.stuid=c.stuid and
         o.schyear=c2.schyear and o.schlevel=c.schlvl and
         o.schsession=c.schsess and o.classlevel=c2.classlvl and
         o.timeseq=d2.timeseq


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

     
  18. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0

    tr-howaikin 君:

    初步見到以下問題。
    (1) where 句子不應插在 left join 之前。
    (2) alias 不應重複使用。

    建議閣下可以找一些有關 SQL 的初階參考書看看,便能略知一般語法。


    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 '常識',
      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=c1.SCHYEAR and
         s.SCHLEVEL=c.SCHLVL and s.SCHSESSION=c.SCHSESS and
         s.CLASSLEVEL=c1.CLASSLVL and s.CLASSCODE=c.CLASSCODE

    left outer join tb_asr_studassessdata d1
      on c.suid=d1.suid and c.stuid=d1.stuid and
         c1.schyear=d1.schyear and c.schlvl=d1.schlevel and
         c.schsess=d1.schsession and c1.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
         c1.schyear=a1.schyear and c.schlvl=a1.schlevel and
         c.schsess=a1.schsession and c1.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
         c1.schyear=a2.schyear and c.schlvl=a2.schlevel and
         c.schsess=a2.schsession and c1.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
         c1.schyear=a3.schyear and c.schlvl=a3.schlevel and
         c.schsess=a3.schsession and c1.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
         c1.schyear=a4.schyear and c.schlvl=a4.schlevel and
         c.schsess=a4.schsession and c1.classlvl=a4.classlevel and
         a4.timeseq=d1.timeseq

    left outer join tb_asr_studassessdata dc1
      on c.suid=dc1.suid and c.stuid=dc1.stuid and
         c1.schyear=dc1.schyear and c.schlvl=dc1.schlevel and
         c.schsess=dc1.schsession and c1.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=c1.schyear and o.schlevel=c.schlvl and
         o.schsession=c.schsess and o.classlevel=c1classlvl and
         o.timeseq=d1.timeseq


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

    left outer join TB_SCH_SCHCLASS s
      on s.SUID=c.SUID and s.SCHYEAR=c2.SCHYEAR and
         s.SCHLEVEL=c.SCHLVL and s.SCHSESSION=c.SCHSESS and
         s.CLASSLEVEL=c2.CLASSLVL and s.CLASSCODE=c.CLASSCODE

    left outer join tb_asr_studassessdata d1
      on c.suid=d1.suid and c.stuid=d1.stuid and
         c2.schyear=d1.schyear and c.schlvl=d1.schlevel and
         c.schsess=d1.schsession and c2.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
         c2.schyear=a1.schyear and c.schlvl=a1.schlevel and
         c.schsess=a1.schsession and c2.classlvl=a1.classlevel and
         a1.timeseq=d2.timeseq
    left outer join tb_asr_subjassessdata a2
      on c.suid=a2.suid and c.stuid=a2.stuid and a2.subjcode='165' and
         c2.schyear=a2.schyear and c.schlvl=a2.schlevel and
         c.schsess=a2.schsession and c2.classlvl=a2.classlevel and
         a2.timeseq=d2.timeseq
    left outer join tb_asr_subjassessdata a3
      on c.suid=a3.suid and c.stuid=a3.stuid and a3.subjcode='280' and
         c2.schyear=a3.schyear and c.schlvl=a3.schlevel and
         c.schsess=a3.schsession and c2.classlvl=a3.classlevel and
         a3.timeseq=d2.timeseq

    left outer join tb_asr_subjassessdata a4
      on c.suid=a4.suid and c.stuid=a4.stuid and a4.subjcode='205' and
         c2.schyear=a4.schyear and c.schlvl=a4.schlevel and
         c.schsess=a4.schsession and c2.classlvl=a4.classlevel and
         a4.timeseq=d2.timeseq

    left outer join tb_asr_studassessdata dc1
      on c.suid=dc1.suid and c.stuid=dc1.stuid and
         c2.schyear=dc1.schyear and c.schlvl=dc1.schlevel and
         c.schsess=dc1.schsession and c2.classlvl=dc1.classlevel and
         dc1.timeseq=d2.timeseq

    left outer join TB_ASR_STUDMISCDATA o
      on o.suid=c.suid and o.stuid=c.stuid and
         o.schyear=c2.schyear and o.schlevel=c.schlvl and
         o.schsession=c.schsess and o.classlevel=c2.classlvl and
         o.timeseq=d2.timeseq


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

    系統及資訊管理組

     
    #18 EDB-EddieKwan, 2007-05-09
  19. 55002479

    發奮
    Expand Collapse

    文章:
    72
    讚:
    0
    仍是出現"<SPAN class=Acknowledge>E-16053 : SQL語句不正確",請指教.</SPAN>
     
  20. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0
    <FONT color=#006600>tr-howaikin 君:<BR><BR>閣下請提供所執行的 SQL 語句。<BR></FONT><BR><FONT color=#006600>系統及資訊管理組</FONT><FONT color=#000000> </FONT>
     
    #20 EDB-EddieKwan, 2007-05-09