老師請參考以下的 SQL 語句。 select a.classcode, a.classno, a.chname, a.enname, x1.en_des 'block 1', x2.en_des 'block 2', x3.en_des 'block 3' from vw_stu_lateststudent a left outer join ( select x.suid, x.schlevel, x.schsession, x.schyear, w.stuid, w.stuschrecid, z.ch_des, z.en_des, y.divsubjcode, b.exdrptype from tb_stu_stusubj w join tb_sch_divsubjgrp x on w.crosssubjgrp=x.subjgrpcode and w.subjcode=x.subjcode join tb_sch_dsgsubj y on x.suid=y.suid and x.schlevel=y.schlevel and x.schsession=y.schsession and x.schyear=y.schyear and x.subjgrpcode=y.subjgrpcode and w.crosssubjcode=y.divsubjcode left outer join tb_asr_subjassessdata b on x.suid=b.suid and x.schyear=b.schyear and w.stuid=b.stuid and w.subjcode = b.subjcode and w.moi = b.moi and b.timeseq=? join tb_hse_common z on w.suid=z.suid and w.subjcode=z.code_id and z.tb_id='SBJ' where x.grptype='B' and y.subjblkcode='Block_1' ) x1 on a.suid=x1.suid and a.schlvl=x1.schlevel and a.schsess=x1.schsession and a.schyear=x1.schyear and a.stuid=x1.stuid and a.stuschrecid=x1.stuschrecid left outer join ( select x.suid, x.schlevel, x.schsession, x.schyear, w.stuid, w.stuschrecid, z.ch_des, z.en_des, y.divsubjcode, b.exdrptype from tb_stu_stusubj w join tb_sch_divsubjgrp x on w.crosssubjgrp=x.subjgrpcode and w.subjcode=x.subjcode join tb_sch_dsgsubj y on x.suid=y.suid and x.schlevel=y.schlevel and x.schsession=y.schsession and x.schyear=y.schyear and x.subjgrpcode=y.subjgrpcode and w.crosssubjcode=y.divsubjcode left outer join tb_asr_subjassessdata b on x.suid=b.suid and x.schyear=b.schyear and w.stuid=b.stuid and w.subjcode = b.subjcode and w.moi = b.moi and b.timeseq=? join tb_hse_common z on w.suid=z.suid and w.subjcode=z.code_id and z.tb_id='SBJ' where x.grptype='B' and y.subjblkcode='Block_2' ) x2 on a.suid=x2.suid and a.schlvl=x2.schlevel and a.schsess=x2.schsession and a.schyear=x2.schyear and a.stuid=x2.stuid and a.stuschrecid=x2.stuschrecid left outer join ( select x.suid, x.schlevel, x.schsession, x.schyear, w.stuid, w.stuschrecid, z.ch_des, z.en_des, y.divsubjcode, b.exdrptype from tb_stu_stusubj w join tb_sch_divsubjgrp x on w.crosssubjgrp=x.subjgrpcode and w.subjcode=x.subjcode join tb_sch_dsgsubj y on x.suid=y.suid and x.schlevel=y.schlevel and x.schsession=y.schsession and x.schyear=y.schyear and x.subjgrpcode=y.subjgrpcode and w.crosssubjcode=y.divsubjcode left outer join tb_asr_subjassessdata b on x.suid=b.suid and x.schyear=b.schyear and w.stuid=b.stuid and w.subjcode = b.subjcode and w.moi = b.moi and b.timeseq=? join tb_hse_common z on w.suid=z.suid and w.subjcode=z.code_id and z.tb_id='SBJ' where x.grptype='B' and y.subjblkcode='Block_3' ) x3 on a.suid=x3.suid and a.schlvl=x3.schlevel and a.schsess=x3.schsession and a.schyear=x3.schyear and a.stuid=x3.stuid and a.stuschrecid=x3.stuschrecid where a.schyear=? and a.classlvl=? and (a.status=null or a.status not in (4,5,6)) order by a.classlvl, a.classcode, a.classno, a.chname, a.enname
老師請參考以下的 SQL 語句。 select a.classcode, a.classno, a.chname, a.enname, x1.en_des 'block 1', x2.en_des 'block 2', x3.en_des 'block 3' from vw_stu_lateststudent a left outer join ( select x.suid, x.schlevel, x.schsession, x.schyear, w.stuid, w.stuschrecid, z.ch_des, z.en_des, y.divsubjcode from tb_stu_stusubj w join tb_sch_divsubjgrp x on w.crosssubjgrp=x.subjgrpcode and w.subjcode=x.subjcode join tb_sch_dsgsubj y on x.suid=y.suid and x.schlevel=y.schlevel and x.schsession=y.schsession and x.schyear=y.schyear and x.subjgrpcode=y.subjgrpcode and w.crosssubjcode=y.divsubjcode join tb_hse_common z on w.suid=z.suid and w.subjcode=z.code_id and z.tb_id='SBJ' where x.grptype='B' and y.subjblkcode='Block_1' ) x1 on a.suid=x1.suid and a.schlvl=x1.schlevel and a.schsess=x1.schsession and a.schyear=x1.schyear and a.stuid=x1.stuid and a.stuschrecid=x1.stuschrecid left outer join ( select x.suid, x.schlevel, x.schsession, x.schyear, w.stuid, w.stuschrecid, z.ch_des, z.en_des, y.divsubjcode from tb_stu_stusubj w join tb_sch_divsubjgrp x on w.crosssubjgrp=x.subjgrpcode and w.subjcode=x.subjcode join tb_sch_dsgsubj y on x.suid=y.suid and x.schlevel=y.schlevel and x.schsession=y.schsession and x.schyear=y.schyear and x.subjgrpcode=y.subjgrpcode and w.crosssubjcode=y.divsubjcode join tb_hse_common z on w.suid=z.suid and w.subjcode=z.code_id and z.tb_id='SBJ' where x.grptype='B' and y.subjblkcode='Block_2' ) x2 on a.suid=x2.suid and a.schlvl=x2.schlevel and a.schsess=x2.schsession and a.schyear=x2.schyear and a.stuid=x2.stuid and a.stuschrecid=x2.stuschrecid left outer join ( select x.suid, x.schlevel, x.schsession, x.schyear, w.stuid, w.stuschrecid, z.ch_des, z.en_des, y.divsubjcode from tb_stu_stusubj w join tb_sch_divsubjgrp x on w.crosssubjgrp=x.subjgrpcode and w.subjcode=x.subjcode join tb_sch_dsgsubj y on x.suid=y.suid and x.schlevel=y.schlevel and x.schsession=y.schsession and x.schyear=y.schyear and x.subjgrpcode=y.subjgrpcode and w.crosssubjcode=y.divsubjcode join tb_hse_common z on w.suid=z.suid and w.subjcode=z.code_id and z.tb_id='SBJ' where x.grptype='B' and y.subjblkcode='Block_3' ) x3 on a.suid=x3.suid and a.schlvl=x3.schlevel and a.schsess=x3.schsession and a.schyear=x3.schyear and a.stuid=x3.stuid and a.stuschrecid=x3.stuschrecid where a.schyear=? and a.classlvl=? and (a.status=null or a.status not in (4,5,6)) order by a.classlvl, a.classcode, a.classno, a.chname, a.enname
做不到老師要求的格式, SQL 並不支援 variable column。不過老師可以參考以下的 SQL 語句 for non-block subjects。 select a.classlvl 'ClassLevel', a.classcode 'Class', a.classno 'ClassNo', a.chname 'StudentName_Chi', a.enname 'StudentName_Eng', b.ch_des 'SubjectName_Chi', b.en_des 'SubjectName_Eng', ccsg.subjgrpdesc 'CrossClassSubjectGroupName', ccsg.subjgrpcode 'CrossClassSubjectGroupCode', (case when ccsg.grptype='B' then 'Subject Block' else 'Non-Subject Block' end) 'SubjectBlock', ccsg1.divsubjcode 'CrossClassSubjectSubGroupCode', smoi.ch_des 'MOI', ccsg1.subjblkcode 'SubjectBlockCode', ccsg2d.ch_des 'CrossClassSubjectSubjectComponent_Chi', ccsg2d.en_des 'CrossClassSubjectSubjectComponent_Eng' from vw_stu_lateststudent a join tb_stu_stusubj a1 on a.suid=a1.suid and a.stuid=a1.stuid and a.stuschrecid=a1.stuschrecid left outer join tb_sch_divsubjgrp ccsg on a.suid=ccsg.suid and a.schlvl=ccsg.schlevel and a.schsess=ccsg.schsession and a.schyear=ccsg.schyear and a1.crosssubjgrp=ccsg.subjgrpcode left outer join tb_sch_dsgsubj ccsg1 on a.suid=ccsg1.suid and a.schlvl=ccsg1.schlevel and a.schsess=ccsg1.schsession and a.schyear=ccsg1.schyear and ccsg.subjgrpcode=ccsg1.subjgrpcode and a1.crosssubjcode=ccsg1.divsubjcode left outer join tb_sch_dsgsubjcomp ccsg2 on a.suid=ccsg2.suid and a.schlvl=ccsg2.schlevel and a.schsess=ccsg2.schsession and a.schyear=ccsg2.schyear and ccsg.subjgrpcode=ccsg2.subjgrpcode and a1.crosssubjcode=ccsg2.divsubjcode left outer join tb_hse_common smoi on a.suid=smoi.suid and a1.moi=smoi.code_id and smoi.tb_id='MOI' left outer join tb_hse_common b on a.suid=b.suid and a1.subjcode=b.code_id and b.tb_id='SBJ' left outer join tb_hse_sbjcmp ccsg2d on a.suid=ccsg2d.suid and ccsg.subjcode=ccsg2d.sbj_code and ccsg2.subjcompcode=ccsg2d.code_id where a.schyear=? and a1.clssubjtype='X' and a.status=null and a.classlvl =? group by a.schlvl, a.schsess, a.classlvl, a.classcode, a.classno, a.enname, a.chname, a1.clssubjtype, b.ch_des, b.en_des, ccsg.subjgrpdesc, ccsg.subjgrpcode, ccsg.grptype, ccsg1.divsubjcode, smoi.ch_des, ccsg1.subjblkcode, ccsg2d.ch_des, ccsg2d.en_des order by ccsg.subjgrpcode, ccsg1.divsubjcode, a.classlvl, a.classcode, a.classno, a.chname, b.ch_des, b.en_des, ccsg2d.ch_des, ccsg2d.en_des
SQL 可顯示非科目集, 但有以下問題: 1. 同一科目小組學生出現 了數次, listening, speaking....同一學生出現了四次 2. 雖然不支援 multi-column, 但可否以班及班號作基礎, 瀏覽附件a.png
老師請參考以下SQL: select a.classlvl 'ClassLevel', a.classcode 'Class', a.classno 'ClassNo', a.chname 'StudentName_Chi', a.enname 'StudentName_Eng', b.ch_des 'SubjectName_Chi', b.en_des 'SubjectName_Eng', ccsg.subjgrpdesc 'CrossClassSubjectGroupName', ccsg.subjgrpcode 'CrossClassSubjectGroupCode', (case when ccsg.grptype='B' then 'Subject Block' else 'Non-Subject Block' end) 'SubjectBlock', ccsg1.divsubjcode 'CrossClassSubjectSubGroupCode', smoi.ch_des 'MOI', ccsg1.subjblkcode 'SubjectBlockCode', ccsg1.divsubjcode, ccsg1t.STAFFSEQ, st.SHORTNAME 'Teacher ShortName', ccsg2d.ch_des 'CrossClassSubjectSubjectComponent_Chi', ccsg2d.en_des 'CrossClassSubjectSubjectComponent_Eng' from vw_stu_lateststudent a join tb_stu_stusubj a1 on a.suid=a1.suid and a.stuid=a1.stuid and a.stuschrecid=a1.stuschrecid left outer join tb_sch_divsubjgrp ccsg on a.suid=ccsg.suid and a.schlvl=ccsg.schlevel and a.schsess=ccsg.schsession and a.schyear=ccsg.schyear and a1.crosssubjgrp=ccsg.subjgrpcode left outer join tb_sch_dsgsubj ccsg1 on a.suid=ccsg1.suid and a.schlvl=ccsg1.schlevel and a.schsess=ccsg1.schsession and a.schyear=ccsg1.schyear and ccsg.subjgrpcode=ccsg1.subjgrpcode and a1.crosssubjcode=ccsg1.divsubjcode left outer join TB_SCH_DSGSUBJTCHR ccsg1t on a.suid=ccsg1t.suid and a.schlvl=ccsg1t.schlevel and a.schsess=ccsg1t.schsession and a.schyear=ccsg1t.schyear and ccsg.subjgrpcode=ccsg1t.subjgrpcode and a1.crosssubjcode=ccsg1t.divsubjcode left outer join VW_ASR_STAFF st on a.suid=st.suid and ccsg1t.STAFFCODE = st.STAFFCODE left outer join tb_sch_dsgsubjcomp ccsg2 on a.suid=ccsg2.suid and a.schlvl=ccsg2.schlevel and a.schsess=ccsg2.schsession and a.schyear=ccsg2.schyear and ccsg.subjgrpcode=ccsg2.subjgrpcode and a1.crosssubjcode=ccsg2.divsubjcode left outer join tb_hse_common smoi on a.suid=smoi.suid and a1.moi=smoi.code_id and smoi.tb_id='MOI' left outer join tb_hse_common b on a.suid=b.suid and a1.subjcode=b.code_id and b.tb_id='SBJ' left outer join tb_hse_sbjcmp ccsg2d on a.suid=ccsg2d.suid and ccsg.subjcode=ccsg2d.sbj_code and ccsg2.subjcompcode=ccsg2d.code_id where a.schyear=? and a1.clssubjtype='X' and a.status=null and a.classlvl =? group by a.schlvl, a.schsess, a.classlvl, a.classcode, a.classno, a.enname, a.chname, a1.clssubjtype, b.ch_des, b.en_des, ccsg.subjgrpdesc, ccsg.subjgrpcode, ccsg.grptype, ccsg1.divsubjcode,ccsg1t.STAFFSEQ,st.SHORTNAME,smoi.ch_des, ccsg1.subjblkcode, ccsg2d.ch_des, ccsg2d.en_des order by ccsg.subjgrpcode, ccsg1.divsubjcode, a.classlvl, a.classcode, a.classno, a.chname, b.ch_des, b.en_des, ccsg2d.ch_des, ccsg2d.en_des
老師請參考以下SQL,如仍未能提取老師所需,請聯絡貴校的網上校管系統學校聯絡主任以便跟進: select a.classcode, a.classno, a.chname, a.enname, x1.en_des 'block 1', x1.divsubjcode 'block 1 divsubjcode', x1.SHORTNAME 'block 1 Teacher SHORTNAME', x2.en_des 'block 2', x2.divsubjcode 'block 2 divsubjcode', x2.SHORTNAME 'block 2 Teacher SHORTNAME', x3.en_des 'block 3', x3.divsubjcode 'block 3 divsubjcode', x3.SHORTNAME 'block 3 Teacher SHORTNAME' from vw_stu_lateststudent a left outer join ( select x.suid, x.schlevel, x.schsession, x.schyear, w.stuid, w.stuschrecid, z.ch_des, z.en_des, y.divsubjcode,st.SHORTNAME from tb_stu_stusubj w join tb_sch_divsubjgrp x on w.crosssubjgrp=x.subjgrpcode and w.subjcode=x.subjcode join tb_sch_dsgsubj y on x.suid=y.suid and x.schlevel=y.schlevel and x.schsession=y.schsession and x.schyear=y.schyear and x.subjgrpcode=y.subjgrpcode and w.crosssubjcode=y.divsubjcode left outer join TB_SCH_DSGSUBJTCHR y1 on x.suid=y1.suid and x.schlevel=y1.schlevel and x.schsession=y1.schsession and x.schyear=y1.schyear and x.subjgrpcode=y1.subjgrpcode and w.crosssubjcode=y1.divsubjcode and y1.STAFFSEQ=1 left outer join VW_ASR_STAFF st on w.suid=st.suid and y1.STAFFCODE = st.STAFFCODE join tb_hse_common z on w.suid=z.suid and w.subjcode=z.code_id and z.tb_id='SBJ' where x.grptype='B' and y.subjblkcode='Block_1' ) x1 on a.suid=x1.suid and a.schlvl=x1.schlevel and a.schsess=x1.schsession and a.schyear=x1.schyear and a.stuid=x1.stuid and a.stuschrecid=x1.stuschrecid left outer join ( select x.suid, x.schlevel, x.schsession, x.schyear, w.stuid, w.stuschrecid, z.ch_des, z.en_des, y.divsubjcode,st.SHORTNAME from tb_stu_stusubj w join tb_sch_divsubjgrp x on w.crosssubjgrp=x.subjgrpcode and w.subjcode=x.subjcode join tb_sch_dsgsubj y on x.suid=y.suid and x.schlevel=y.schlevel and x.schsession=y.schsession and x.schyear=y.schyear and x.subjgrpcode=y.subjgrpcode and w.crosssubjcode=y.divsubjcode left outer join TB_SCH_DSGSUBJTCHR y1 on x.suid=y1.suid and x.schlevel=y1.schlevel and x.schsession=y1.schsession and x.schyear=y1.schyear and x.subjgrpcode=y1.subjgrpcode and w.crosssubjcode=y1.divsubjcode and y1.STAFFSEQ=1 left outer join VW_ASR_STAFF st on w.suid=st.suid and y1.STAFFCODE = st.STAFFCODE join tb_hse_common z on w.suid=z.suid and w.subjcode=z.code_id and z.tb_id='SBJ' where x.grptype='B' and y.subjblkcode='Block_2' ) x2 on a.suid=x2.suid and a.schlvl=x2.schlevel and a.schsess=x2.schsession and a.schyear=x2.schyear and a.stuid=x2.stuid and a.stuschrecid=x2.stuschrecid left outer join ( select x.suid, x.schlevel, x.schsession, x.schyear, w.stuid, w.stuschrecid, z.ch_des, z.en_des, y.divsubjcode,st.SHORTNAME from tb_stu_stusubj w join tb_sch_divsubjgrp x on w.crosssubjgrp=x.subjgrpcode and w.subjcode=x.subjcode join tb_sch_dsgsubj y on x.suid=y.suid and x.schlevel=y.schlevel and x.schsession=y.schsession and x.schyear=y.schyear and x.subjgrpcode=y.subjgrpcode and w.crosssubjcode=y.divsubjcode left outer join TB_SCH_DSGSUBJTCHR y1 on x.suid=y1.suid and x.schlevel=y1.schlevel and x.schsession=y1.schsession and x.schyear=y1.schyear and x.subjgrpcode=y1.subjgrpcode and w.crosssubjcode=y1.divsubjcode and y1.STAFFSEQ=1 left outer join VW_ASR_STAFF st on w.suid=st.suid and y1.STAFFCODE = st.STAFFCODE join tb_hse_common z on w.suid=z.suid and w.subjcode=z.code_id and z.tb_id='SBJ' where x.grptype='B' and y.subjblkcode='Block_3' ) x3 on a.suid=x3.suid and a.schlvl=x3.schlevel and a.schsess=x3.schsession and a.schyear=x3.schyear and a.stuid=x3.stuid and a.stuschrecid=x3.stuschrecid where a.schyear=? and a.classlvl=? and (a.status=null or a.status not in (4,5,6)) order by a.classlvl, a.classcode, a.classno, a.chname, a.enname