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

本文由 cantsy 在 2012-08-06 發表於 "WebSAMS 討論區" 討論區

  1. 56560977

    cantsy
    Expand Collapse

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


    Thank you.
     

    附件文件:

    • eg.jpg
      文件大小:
      14.8 KB
      瀏覽:
      3,602
  2. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    老師請參考以下的 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
     
    #2 edb-石頭, 2012-08-07
  3. 56560977

    cantsy
    Expand Collapse

    文章:
    129
    讚:
    0
    What are the input ? Thx.
     

    附件文件:

    • input.png
      文件大小:
      12.4 KB
      瀏覽:
      3,608
  4. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    老師請參考以下的 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
     
    #4 edb-石頭, 2012-08-07
  5. 56560977

    cantsy
    Expand Collapse

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

    Tom
    Expand Collapse

    文章:
    79
    讚:
    0
    既然這個SQL 好用, 可否加入成為常設在Websams 學生資料 > 科目設定 > 報告 ?
     
  7. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    老師可以聯絡貴校的網上校管系統學校聯絡主任提出建議。
     
    #7 edb-石頭, 2012-08-31
  8. 56560977

    cantsy
    Expand Collapse

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

    附件文件:

    • a.png
      文件大小:
      9.4 KB
      瀏覽:
      3,594
  9. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    老師請聯絡貴校的網上校管系統學校聯絡主任。
     
    #9 edb-石頭, 2012-08-31
  10. 56560977

    cantsy
    Expand Collapse

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

    cantsy
    Expand Collapse

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

    瀏覽附件c.png
     
  12. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    做不到老師要求的格式, 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
     
    #12 edb-石頭, 2012-09-03
  13. 56560977

    cantsy
    Expand Collapse

    文章:
    129
    讚:
    0
    SQL 可顯示非科目集, 但有以下問題:
    1. 同一科目小組學生出現 了數次, listening, speaking....同一學生出現了四次
    2. 雖然不支援 multi-column, 但可否以班及班號作基礎,
    瀏覽附件a.png
     
  14. 10873736

    ps-ncw
    Expand Collapse

    文章:
    108
    讚:
    0
    回覆 4# edb-石頭


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

    edb-escm
    Expand Collapse

    文章:
    434
    讚:
    0
    老師請參考以下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, 2015-08-10
  16. 10873736

    ps-ncw
    Expand Collapse

    文章:
    108
    讚:
    0
    回覆 15# edb-escm


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

    edb-escm
    Expand Collapse

    文章:
    434
    讚:
    0
    老師請聯絡貴校的網上校管系統學校聯絡主任以便跟進。
     
    #17 edb-escm, 2015-08-10
  18. 10873736

    ps-ncw
    Expand Collapse

    文章:
    108
    讚:
    0
    回覆 17# edb-escm


    但本身的post 4 SQL是可以的!
     
  19. 57678715

    edb-escm
    Expand Collapse

    文章:
    434
    讚:
    0
    老師請參考以下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, 2015-08-10
  20. 10873736

    ps-ncw
    Expand Collapse

    文章:
    108
    讚:
    0
    回覆 19# edb-escm


    這個成功了!
    謝謝!