支援團隊及大家同工 大家好 我一直用以下的抽取程式完成大榜工作及不同報表, 11月5日,3F15學生已被同事標離校但等候交FORM A 中, 同事在12月整合了的成績後抽取出現附圖(模擬圖)中的情況: 3F15學生姓名沒出現,但成績卻出現了-這班 16號生的卻配了這15號生的成績, 17號生就配了這16號生的成績......29號生(缺考的)就配了這28號生的成績....., 但到3H班又回復正常... 請問是否我們處理退學生及成績模組之間 忽略了哪些步驟或哪些程序,以致出現這次情況? (註.現在的抽取正常沒有闗問題) 瀏覽附件6473 select a.schyear '年度',a.regno '註冊編號',a.classcode '班別',a.classno '班號',a.chname '姓名',a1.sysscore as '中文', cast(floor(b1.sysscore * 10)/10 as int ) '閱讀',cast(floor(b2.sysscore * 10)/10 as int ) '寫作',cast(floor(b3.sysscore * 10)/10 as int ) '聆視',cast(floor(b4.sysscore * 10)/10 as int ) '說話',a2.sysscore as '英文',cast(floor(b5.sysscore * 10)/10 as int ) '讀本',cast(floor(b6.sysscore * 10)/10 as int ) '英聆',cast(floor(b7.sysscore * 10)/10 as int ) '英說',cast(floor(a3.sysscore * 10)/10 as int ) '數學',cast(floor(a4.sysscore * 10)/10 as int ) '常識',a5.sysgradeconvcompcode as '普話',a6.sysgradeconvcompcode as '電腦',a7.sysgradeconvcompcode as '視藝p1p4',a8.sysgradeconvcompcode as '音樂p1p4',a9.sysgradeconvcompcode as '體育', b.syspercscore '平均分', sum(i.sysscore * i1.value) '總分', g.overcondgradeconversioncompcode '操行', f.omclass '班名次', f.omclasslvl '級名次', isnull(o.ADJABSENTDAY, o.ABSENTDAY) '缺席', a.enname '英文名', a10.sysscore as '視藝p5p6', a11.sysscore as '音樂p5p6', a.sex as '性別' from vw_stu_lateststudent a left outer join tb_asr_studassessdata d1 on a.suid=d1.suid and a.stuid=d1.stuid and a.schyear=d1.schyear and a.schlvl=d1.schlevel and a.schsess=d1.schsession and a.classlvl=d1.classlevel left outer join tb_asr_subjassessdata a1 on a.suid=a1.suid and a.stuid=a1.stuid and a1.subjcode='080' and a.schyear=a1.schyear and a.schlvl=a1.schlevel and a.schsess=a1.schsession and a.classlvl=a1.classlevel and a1.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a2 on a.suid=a2.suid and a.stuid=a2.stuid and a2.subjcode='165' and a.schyear=a2.schyear and a.schlvl=a2.schlevel and a.schsess=a2.schsession and a.classlvl=a2.classlevel and a2.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a3 on a.suid=a3.suid and a.stuid=a3.stuid and a3.subjcode='280' and a.schyear=a3.schyear and a.schlvl=a3.schlevel and a.schsess=a3.schsession and a.classlvl=a3.classlevel and a3.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a4 on a.suid=a4.suid and a.stuid=a4.stuid and a4.subjcode='205' and a.schyear=a4.schyear and a.schlvl=a4.schlevel and a.schsess=a4.schsession and a.classlvl=a4.classlevel and a4.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a5 on a.suid=a5.suid and a.stuid=a5.stuid and a5.subjcode='350' and a.schyear=a5.schyear and a.schlvl=a5.schlevel and a.schsess=a5.schsession and a.classlvl=a5.classlevel and a5.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a6 on a.suid=a6.suid and a.stuid=a6.stuid and a6.subjcode='910' and a.schyear=a6.schyear and a.schlvl=a6.schlevel and a.schsess=a6.schsession and a.classlvl=a6.classlevel and a6.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a7 on a.suid=a7.suid and a.stuid=a7.stuid and a7.subjcode='432' and a.schyear=a7.schyear and a.schlvl=a7.schlevel and a.schsess=a7.schsession and a.classlvl=a7.classlevel and a7.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a8 on a.suid=a8.suid and a.stuid=a8.stuid and a8.subjcode='300' and a.schyear=a8.schyear and a.schlvl=a8.schlevel and a.schsess=a8.schsession and a.classlvl=a8.classlevel and a8.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a9 on a.suid=a9.suid and a.stuid=a9.stuid and a9.subjcode='310' and a.schyear=a9.schyear and a.schlvl=a9.schlevel and a.schsess=a9.schsession and a.classlvl=a9.classlevel and a9.timeseq=d1.timeseq left outer join TB_ASR_subjcomassessdata b1 on a.suid=b1.suid and a.stuid=b1.stuid and b1.subjcode='080' and b1.subjcompcode=01 and a.schyear=b1.schyear and a.schlvl=b1.schlevel and a.schsess=b1.schsession and a.classlvl=b1.classlevel and b1.timeseq=d1.timeseq left outer join TB_ASR_subjcomassessdata b2 on a.suid=b2.suid and a.stuid=b2.stuid and b2.subjcode='080' and b2.subjcompcode=02 and a.schyear=b2.schyear and a.schlvl=b2.schlevel and a.schsess=b2.schsession and a.classlvl=b2.classlevel and b2.timeseq=d1.timeseq left outer join TB_ASR_subjcomassessdata b3 on a.suid=b3.suid and a.stuid=b3.stuid and b3.subjcode='080' and b3.subjcompcode=03 and a.schyear=b3.schyear and a.schlvl=b3.schlevel and a.schsess=b3.schsession and a.classlvl=b3.classlevel and b3.timeseq=d1.timeseq left outer join TB_ASR_subjcomassessdata b4 on a.suid=b4.suid and a.stuid=b4.stuid and b4.subjcode='080' and b4.subjcompcode=06 and a.schyear=b4.schyear and a.schlvl=b4.schlevel and a.schsess=b4.schsession and a.classlvl=b4.classlevel and b4.timeseq=d1.timeseq left outer join TB_ASR_subjcomassessdata b5 on a.suid=b5.suid and a.stuid=b5.stuid and b5.subjcode='165' and b5.subjcompcode=01 and a.schyear=b5.schyear and a.schlvl=b5.schlevel and a.schsess=b5.schsession and a.classlvl=b5.classlevel and b5.timeseq=d1.timeseq left outer join TB_ASR_subjcomassessdata b6 on a.suid=b6.suid and a.stuid=b6.stuid and b6.subjcode='165' and b6.subjcompcode=02 and a.schyear=b6.schyear and a.schlvl=b6.schlevel and a.schsess=b6.schsession and a.classlvl=b6.classlevel and b6.timeseq=d1.timeseq left outer join TB_ASR_subjcomassessdata b7 on a.suid=b7.suid and a.stuid=b7.stuid and b7.subjcode='165' and b7.subjcompcode=03 and a.schyear=b7.schyear and a.schlvl=b7.schlevel and a.schsess=b7.schsession and a.classlvl=b7.classlevel and b7.timeseq=d1.timeseq left outer join tb_asr_studassessdata b on a.suid=b.suid and a.schlvl=b.schlevel and a.schsess=b.schsession and a.classlvl=b.classlevel and a.stuid=b.stuid and a.schyear=b.schyear and b.timeseq=d1.timeseq left outer join tb_asr_studassessdata f on a.suid=f.suid and a.schlvl=f.schlevel and a.schsess=f.schsession and a.classlvl=f.classlevel and a.stuid=f.stuid and a.schyear=f.schyear and f.timeseq=d1.timeseq left outer join tb_asr_studassessdata g on a.suid=g.suid and a.schlvl=g.schlevel and a.schsess=g.schsession and a.classlvl=g.classlevel and a.stuid=g.stuid and a.schyear=g.schyear and g.timeseq=d1.timeseq left outer join tb_asr_subjassessdata i on a.suid=i.suid and a.schlvl=i.schlevel and a.schsess=i.schsession and a.classlvl=i.classlevel and a.stuid=i.stuid and a.schyear=i.schyear and i.timeseq=d1.timeseq left outer join TB_ASR_WEIGHTING i1 on a.suid=i1.suid and a.schlvl=i1.schlevel and a.schsess=i1.schsession and a.classlvl=i1.classlevel and a.schyear=i1.schyear and i1.SUBJCODE = i.SUBJCODE and i1.SUBJCOMPCODE = null and i.moi = i1.moi and i1.timeseq=d1.timeseq and i1.weightingtype ='SUBJ_ASSESS_W_VER' left outer join TB_ASR_STUDMISCDATA o on o.suid=a.suid and o.stuid=a.stuid and o.schyear=a.schyear and o.schlevel=a.schlvl and o.schsession=a.schsess and o.classlevel=a.classlvl and o.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a10 on a.suid=a10.suid and a.stuid=a10.stuid and a10.subjcode='432' and a.schyear=a10.schyear and a.schlvl=a10.schlevel and a.schsess=a10.schsession and a.classlvl=a10.classlevel and a10.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a11 on a.suid=a11.suid and a.stuid=a11.stuid and a11.subjcode='300' and a.schyear=a11.schyear and a.schlvl=a11.schlevel and a.schsess=a11.schsession and a.classlvl=a11.classlevel and a11.timeseq=d1.timeseq where a.schyear=? and d1.timeseq=? group by a.schyear, a.regno, a.schlvl, a.schsess, a.classlvl, a.classcode,a.classno, a.enname, a.chname, a.sex, b.syspercscore, f.syspercscore,g.syspercscore, g.overcondgradeconversioncompcode, f.omclass,f.omclasslvl, b1.sysscore, a1.sysscore, b2.sysscore, b3.sysscore,b4.sysscore,a2.sysscore, b5.sysscore,b6.sysscore, b7.sysscore, a3.sysscore,a4.sysscore, a5.sysgradeconvcompcode, a6.sysgradeconvcompcode,a7.sysgradeconvcompcode, a8.sysgradeconvcompcode,a9.sysgradeconvcompcode, o.ABSENTDAY,o.ADJABSENTDAY,a10.sysscore,a11.sysscore order by a.classcode, a.classno, a.enname
請問第3個欄位是否中文科的分數?? 我查看過每個TABLE連結時, 都有用LEFT OUTER JOIN 已包括STUID, 所以沒有數據時應出現NULL, 而非上移資料。 我建議不使用GROUP BY, 而且刪除 Sum(i.sysscore * i1.value) '總分' 看看資料是否回復正常 如仍然有問題, 可以聯絡校主, 作進一步跟步, 謝謝