SQL SQL 顯示學生選修科屬於哪個 block

SQL 顯示每位學生選修科屬於哪個 block, 以班及班跪排序 , 例如:


Thank you.

 

老師請參考以下的 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

 

What are the input ? Thx.

 

老師請參考以下的 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

 

謝謝 ! 很好用, 但如果加入所有非科目集的科目, 可以嗎? 例如英文小組 EN15_a, M1, M2,......等

 

既然這個SQL 好用, 可否加入成為常設在Websams 學生資料 > 科目設定 > 報告 ?

 

老師可以聯絡貴校的網上校管系統學校聯絡主任提出建議。

 

請問如何在原先的SQL一併顯示所有 科目集和非科目集, 例如: 謝謝 !

 

老師請聯絡貴校的網上校管系統學校聯絡主任。

 

如果加入所有非科目集的科目, 可以嗎? 例如英文小組 EN15_a, M1, M2,......等
Thank you. 瀏覽附件b.png

 

如果加入所有非科目集的科目, 可以嗎? 例如英文小組 EN15_a, M1, M2,......等
Thank you.

瀏覽附件c.png

 

做不到老師要求的格式, 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

 

回覆 4# edb-石頭


請問可否顯示divsubjcode及其任教老師SHORTNAME?

 

老師請參考以下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

 

回覆 15# edb-escm


沒有資料顯示出來！我用post 4可以出到資料，想在這個SQL之上顯示divsubjcode及其任教老師SHORTNAME！
謝謝！

 

老師請聯絡貴校的網上校管系統學校聯絡主任以便跟進。

 

回覆 17# edb-escm


但本身的post 4 SQL是可以的！

 

老師請參考以下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

 

回覆 19# edb-escm


這個成功了!
謝謝!