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

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

 

對不起,如能於上表加上該次考試的級名次會更佳,謝謝!

 

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

 

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

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

 

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

 

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

 

謝謝你的幫忙,但可否多加一欄顯示曾留級學生之留級年份,謝謝!

 

<FONT color=#006600>淮山杞子君：<BR><BR>如需分卷成績，分卷代碼就要寫進 SQL 語句才行。<BR>請閣下提供學校中、英文科各分卷的代碼。<BR><BR>系統及資訊管理組</FONT>

 

如本校的分卷是:

080 中國語文

--- 02 中文閱讀

--- 03 中文寫作

--- 04 中文默書

--- 06 中文聆聽

--- 07 中文說話

165 英國語文

--- 02 英文默書

--- 05 英文讀寫

--- 06 英文聆聽

--- 07 英文說話

280 數學

 

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

系統及資訊管理組

 

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

系統及資訊管理組

 

如果要同時顯示兩個不同的學年某一考績(如2006學年的T1及2005學年T1)各主科(中英數)分卷的成績又如何?

 

<FONT color=#006600>tr-howaikin君：<BR><BR>閣下只需將上述的 SQL 語句以不同參數執行一次，便能提取所需資料，對否？<BR><BR>系統及資訊管理組</FONT>

 

請問是哪些不同參數?

 

<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>

 

謝謝!

 

用以下的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

 

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

系統及資訊管理組

 

仍是出現"<SPAN class=Acknowledge>E-16053 ： SQL語句不正確",請指教.</SPAN>

 

<FONT color=#006600>tr-howaikin 君：<BR><BR>閣下請提供所執行的 SQL 語句。<BR></FONT><BR><FONT color=#006600>系統及資訊管理組</FONT><FONT color=#000000> </FONT>