SQL SQL抽取資料錯誤

本文由 bmf-admin 在 2020-01-16 發表於 "WebSAMS 討論區" 討論區

  1. 121699

    bmf-admin
    Expand Collapse

    文章:
    45
    讚:
    0
    本人用以下SQL抽取優缺出勤操行評語總平均名次資料,
    但發現有以下問題︰
    1. 這SQL會把狀況為"不在學"的學生也抽出來;
    2. 如該班有狀況為"不在學"的學生,該班整班所有資料皆為0(即抽不到資料)。 懇請高手們幫忙修正。謝謝。


    優缺出勤操行評語總平均名次(按學期計算)

    select
    a.classcode '班別',
    a.classno '班號',
    a.regno '學生編號',
    a.enname '英文姓名',
    a.chname '中文姓名',
    b.syspercscore '平均分',
    b.omclass '班名次',
    b.omclasslvl '級名次',
    b.OVERCONDGRADECONVERSIONCOMPCODE '操行',


    string(isnull(o.adjlvl1merit, o.lvl1merit, 0)) '優點',
    string(isnull(o.adjlvl2merit, o.lvl2merit, 0)) '小功',
    string(isnull(o.adjlvl3merit, o.lvl3merit, 0)) '大功',
    string(isnull(o.adjlvl1demerit, o.lvl1demerit, 0)) '缺點',
    string(isnull(o.adjlvl2demerit, o.lvl2demerit, 0)) '小過',
    string(isnull(o.adjlvl3demerit, o.lvl3demerit, 0)) '大過',
    isnull(o.ADJABSENTDAY, o.ABSENTDAY) '缺席',
    isnull(o.ADJLATEDAY, o.LATEDAY) '遲到',

    (case when b.COMMENTDESCCHI <> '' then b.COMMENTDESCCHI else b.COMMENTDESCENG end) '整體評語'


    from vw_stu_lateststudent a
    left outer join tb_asr_studassessdata b
    on a.suid=b.suid and a.schlvl=b.schlevel and a.schsess=b.schsession and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=?

    left outer join tb_asr_studassessdata dc1
    on a.suid=dc1.suid and a.schlvl=dc1.schlevel and a.schsess=dc1.schsession and a.schyear=dc1.schyear and a.stuid=dc1.stuid and dc1.timeseq= b.timeseq

    left outer join TB_ASR_STUDMISCDATA o
    on o.suid=dc1.suid and o.stuid=dc1.stuid and
    o.schyear=a.schyear and o.schlevel=a.schlvl and
    o.schsession=a.schsess and o.classlevel=a.classlvl and
    o.timeseq= b.timeseq

    left outer join TB_STU_ANPSCHEMEDETAILMD m
    on m.SUID=a.SUID and m.SCHYEAR=a.SCHYEAR

    where a.schyear=? and a.classlvl=?
    order by a.classcode, a.classno, a.regno
     
    #1 bmf-admin, 2020-01-16
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    97
    讚:
    0
    你好,

    用 TB_STU_DEPARTURE 除去 "不在學" 的學生資料,請參考以下的SQL

    select
    a.classcode '班別',
    a.classno '班號',
    a.regno '學生編號',
    a.enname '英文姓名',
    a.chname '中文姓名',
    b.syspercscore '平均分',
    b.omclass '班名次',
    b.omclasslvl '級名次',
    b.OVERCONDGRADECONVERSIONCOMPCODE '操行',


    string(isnull(o.adjlvl1merit, o.lvl1merit, 0)) '優點',
    string(isnull(o.adjlvl2merit, o.lvl2merit, 0)) '小功',
    string(isnull(o.adjlvl3merit, o.lvl3merit, 0)) '大功',
    string(isnull(o.adjlvl1demerit, o.lvl1demerit, 0)) '缺點',
    string(isnull(o.adjlvl2demerit, o.lvl2demerit, 0)) '小過',
    string(isnull(o.adjlvl3demerit, o.lvl3demerit, 0)) '大過',
    isnull(o.ADJABSENTDAY, o.ABSENTDAY) '缺席',
    isnull(o.ADJLATEDAY, o.LATEDAY) '遲到',

    (case when b.COMMENTDESCCHI <> '' then b.COMMENTDESCCHI else b.COMMENTDESCENG end) '整體評語'


    from vw_stu_lateststudent a
    left outer join tb_asr_studassessdata b
    on a.suid=b.suid and a.schlvl=b.schlevel and a.schsess=b.schsession and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=?

    left outer join tb_asr_studassessdata dc1
    on a.suid=dc1.suid and a.schlvl=dc1.schlevel and a.schsess=dc1.schsession and a.schyear=dc1.schyear and a.stuid=dc1.stuid and dc1.timeseq= b.timeseq

    left outer join TB_ASR_STUDMISCDATA o
    on o.suid=dc1.suid and o.stuid=dc1.stuid and
    o.schyear=a.schyear and o.schlevel=a.schlvl and
    o.schsession=a.schsess and o.classlevel=a.classlvl and
    o.timeseq= b.timeseq

    left outer join TB_STU_ANPSCHEMEDETAILMD m
    on m.SUID=a.SUID and m.SCHYEAR=a.SCHYEAR

    where a.schyear=? and a.classlvl=?
    and a.stuid not in (select k.stuid from TB_STU_DEPARTURE k where k.stuid=a.stuid)
    order by a.classcode, a.classno, a.regno
     
    #2 edb-catherinewschan, 2020-01-21
  3. 58850605

    勞啟祥Lo Kai Cheung Job
    Expand Collapse

    文章:
    1
    讚:
    0
    and a.stuid not in (select k.stuid from TB_STU_DEPARTURE k where k.stuid=a.stuid)

    加入這句,可將退學的學生抽走,但發現重讀生也被抽走(因為學生之前有離校記錄,所以同樣被抽走),如希望保留重讀生資料,可以嗎?
     
    #3 勞啟祥Lo Kai Cheung Job, 2020-02-05
  4. 57873296

    edb-rn
    Expand Collapse

    文章:
    190
    讚:
    0
    如果重讀生有離校記錄,而不想被抽走。

    當中涉及SQL 繁複的修改,請老師聯絡所屬的網上校管系統小組的學校聯絡主任。謝謝!