你好, 現有SQL如下 想加入中英文分卷成績,謝謝 英文 reading: code 165 subject components:00 writing: code 165 subject components:11 listening&Integrated Skill :code 165 subject components:12 Speaking : code165 subject components:15 中文 reading: code 080 subject components:11 writing: code 080 subject components:12 IIntegrated Skill :code 080 subject components:19 Speaking : code080 subject components:20 select a.regno '學生編號', a.chname '學生姓名', a.classcode '班別', a.classno '學號', d.sysscore '中文', e.sysscore '英文', f.sysscore '數學', g.sysscore '通識', h.sysscore '生物', i.sysscore '化學', j.sysscore '中史', n.sysscore 'BAFS', l.sysscore '經濟', o.sysscore '地理', p.sysscore '歷史', q.sysscore 'M2', r.sysscore '物理', s.sysscore '旅遊', t.sysscore 'ICT', u.sysscore '視藝' from vw_stu_lateststudent a left outer join wsadmin.TB_ASR_SUBJASSESSDATA 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 = '080' and d.TIMESEQ = ? 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.SUBJCODE = '165' and e.TIMESEQ = d.TIMESEQ 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.SUBJCODE = '22S' and f.TIMESEQ = d.TIMESEQ 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.SUBJCODE = '265' and g.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA h on a.SUID = h.SUID and a.SCHLVL = h.SCHLEVEL and a.SCHSESS = h.SCHSESSION and a.SCHYEAR = h.SCHYEAR and a.STUID = h.STUID and h.SUBJCODE = '045' and h.TIMESEQ = d.TIMESEQ 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 = i.SCHYEAR and a.STUID = i.STUID and i.SUBJCODE = '070' and i.TIMESEQ = d.TIMESEQ 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 = j.SCHYEAR and a.STUID = j.STUID and j.SUBJCODE = '075' and j.TIMESEQ = d.TIMESEQ 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 = n.SCHYEAR and a.STUID = n.STUID and n.SUBJCODE = '11N' and n.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA l on a.SUID = l.SUID and a.SCHLVL = l.SCHLEVEL and a.SCHSESS = l.SCHSESSION and a.SCHYEAR = l.SCHYEAR and a.STUID = l.STUID and l.SUBJCODE = '135' and l.TIMESEQ = d.TIMESEQ 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 = o.SCHYEAR and a.STUID = o.STUID and o.SUBJCODE = '210' and o.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA p on a.SUID = p.SUID and a.SCHLVL = p.SCHLEVEL and a.SCHSESS = p.SCHSESSION and a.SCHYEAR = p.SCHYEAR and a.STUID = p.STUID and p.SUBJCODE = '235' and p.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA q on a.SUID = q.SUID and a.SCHLVL = q.SCHLEVEL and a.SCHSESS = q.SCHSESSION and a.SCHYEAR = q.SCHYEAR and a.STUID = q.STUID and q.SUBJCODE = '24S' and q.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA r on a.SUID = r.SUID and a.SCHLVL = r.SCHLEVEL and a.SCHSESS = r.SCHSESSION and a.SCHYEAR = r.SCHYEAR and a.STUID = r.STUID and r.SUBJCODE = '315' and r.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA s on a.SUID = s.SUID and a.SCHLVL = s.SCHLEVEL and a.SCHSESS = s.SCHSESSION and a.SCHYEAR = s.SCHYEAR and a.STUID = s.STUID and s.SUBJCODE = '71S' and s.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA t on a.SUID = t.SUID and a.SCHLVL = t.SCHLEVEL and a.SCHSESS = t.SCHSESSION and a.SCHYEAR = t.SCHYEAR and a.STUID = t.STUID and t.SUBJCODE = '81N' and t.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA u on a.SUID = u.SUID and a.SCHLVL = u.SCHLEVEL and a.SCHSESS = u.SCHSESSION and a.SCHYEAR = u.SCHYEAR and a.STUID = u.STUID and u.SUBJCODE = '83S' and u.TIMESEQ = d.TIMESEQ where a.schyear= ? and a.classlvl= ? order by a.CLASSLVL, a.CLASSCODE, a.CLASSNO
感謝 已解決 我成功改了 CODE 如下 select a.regno '學生編號', a.chname '學生姓名', a.classcode '班別', a.classno '學號', d.sysscore '中文', d1.sysscore 'CP1', d2.sysscore 'CP2', d4.sysscore 'CP3', d5.sysscore 'CP4', e.sysscore '英文', e1.sysscore 'READ', e2.sysscore 'WRIT', e4.sysscore 'L/IN', e5.sysscore 'SP/S', f.sysscore '數學', g.sysscore '通識', h.sysscore '生物', i.sysscore '化學', j.sysscore '中史', n.sysscore 'BAFS', l.sysscore '經濟', o.sysscore '地理', p.sysscore '歷史', q.sysscore 'M2', r.sysscore '物理', s.sysscore '旅遊', t.sysscore 'ICT', u.sysscore '視藝' from vw_stu_lateststudent a left outer join wsadmin.TB_ASR_SUBJASSESSDATA 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 = '080' and d.TIMESEQ = ? left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d1 on a.SUID = d1.SUID and A.SCHLVL = d1.SCHLEVEL and a.SCHSESS = d1.SCHSESSION and a.SCHYEAR = d1.SCHYEAR and a.STUID = d1.STUID and d.SUBJCODE = d1.SUBJCODE and d1.SUBJCOMPCODE = '11' and d.TIMESEQ = d1.TIMESEQ left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d2 on a.SUID = d2.SUID and A.SCHLVL = d2.SCHLEVEL and a.SCHSESS = d2.SCHSESSION and a.SCHYEAR = d2.SCHYEAR and a.STUID = d2.STUID and d.SUBJCODE = d2.SUBJCODE and d2.SUBJCOMPCODE = '12' and d.TIMESEQ = d2.TIMESEQ left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d4 on a.SUID = d4.SUID and A.SCHLVL = d4.SCHLEVEL and a.SCHSESS = d4.SCHSESSION and a.SCHYEAR = d4.SCHYEAR and a.STUID = d4.STUID and d.SUBJCODE = d4.SUBJCODE and d4.SUBJCOMPCODE = '19' and d.TIMESEQ = d4.TIMESEQ left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d5 on a.SUID = d5.SUID and A.SCHLVL = d5.SCHLEVEL and a.SCHSESS = d5.SCHSESSION and a.SCHYEAR = d5.SCHYEAR and a.STUID = d5.STUID and d.SUBJCODE = d5.SUBJCODE and d5.SUBJCOMPCODE = '20' and d.TIMESEQ = d5.TIMESEQ 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.SUBJCODE = '165' and e.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e1 on a.SUID = e1.SUID and A.SCHLVL = e1.SCHLEVEL and a.SCHSESS = e1.SCHSESSION and a.SCHYEAR = e1.SCHYEAR and a.STUID = e1.STUID and e.SUBJCODE = e1.SUBJCODE and e1.SUBJCOMPCODE = '00' and d.TIMESEQ = e1.TIMESEQ left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e2 on a.SUID = e2.SUID and A.SCHLVL = e2.SCHLEVEL and a.SCHSESS = e2.SCHSESSION and a.SCHYEAR = e2.SCHYEAR and a.STUID = e2.STUID and e.SUBJCODE = e2.SUBJCODE and e2.SUBJCOMPCODE = '11' and d.TIMESEQ = e2.TIMESEQ left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e4 on a.SUID = e4.SUID and A.SCHLVL = e4.SCHLEVEL and a.SCHSESS = e4.SCHSESSION and a.SCHYEAR = e4.SCHYEAR and a.STUID = e4.STUID and e.SUBJCODE = e4.SUBJCODE and e4.SUBJCOMPCODE = '12' and d.TIMESEQ = e4.TIMESEQ left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e5 on a.SUID = e5.SUID and A.SCHLVL = e5.SCHLEVEL and a.SCHSESS = e5.SCHSESSION and a.SCHYEAR = e5.SCHYEAR and a.STUID = e5.STUID and e.SUBJCODE = e5.SUBJCODE and e5.SUBJCOMPCODE = '15' and d.TIMESEQ = e5.TIMESEQ 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.SUBJCODE = '22S' and f.TIMESEQ = d.TIMESEQ 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.SUBJCODE = '265' and g.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA h on a.SUID = h.SUID and a.SCHLVL = h.SCHLEVEL and a.SCHSESS = h.SCHSESSION and a.SCHYEAR = h.SCHYEAR and a.STUID = h.STUID and h.SUBJCODE = '045' and h.TIMESEQ = d.TIMESEQ 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 = i.SCHYEAR and a.STUID = i.STUID and i.SUBJCODE = '070' and i.TIMESEQ = d.TIMESEQ 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 = j.SCHYEAR and a.STUID = j.STUID and j.SUBJCODE = '075' and j.TIMESEQ = d.TIMESEQ 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 = n.SCHYEAR and a.STUID = n.STUID and n.SUBJCODE = '11N' and n.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA l on a.SUID = l.SUID and a.SCHLVL = l.SCHLEVEL and a.SCHSESS = l.SCHSESSION and a.SCHYEAR = l.SCHYEAR and a.STUID = l.STUID and l.SUBJCODE = '135' and l.TIMESEQ = d.TIMESEQ 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 = o.SCHYEAR and a.STUID = o.STUID and o.SUBJCODE = '210' and o.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA p on a.SUID = p.SUID and a.SCHLVL = p.SCHLEVEL and a.SCHSESS = p.SCHSESSION and a.SCHYEAR = p.SCHYEAR and a.STUID = p.STUID and p.SUBJCODE = '235' and p.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA q on a.SUID = q.SUID and a.SCHLVL = q.SCHLEVEL and a.SCHSESS = q.SCHSESSION and a.SCHYEAR = q.SCHYEAR and a.STUID = q.STUID and q.SUBJCODE = '24S' and q.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA r on a.SUID = r.SUID and a.SCHLVL = r.SCHLEVEL and a.SCHSESS = r.SCHSESSION and a.SCHYEAR = r.SCHYEAR and a.STUID = r.STUID and r.SUBJCODE = '315' and r.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA s on a.SUID = s.SUID and a.SCHLVL = s.SCHLEVEL and a.SCHSESS = s.SCHSESSION and a.SCHYEAR = s.SCHYEAR and a.STUID = s.STUID and s.SUBJCODE = '71S' and s.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA t on a.SUID = t.SUID and a.SCHLVL = t.SCHLEVEL and a.SCHSESS = t.SCHSESSION and a.SCHYEAR = t.SCHYEAR and a.STUID = t.STUID and t.SUBJCODE = '81N' and t.TIMESEQ = d.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA u on a.SUID = u.SUID and a.SCHLVL = u.SCHLEVEL and a.SCHSESS = u.SCHSESSION and a.SCHYEAR = u.SCHYEAR and a.STUID = u.STUID and u.SUBJCODE = '83S' and u.TIMESEQ = d.TIMESEQ where a.schyear= ? and a.classlvl= ? order by a.CLASSLVL, a.CLASSCODE, a.CLASSNO