修改SQL 分卷分數 及分卷名次 過住三年記錄比較 select a.CLASSCODE '班別', a.CLASSNO '班號', a.CHNAME '中文姓名', a.ENNAME '英文姓名', c.en_des 'Subject', c.ch_des '科目', a.SCHYEAR '學年', a.classcode '今年班別', e.sysscore '今年T3成績', f.sysscore '今年T2成績', g.sysscore '今年T1成績', h.classcode '1年前班別', i.sysscore '1年前T3成績', j.sysscore '1年前T2成績', k.sysscore '1年前T1成績', l.classcode '2年前班別', m.sysscore '2年前T3成績', n.sysscore '2年前T2成績', o.sysscore '2年前T1成績' from wsadmin.VW_STU_LATESTSTUDENT a left outer join wsadmin.TB_ASR_SUBJASSESSDATA 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.TIMESEQ = 1300 and e.subjcode=? left outer join wsadmin.TB_ASR_SUBJASSESSDATA f on a.SUID = f.SUID and a.SCHLVL = f.SCHLEVEL and a.SCHSESS = f.SCHSESSION and a.SCHYEAR = f.SCHYEAR and a.STUID = f.STUID and f.TIMESEQ = 1200 and f.subjcode=e.subjcode left outer join wsadmin.TB_ASR_SUBJASSESSDATA g on a.SUID = g.SUID and a.SCHLVL = g.SCHLEVEL and a.SCHSESS = g.SCHSESSION and a.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and g.TIMESEQ = 1100 and g.subjcode=e.subjcode left outer join wsadmin.VW_STU_LATESTSTUDENT h on a.SUID = h.SUID and a.SCHLVL = h.SCHLVL and a.SCHSESS = h.SCHSESS and a.SCHYEAR-1 = h.SCHYEAR and a.STUID = h.STUID left outer join wsadmin.TB_ASR_SUBJASSESSDATA i on a.SUID = i.SUID and a.SCHLVL = i.SCHLEVEL and a.SCHSESS = i.SCHSESSION and a.SCHYEAR-1 = i.SCHYEAR and a.STUID = i.STUID and i.TIMESEQ = 1300 and i.subjcode=e.subjcode left outer join wsadmin.TB_ASR_SUBJASSESSDATA j on a.SUID = j.SUID and a.SCHLVL = j.SCHLEVEL and a.SCHSESS = j.SCHSESSION and a.SCHYEAR-1 = j.SCHYEAR and a.STUID = j.STUID and j.TIMESEQ = 1200 and j.subjcode=e.subjcode left outer join wsadmin.TB_ASR_SUBJASSESSDATA k on a.SUID = k.SUID and a.SCHLVL = k.SCHLEVEL and a.SCHSESS = k.SCHSESSION and a.SCHYEAR-1 = k.SCHYEAR and a.STUID = k.STUID and k.TIMESEQ = 1100 and k.subjcode=e.subjcode left outer join wsadmin.VW_STU_LATESTSTUDENT l on a.SUID = l.SUID and a.SCHLVL = l.SCHLVL and a.SCHSESS = l.SCHSESS and a.SCHYEAR-2 = l.SCHYEAR and a.STUID = l.STUID left outer join wsadmin.TB_ASR_SUBJASSESSDATA m on a.SUID = m.SUID and a.SCHLVL = m.SCHLEVEL and a.SCHSESS = m.SCHSESSION and a.SCHYEAR-2 = m.SCHYEAR and a.STUID = m.STUID and m.TIMESEQ = 1300 and m.subjcode=e.subjcode left outer join wsadmin.TB_ASR_SUBJASSESSDATA n on a.SUID = n.SUID and a.SCHLVL = n.SCHLEVEL and a.SCHSESS = n.SCHSESSION and a.SCHYEAR-2 = n.SCHYEAR and a.STUID = n.STUID and n.TIMESEQ = 1200 and n.subjcode=e.subjcode left outer join wsadmin.TB_ASR_SUBJASSESSDATA o on a.SUID = o.SUID and a.SCHLVL = o.SCHLEVEL and a.SCHSESS = o.SCHSESSION and a.SCHYEAR-2 = o.SCHYEAR and a.STUID = o.STUID and o.TIMESEQ = 1100 and o.subjcode=e.subjcode left outer join tb_hse_common c on a.suid=c.suid and e.subjcode=c.code_id and c.tb_id='SBJ' where a.SCHYEAR=? and a.classlvl=? order by a.CLASSCODE,a.CLASSNO 想咁改. .唔知可唔可以呢？ select a.REGNO ‘學生註冊編號’ a.CLASSCODE '班別', a.CLASSNO '班號', a.CHNAME '中文姓名', a.ENNAME '英文姓名', a.SCHYEAR '學年', a.classcode '今年班別', g. SYSGRADECONVCOMPCOD '今年T1成績中文13分卷分數', g. omclasslvl '今年T1成績中文13分卷名次 h.classcode '1年前班別', k. SYSGRADECONVCOMPCOD '1年前T1成績中文13分卷分數', k. omclasslvl '1年前T1成績中文13分卷名次' l.classcode '2年前班別', o. SYSGRADECONVCOMPCOD '2年前T1成績中文13分卷分數' o. omclasslvl '2年前T1成績中文13分卷名次
你好 由於分卷是用TB_ASR_SUBJCOMASSESSDATA，所以一律取代TB_ASR_SUBJASSESSDATA。 另外，由於你只抽取T1成績，所以把不用的連結刪除，即是所以抽取T2和T3的連結。 我再優化SQL，令到任由用戶輸入級別、科目、科目分卷、年份和考績的資料。自動抽取資料，而不用更改SQL語句。 可以參考以下的SQL select a.REGNO '學生註冊編號', a.CLASSCODE '班別', a.CLASSNO '班號', a.CHNAME '中文姓名', a.ENNAME '英文姓名', a.SCHYEAR '學年', c.en_des 'Subject', c.ch_des '分卷科目', a.classcode '今年班別', g.SYSGRADECONVCOMPCODE '今年T1成績中文13分卷分數', g.omclasslvl '今年T1成績中文13分卷名次', h.classcode '1年前班別', k.SYSGRADECONVCOMPCODE '1年前T1成績中文13分卷分數', k.omclasslvl '1年前T1成績中文13分卷名次', l.classcode '2年前班別', o.SYSGRADECONVCOMPCODE '2年前T1成績中文13分卷分數', o.omclasslvl '2年前T1成績中文13分卷名次' from wsadmin.VW_STU_LATESTSTUDENT a left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA g on a.SUID = g.SUID and a.SCHLVL = g.SCHLEVEL and a.SCHSESS = g.SCHSESSION and a.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and g.TIMESEQ = ? and g.subjcode=? and g.subjcompcode=? left outer join wsadmin.VW_STU_LATESTSTUDENT h on a.SUID = h.SUID and a.SCHLVL = h.SCHLVL and a.SCHSESS = h.SCHSESS and a.SCHYEAR-1 = h.SCHYEAR and a.STUID = h.STUID left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA k on a.SUID = k.SUID and a.SCHLVL = k.SCHLEVEL and a.SCHSESS = k.SCHSESSION and a.SCHYEAR-1 = k.SCHYEAR and a.STUID = k.STUID and k.TIMESEQ = g.TIMESEQ and k.subjcode=g.subjcode and k.subjcompcode=g.subjcompcode left outer join wsadmin.VW_STU_LATESTSTUDENT l on a.SUID = l.SUID and a.SCHLVL = l.SCHLVL and a.SCHSESS = l.SCHSESS and a.SCHYEAR-2 = l.SCHYEAR and a.STUID = l.STUID left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA o on a.SUID = o.SUID and a.SCHLVL = o.SCHLEVEL and a.SCHSESS = o.SCHSESSION and a.SCHYEAR-2 = o.SCHYEAR and a.STUID = o.STUID and o.TIMESEQ = g.TIMESEQ and o.subjcode=g.subjcode and o.subjcompcode=g.subjcompcode left outer join wsadmin.TB_HSE_SBJCMP c on a.suid=c.suid and g.subjcode=c.sbj_code and g.subjcompcode=c.code_id and c.tb_id='SBJCMP' where a.SCHYEAR=? and a.classlvl=? order by a.CLASSCODE,a.CLASSNO