敝校(小學)需要抽取IT, PE, VA, 和中英文科部份分卷, 參考了常用SQL參考庫, 寫了以下SQL, 但很奇怪, 我可以抽出去年, 前年....的成績, 但不能抽出本年度的成績 (已入分, 能抽出姓名班別學號), 可否幫忙查看以下的 SQL? 謝謝. select b.SCHYEAR '年度', d.TIMESEQ '考試', b.CLASSCODE '班別', b.CLASSNO '班號', a.CHNAME '中文姓名', d1.sysscore '說話＿', d2.sysscore '默書＿', d3.SYSGRADECONVCOMPCODE '書法＿', e1.sysscore 'Oral＿', e2.sysscore 'Dict＿', e4.SYSGRADECONVCOMPCODE 'Pen＿', f.SYSGRADECONVCOMPCODE '體育＿', g.SYSGRADECONVCOMPCODE '視藝＿', h.SYSGRADECONVCOMPCODE 'I.T.＿' from wsadmin.TB_STU_STUDENT a join wsadmin.VW_STU_LATESTSTUSCHREC b on a.SUID = b.SUID and a.STUID = b.STUID and b.SCHYEAR =? and b.CLASScode = ? left outer join wsadmin.TB_ASR_SUBJASSESSDATA d on a.SUID = d.SUID and a.SCHLVL = d.SCHLEVEL and a.SCHSESS = d.SCHSESSION and b.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 b.SCHYEAR = d1.SCHYEAR and a.STUID = d1.STUID and d.SUBJCODE = d1.SUBJCODE and d1.SUBJCOMPCODE = '04' 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 b.SCHYEAR = d2.SCHYEAR and a.STUID = d2.STUID and d.SUBJCODE = d2.SUBJCODE and d2.SUBJCOMPCODE = '05' and d.TIMESEQ = d2.TIMESEQ left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d3 on a.SUID = d3.SUID and A.SCHLVL = d3.SCHLEVEL and a.SCHSESS = d3.SCHSESSION and b.SCHYEAR = d3.SCHYEAR and a.STUID = d3.STUID and d.SUBJCODE = d3.SUBJCODE and d3.SUBJCOMPCODE = '06' and d.TIMESEQ = d3.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 b.SCHYEAR = d5.SCHYEAR and a.STUID = d5.STUID and d.SUBJCODE = d5.SUBJCODE and d5.SUBJCOMPCODE = '07' and d.TIMESEQ = d5.TIMESEQ left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d6 on a.SUID = d6.SUID and A.SCHLVL = d6.SCHLEVEL and a.SCHSESS = d6.SCHSESSION and b.SCHYEAR = d6.SCHYEAR and a.STUID = d6.STUID and d.SUBJCODE = d6.SUBJCODE and d6.SUBJCOMPCODE = '08' and d.TIMESEQ = d6.TIMESEQ left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA d7 on a.SUID = d7.SUID and A.SCHLVL = d7.SCHLEVEL and a.SCHSESS = d7.SCHSESSION and b.SCHYEAR = d7.SCHYEAR and a.STUID = d7.STUID and d.SUBJCODE = d7.SUBJCODE and d7.SUBJCOMPCODE = '09' and d.TIMESEQ = d7.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 b.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.SUBJCODE = '165' and d.TIMESEQ = e.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 b.SCHYEAR = e1.SCHYEAR and a.STUID = e1.STUID and e.SUBJCODE = e1.SUBJCODE and e1.SUBJCOMPCODE = '04' 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 b.SCHYEAR = e2.SCHYEAR and a.STUID = e2.STUID and e.SUBJCODE = e2.SUBJCODE and e2.SUBJCOMPCODE = '05' 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 b.SCHYEAR = e4.SCHYEAR and a.STUID = e4.STUID and e.SUBJCODE = e4.SUBJCODE and e4.SUBJCOMPCODE = '06' and d.TIMESEQ = e4.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 b.SCHYEAR = f.SCHYEAR and a.STUID = f.STUID and f.SUBJCODE = '310' and d.TIMESEQ = f.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 b.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and g.SUBJCODE = '432' and d.TIMESEQ = g.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 b.SCHYEAR = h.SCHYEAR and a.STUID = h.STUID and h.SUBJCODE = '906' and d.TIMESEQ = h.TIMESEQ order by b.SCHLVL, b.SCHSESS, b.CLASSLVL, b.CLASSCODE, b.CLASSNO
你好, 因為第一個JOIN TABLE的RESULT要有VALUE才可以JOIN到其他的TABLE 根據這句 d.SUBJCODE = '080' and d.TIMESEQ = ? , '080' 是中文科, 而TIMESEQ是學期考績, 如果你輸入1101, 那麼中文科的考績1101, 有沒有入齊分數??