SQL 求SQL 上下學期英文分卷

本文由 hylamsir 在 2022-08-01 發表於 "WebSAMS 討論區" 討論區

標籤:
  1. 55073149

    hylamsir
    Expand Collapse

    文章:
    10
    讚:
    0
    你好!
    想求SQL,英文科分卷 英文閱讀及寫作 和 英國語文應用
    兩個分卷 上學期 和 下學期 考試的成績
    用來比較兩次進步的

    十分感謝~~
     
    #1 hylamsir, 2022-08-01
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    250
    讚:
    0
    可以參考以下的SQL

    由於你未提供分卷編號,所以會用01、02和03去代替。

    英文科
    01英文閱讀
    02英文寫作
    03英國語文應用

    當執時,輸入年份和兩個考績編號就可以了。

    select
    b.CLASSLVL '級別',
    b.CLASSCODE '班別',
    b.CLASSNO '班號',
    a.ENNAME '英文姓名',
    a.CHNAME '中文姓名',

    e.sysscore '(1)英文成績',
    e.SYSGRADECONVCOMPCODE '(1)英文等級',
    e.omclasslvl '(1)英文級名次',
    e.omclassbase '(1)英文人數',
    e1.sysscore '(1) 01英文閱讀 成績',
    e1.SYSGRADECONVCOMPCODE '(1) 01英文閱讀 等級',
    e1.omclasslvl '(1) 01英文閱讀 級名次',
    e1.omclassbase '(1) 01英文閱讀 人數',
    e2.sysscore '(1) 02英文寫作 成績',
    e2.SYSGRADECONVCOMPCODE '(1) 02英文寫作 等級',
    e2.omclasslvl '(1) 02英文寫作 級名次',
    e2.omclassbase '(1) 02英文寫作 人數',
    e4.sysscore '(1) 03英國語文應用 成績',
    e4.SYSGRADECONVCOMPCODE '(1) 03英國語文應用 等級',
    e4.omclasslvl '(1) 03英國語文應用 級名次',
    e4.omclassbase '(1) 03英國語文應用 人數',

    f.sysscore '(2)英文成績',
    f.SYSGRADECONVCOMPCODE '(2)英文等級',
    f.omclasslvl '(2)英文級名次',
    f.omclassbase '(2)英文人數',
    f1.sysscore '(2) 01英文閱讀 成績',
    f1.SYSGRADECONVCOMPCODE '(2) 01英文閱讀 等級',
    f1.omclasslvl '(2) 01英文閱讀 級名次',
    f1.omclassbase '(2) 01英文閱讀 人數',
    f2.sysscore '(2) 02英文寫作 成績',
    f2.SYSGRADECONVCOMPCODE '(2) 02英文寫作 等級',
    f2.omclasslvl '(2) 02英文寫作 級名次',
    f2.omclassbase '(2) 02英文寫作 人數',
    f4.sysscore '(2) 03英國語文應用 成績',
    f4.SYSGRADECONVCOMPCODE '(2) 03英國語文應用 等級',
    f4.omclasslvl '(2) 03英國語文應用 級名次',
    f4.omclassbase '(2) 03英國語文應用 人數'

    from wsadmin.TB_STU_STUDENT a
    join wsadmin.VW_STU_LATESTSTUSCHREC b
    on a.SUID = b.SUID and a.STUID = b.STUID and b.SCHYEAR = ?
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA e
    on a.SUID = e.SUID and a.SCHLVL = e.SCHLEVEL and a.SCHSESS = e.SCHSESSION and b.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.SUBJCODE = '165'
    and e.TIMESEQ=?
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e1
    on a.SUID = e1.SUID and A.SCHLVL = e1.SCHLEVEL and a.SCHSESS = e1.SCHSESSION and b.SCHYEAR = e1.SCHYEAR and a.STUID = e1.STUID and
    e.SUBJCODE = e1.SUBJCODE and e1.SUBJCOMPCODE = '01' and e.TIMESEQ = e1.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e2
    on a.SUID = e2.SUID and A.SCHLVL = e2.SCHLEVEL and a.SCHSESS = e2.SCHSESSION and b.SCHYEAR = e2.SCHYEAR and a.STUID = e2.STUID and
    e.SUBJCODE = e2.SUBJCODE and e2.SUBJCOMPCODE = '02' and e.TIMESEQ = e2.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA e4
    on a.SUID = e4.SUID and A.SCHLVL = e4.SCHLEVEL and a.SCHSESS = e4.SCHSESSION and b.SCHYEAR = e4.SCHYEAR and a.STUID = e4.STUID and
    e.SUBJCODE = e4.SUBJCODE and e4.SUBJCOMPCODE = '03' and e.TIMESEQ = e4.TIMESEQ

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA f
    on a.SUID = f.SUID and a.SCHLVL = f.SCHLEVEL and a.SCHSESS = f.SCHSESSION and b.SCHYEAR = f.SCHYEAR
    and a.STUID = f.STUID and f.SUBJCODE = '165' and f.TIMESEQ=?
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA f1
    on a.SUID = f1.SUID and A.SCHLVL = f1.SCHLEVEL and a.SCHSESS = f1.SCHSESSION and b.SCHYEAR = f1.SCHYEAR
    and a.STUID = f1.STUID and f.SUBJCODE = f1.SUBJCODE and f1.SUBJCOMPCODE = '01' and f.TIMESEQ = f1.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA f2
    on a.SUID = f2.SUID and A.SCHLVL = f2.SCHLEVEL and a.SCHSESS = f2.SCHSESSION and b.SCHYEAR = f2.SCHYEAR
    and a.STUID = f2.STUID and f.SUBJCODE = f2.SUBJCODE and f2.SUBJCOMPCODE = '02' and f.TIMESEQ = f2.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA f4
    on a.SUID = f4.SUID and A.SCHLVL = f4.SCHLEVEL and a.SCHSESS = f4.SCHSESSION and b.SCHYEAR = f4.SCHYEAR
    and a.STUID = f4.STUID and f.SUBJCODE = f4.SUBJCODE and f4.SUBJCOMPCODE = '03' and f.TIMESEQ = f4.TIMESEQ
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA f5
    on a.SUID = f5.SUID and A.SCHLVL = f5.SCHLEVEL and a.SCHSESS = f5.SCHSESSION and b.SCHYEAR = f5.SCHYEAR
    and a.STUID = f5.STUID and f.SUBJCODE = f5.SUBJCODE and f5.SUBJCOMPCODE = '04' and f.TIMESEQ = f5.TIMESEQ

    order by b.SCHLVL, b.SCHSESS, b.CLASSLVL, b.CLASSCODE, b.CLASSNO
     
    #2 edb-catherinewschan, 2022-08-05
  3. 55073149

    hylamsir
    Expand Collapse

    文章:
    10
    讚:
    0
    感謝你~~~
     
    #3 hylamsir, 2022-08-11
  4. 55073149

    hylamsir
    Expand Collapse

    文章:
    10
    讚:
    0
    抱歉,我不會修改分卷編號,我們的分卷是


    英文-
    英默
    英會
    英卷
    英閱寫

    請問可以如何知道分卷編號?
     
    #4 hylamsir, 2022-08-12
  5. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    250
    讚:
    0
    你好

    在 代碼管理 > 編修代碼表,找 "科目分卷",就會顯示各科的科目分卷 代碼表
     
    #5 edb-catherinewschan, 2022-08-12
  6. 55073149

    hylamsir
    Expand Collapse

    文章:
    10
    讚:
    0
    可是我全部分卷,都是寫「165」
     

    附件文件:

    #6 hylamsir, 2022-08-12
  7. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    250
    讚:
    0
    你好,

    subjectcode.JPG
     
    #7 edb-catherinewschan, 2022-08-12
  8. 55073149

    hylamsir
    Expand Collapse

    文章:
    10
    讚:
    0
    大成功了,謝謝:):)
     
    #8 hylamsir, 2022-08-13