SQL 抽取出現異常...

本文由 tr-MCC 在 2021-01-21 發表於 "WebSAMS 討論區" 討論區

  1. 55780289

    tr-MCC
    Expand Collapse

    文章:
    30
    讚:
    0
    2020-DEC.jpg 支援團隊及大家同工

    大家好

    我一直用以下的抽取程式完成大榜工作及不同報表,

    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
     
    #1 tr-MCC, 2021-01-21
    Last edited: 2021-01-22
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    191
    讚:
    0
    請問第3個欄位是否中文科的分數??

    我查看過每個TABLE連結時, 都有用LEFT OUTER JOIN 已包括STUID, 所以沒有數據時應出現NULL, 而非上移資料。

    我建議不使用GROUP BY, 而且刪除
    Sum(i.sysscore * i1.value) '總分'
    看看資料是否回復正常

    如仍然有問題, 可以聯絡校主, 作進一步跟步, 謝謝
     
    #2 edb-catherinewschan, 2021-01-24