SQL 請教以SQL 輸出學生中文科及英文科細卷分數及輸出其他考績操行內的紀律、盡責、禮貌、整潔.

本文由 tr-mussochan 在 2022-03-08 發表於 "WebSAMS 討論區" 討論區

  1. 57307132

    tr-mussochan
    Expand Collapse

    文章:
    4
    讚:
    0
    你好,請問可否協助在這個SQL再加上資料,包括(學生中文科及英文科細卷的分數)
    中文科細卷包括:語文、默書、寫作、聆聽、說話
    英文科細卷包括:語文、默書、說話、聆聽

    及輸出其他考績操行內的紀律、盡責、禮貌、整潔),

    謝謝你們﹗感激不盡。

    select c.classlvl as '班級', s.classname as '班別', c.classno as '班號', c.chname as '中文姓名',c.enname as '英文姓名', a1.sysscore as '中文', a2.sysscore as '英文', a3.sysscore as '數學', a4.sysscore as '常識', a5.sysscore as '音樂', a6.sysscore as '視藝', a7.SYSGRADECONVCOMPCODE as '體育', a8.sysscore as '宗教', a9.SYSGRADECONVCOMPCODE as '電腦', d1.syspercscore as '平均分',d1.omclasslvl as '級名次', d1.omclass as '班名次', dc1.overcondgradeconversioncompcode as '操行', o.ABSENTDAY as '缺席日數', d1.COMMENTDESCCHI '整體評語' from vw_stu_lateststudent c left outer join TB_SCH_SCHCLASS s on s.SUID=c.SUID and s.SCHYEAR=c.SCHYEAR and s.SCHLEVEL=c.SCHLVL and s.SCHSESSION=c.SCHSESS and s.CLASSLEVEL=c.CLASSLVL and s.CLASSCODE=c.CLASSCODE left outer join tb_asr_studassessdata d1 on c.suid=d1.suid and c.stuid=d1.stuid and c.schyear=d1.schyear and c.schlvl=d1.schlevel and c.schsess=d1.schsession and c.classlvl=d1.classlevel left outer join tb_asr_subjassessdata a1 on c.suid=a1.suid and c.stuid=a1.stuid and a1.subjcode='080' and c.schyear=a1.schyear and c.schlvl=a1.schlevel and c.schsess=a1.schsession and c.classlvl=a1.classlevel and a1.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a2 on c.suid=a2.suid and c.stuid=a2.stuid and a2.subjcode='165' and c.schyear=a2.schyear and c.schlvl=a2.schlevel and c.schsess=a2.schsession and c.classlvl=a2.classlevel and a2.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a3 on c.suid=a3.suid and c.stuid=a3.stuid and a3.subjcode='280' and c.schyear=a3.schyear and c.schlvl=a3.schlevel and c.schsess=a3.schsession and c.classlvl=a3.classlevel and a3.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a4 on c.suid=a4.suid and c.stuid=a4.stuid and a4.subjcode='205' and c.schyear=a4.schyear and c.schlvl=a4.schlevel and c.schsess=a4.schsession and c.classlvl=a4.classlevel and a4.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a5 on c.suid=a5.suid and c.stuid=a5.stuid and a5.subjcode='300' and c.schyear=a5.schyear and c.schlvl=a3.schlevel and c.schsess=a5.schsession and c.classlvl=a5.classlevel and a5.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a6 on c.suid=a6.suid and c.stuid=a6.stuid and a6.subjcode='432' and c.schyear=a6.schyear and c.schlvl=a6.schlevel and c.schsess=a6.schsession and c.classlvl=a6.classlevel and a6.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a7 on c.suid=a3.suid and c.stuid=a7.stuid and a7.subjcode='310' and c.schyear=a7.schyear and c.schlvl=a7.schlevel and c.schsess=a7.schsession and c.classlvl=a7.classlevel and a7.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a8 on c.suid=a8.suid and c.stuid=a8.stuid and a8.subjcode='185' and c.schyear=a8.schyear and c.schlvl=a8.schlevel and c.schsess=a8.schsession and c.classlvl=a8.classlevel and a8.timeseq=d1.timeseq left outer join tb_asr_subjassessdata a9 on c.suid=a9.suid and c.stuid=a9.stuid and a9.subjcode='991' and c.schyear=a9.schyear and c.schlvl=a9.schlevel and c.schsess=a9.schsession and c.classlvl=a9.classlevel and a9.timeseq=d1.timeseq left outer join tb_asr_studassessdata dc1 on c.suid=dc1.suid and c.stuid=dc1.stuid and c.schyear=dc1.schyear and c.schlvl=dc1.schlevel and c.schsess=dc1.schsession and c.classlvl=dc1.classlevel and dc1.timeseq=d1.timeseq left outer join TB_ASR_STUDMISCDATA o on o.suid=c.suid and o.stuid=c.stuid and o.schyear=c.schyear and o.schlevel=c.schlvl and o.schsession=c.schsess and o.classlevel=c.classlvl and o.timeseq=d1.timeseq where c.schyear=? and d1.timeseq=? and c.classlvl=? order by c.classcode, c.classno
     
    #1 tr-mussochan, 2022-03-08
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    231
    讚:
    0
    你好,

    都嘗試按你要求加插了中英文的分卷 (紅藍色部份) 和操行的分卷 (綠色部份) , 但是WEBSAMS對SQL有字數限制, 不能超過8000字元包括空位。

    請自行減省不必要的部份, 例如刪除操行的分卷, 就把綠色部份刪除就可以了。

    select
    c.classlvl as '班級',
    s.classname as '班別',
    c.classno as '班號',
    c.chname as '中文姓名',
    c.enname as '英文姓名',
    a1.sysscore as '中文',
    a1a.sysscore as '中文分卷-語文',
    a1b.sysscore as '中文分卷-默書',
    a1c.sysscore as '中文分卷-寫作',
    a1d.sysscore as '中文分卷-聆聽',
    a1e.sysscore as '中文分卷-說話',

    a2.sysscore as '英文',
    a2a.sysscore as '英文分卷-語文',
    a2b.sysscore as '英文分卷-默書',
    a2c.sysscore as '英文分卷-說話',
    a2d.sysscore as '英文分卷-聆聽',

    a3.sysscore as '數學',
    a4.sysscore as '常識',
    a5.sysscore as '音樂',
    a6.sysscore as '視藝',
    a7.SYSGRADECONVCOMPCODE as '體育',
    a8.sysscore as '宗教',
    a9.SYSGRADECONVCOMPCODE as '電腦',
    d1.syspercscore as '平均分',
    d1.omclasslvl as '級名次',
    d1.omclass as '班名次',
    dc1.overcondgradeconversioncompcode as '操行',
    cond1.gradeconversioncompcode as '紀律',
    cond2.gradeconversioncompcode as '盡責',
    cond3.gradeconversioncompcode as '禮貌',
    cond4.gradeconversioncompcode as '整潔',

    o.ABSENTDAY as '缺席日數',
    d1.COMMENTDESCCHI '整體評語'
    from wsadmin.vw_stu_lateststudent c
    left outer join wsadmin.TB_SCH_SCHCLASS s on s.SUID=c.SUID and s.SCHYEAR=c.SCHYEAR and s.SCHLEVEL=c.SCHLVL and s.SCHSESSION=c.SCHSESS and s.CLASSLEVEL=c.CLASSLVL and s.CLASSCODE=c.CLASSCODE
    left outer join wsadmin.tb_asr_studassessdata d1 on c.suid=d1.suid and c.stuid=d1.stuid and c.schyear=d1.schyear and c.schlvl=d1.schlevel and c.schsess=d1.schsession and c.classlvl=d1.classlevel --and c.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjassessdata a1 on c.suid=a1.suid and c.stuid=a1.stuid and a1.subjcode='080' and c.schyear=a1.schyear and c.schlvl=a1.schlevel and c.schsess=a1.schsession and c.classlvl=a1.classlevel and a1.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjcomassessdata a1a on a1.suid=a1a.suid and a1.stuid=a1a.stuid and a1a.subjcode='080' and a1a.subjcompcode='01' and a1.schyear=a1a.schyear and a1.schlevel=a1a.schlevel and a1.schsession=a1a.schsession
    and a1.classlevel=a1a.classlevel and a1.timeseq=a1a.timeseq
    left outer join wsadmin.tb_asr_subjcomassessdata a1b on a1.suid=a1b.suid and a1.stuid=a1b.stuid and a1b.subjcode='080' and a1b.subjcompcode='02' and a1.schyear=a1b.schyear and a1.schlevel=a1b.schlevel and a1.schsession=a1b.schsession
    and a1.classlevel=a1b.classlevel and a1.timeseq=a1b.timeseq
    left outer join wsadmin.tb_asr_subjcomassessdata a1c on a1.suid=a1c.suid and a1.stuid=a1c.stuid and a1c.subjcode='080' and a1c.subjcompcode='03' and a1.schyear=a1c.schyear and a1.schlevel=a1c.schlevel and a1.schsession=a1c.schsession
    and a1.classlevel=a1c.classlevel and a1.timeseq=a1c.timeseq
    left outer join wsadmin.tb_asr_subjcomassessdata a1d on a1.suid=a1d.suid and a1.stuid=a1d.stuid and a1d.subjcode='080' and a1d.subjcompcode='04' and a1.schyear=a1d.schyear and a1.schlevel=a1d.schlevel and a1.schsession=a1d.schsession
    and a1.classlevel=a1d.classlevel and a1.timeseq=a1d.timeseq
    left outer join wsadmin.tb_asr_subjcomassessdata a1e on a1.suid=a1e.suid and a1.stuid=a1e.stuid and a1e.subjcode='080' and a1e.subjcompcode='05' and a1.schyear=a1e.schyear and a1.schlevel=a1e.schlevel and a1.schsession=a1e.schsession
    and a1.classlevel=a1e.classlevel and a1.timeseq=a1e.timeseq

    left outer join wsadmin.tb_asr_subjassessdata a2 on c.suid=a2.suid and c.stuid=a2.stuid and a2.subjcode='165' and c.schyear=a2.schyear and c.schlvl=a2.schlevel and c.schsess=a2.schsession and c.classlvl=a2.classlevel and a2.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjcomassessdata a2a on a2.suid=a2a.suid and a2.stuid=a2a.stuid and a2a.subjcode='165' and a2a.subjcompcode='01' and a2.schyear=a2a.schyear and a2.schlevel=a2a.schlevel and a2.schsession=a2a.schsession
    and a2.classlevel=a2a.classlevel and a2.timeseq=a2a.timeseq
    left outer join wsadmin.tb_asr_subjcomassessdata a2b on a2.suid=a2b.suid and a2.stuid=a2b.stuid and a2b.subjcode='165' and a2b.subjcompcode='02' and a2.schyear=a2b.schyear and a2.schlevel=a2b.schlevel and a2.schsession=a2b.schsession
    and a2.classlevel=a2b.classlevel and a2.timeseq=a2b.timeseq
    left outer join wsadmin.tb_asr_subjcomassessdata a2c on a2.suid=a2c.suid and a2.stuid=a2c.stuid and a2c.subjcode='165' and a2c.subjcompcode='03' and a2.schyear=a2c.schyear and a2.schlevel=a2c.schlevel and a2.schsession=a2c.schsession
    and a2.classlevel=a2c.classlevel and a2.timeseq=a2c.timeseq
    left outer join wsadmin.tb_asr_subjcomassessdata a2d on a2.suid=a2d.suid and a2.stuid=a2d.stuid and a2d.subjcode='165' and a2d.subjcompcode='04' and a2.schyear=a2d.schyear and a2.schlevel=a2d.schlevel and a2.schsession=a2d.schsession
    and a2.classlevel=a2d.classlevel and a2.timeseq=a2d.timeseq

    left outer join wsadmin.tb_asr_subjassessdata a3 on c.suid=a3.suid and c.stuid=a3.stuid and a3.subjcode='280' and c.schyear=a3.schyear and c.schlvl=a3.schlevel and c.schsess=a3.schsession and c.classlvl=a3.classlevel and a3.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjassessdata a4 on c.suid=a4.suid and c.stuid=a4.stuid and a4.subjcode='205' and c.schyear=a4.schyear and c.schlvl=a4.schlevel and c.schsess=a4.schsession and c.classlvl=a4.classlevel and a4.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjassessdata a5 on c.suid=a5.suid and c.stuid=a5.stuid and a5.subjcode='300' and c.schyear=a5.schyear and c.schlvl=a3.schlevel and c.schsess=a5.schsession and c.classlvl=a5.classlevel and a5.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjassessdata a6 on c.suid=a6.suid and c.stuid=a6.stuid and a6.subjcode='432' and c.schyear=a6.schyear and c.schlvl=a6.schlevel and c.schsess=a6.schsession and c.classlvl=a6.classlevel and a6.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjassessdata a7 on c.suid=a3.suid and c.stuid=a7.stuid and a7.subjcode='310' and c.schyear=a7.schyear and c.schlvl=a7.schlevel and c.schsess=a7.schsession and c.classlvl=a7.classlevel and a7.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjassessdata a8 on c.suid=a8.suid and c.stuid=a8.stuid and a8.subjcode='185' and c.schyear=a8.schyear and c.schlvl=a8.schlevel and c.schsess=a8.schsession and c.classlvl=a8.classlevel and a8.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_subjassessdata a9 on c.suid=a9.suid and c.stuid=a9.stuid and a9.subjcode='991' and c.schyear=a9.schyear and c.schlvl=a9.schlevel and c.schsess=a9.schsession and c.classlvl=a9.classlevel and a9.timeseq=d1.timeseq
    left outer join wsadmin.tb_asr_studassessdata dc1 on c.suid=dc1.suid and c.stuid=dc1.stuid and c.schyear=dc1.schyear and c.schlvl=dc1.schlevel and c.schsess=dc1.schsession and c.classlvl=dc1.classlevel and dc1.timeseq=d1.timeseq
    left outer join wsadmin.TB_ASR_STUDMISCDATA o on o.suid=c.suid and o.stuid=c.stuid and o.schyear=c.schyear and o.schlevel=c.schlvl and o.schsession=c.schsess and o.classlevel=c.classlvl and o.timeseq=d1.timeseq
    left outer join (select cd1.suid, cd1.schlevel, cd1.schsession, cd1.classlevel, cd1.schyear, cd1.stuid, cd1.timeseq, cd2.namechi, cd1.gradeconversioncompcode
    from wsadmin.tb_asr_studcondcompdata cd1
    join wsadmin.tb_asr_condcomp cd2
    on cd1.suid=cd2.suid and cd1.schlevel=cd2.schlevel and cd1.schsession=cd2.schsession and cd1.schyear=cd2.schyear and cd1.condcompseq=cd2.condcompseq and trim(cd2.namechi)='紀律') cond1
    on d1.suid=cond1.suid and d1.schlevel=cond1.schlevel and d1.schsession=cond1.schsession and d1.schyear=cond1.schyear and d1.stuid=cond1.stuid and d1.timeseq=cond1.timeseq
    left outer join (select cd1.suid, cd1.schlevel, cd1.schsession, cd1.classlevel, cd1.schyear, cd1.stuid, cd1.timeseq, cd2.namechi, cd1.gradeconversioncompcode
    from wsadmin.tb_asr_studcondcompdata cd1
    join wsadmin.tb_asr_condcomp cd2
    on cd1.suid=cd2.suid and cd1.schlevel=cd2.schlevel and cd1.schsession=cd2.schsession and cd1.schyear=cd2.schyear and cd1.condcompseq=cd2.condcompseq and trim(cd2.namechi)='盡責') cond2
    on d1.suid=cond2.suid and d1.schlevel=cond2.schlevel and d1.schsession=cond2.schsession and d1.schyear=cond2.schyear and d1.stuid=cond2.stuid and d1.timeseq=cond2.timeseq
    left outer join (select cd1.suid, cd1.schlevel, cd1.schsession, cd1.classlevel, cd1.schyear, cd1.stuid, cd1.timeseq, cd2.namechi, cd1.gradeconversioncompcode
    from wsadmin.tb_asr_studcondcompdata cd1
    join wsadmin.tb_asr_condcomp cd2
    on cd1.suid=cd2.suid and cd1.schlevel=cd2.schlevel and cd1.schsession=cd2.schsession and cd1.schyear=cd2.schyear and cd1.condcompseq=cd2.condcompseq and trim(cd2.namechi)='禮貌') cond3
    on d1.suid=cond3.suid and d1.schlevel=cond3.schlevel and d1.schsession=cond3.schsession and d1.schyear=cond3.schyear and d1.stuid=cond3.stuid and d1.timeseq=cond3.timeseq
    left outer join (select cd1.suid, cd1.schlevel, cd1.schsession, cd1.classlevel, cd1.schyear, cd1.stuid, cd1.timeseq, cd2.namechi, cd1.gradeconversioncompcode
    from wsadmin.tb_asr_studcondcompdata cd1
    join wsadmin.tb_asr_condcomp cd2
    on cd1.suid=cd2.suid and cd1.schlevel=cd2.schlevel and cd1.schsession=cd2.schsession and cd1.schyear=cd2.schyear and cd1.condcompseq=cd2.condcompseq and trim(cd2.namechi)='整潔') cond4
    on d1.suid=cond4.suid and d1.schlevel=cond4.schlevel and d1.schsession=cond4.schsession and d1.schyear=cond4.schyear and d1.stuid=cond4.stuid and d1.timeseq=cond4.timeseq

    where c.schyear=? and d1.timeseq=? and c.classlvl=?
    order by c.classcode, c.classno
     
    #2 edb-catherinewschan, 2022-03-09
    Last edited: 2022-03-09