SQL 修改SQL 分卷分數 及分卷名次 過住三年記錄比較

本文由 ITA-SAU 在 2023-04-12 發表於 "WebSAMS 討論區" 討論區

標籤:
上一篇 下一篇
正在加載...
  1. 10419806

    ITA-SAU
    Expand Collapse

    文章:
    2
    讚:
    0
    修改SQL 分卷分數 及分卷名次 過住三年記錄比較

    select

    a.CLASSCODE '班別',

    a.CLASSNO '班號',

    a.CHNAME '中文姓名',

    a.ENNAME '英文姓名',

    c.en_des 'Subject',

    c.ch_des '科目',

    a.SCHYEAR '學年',

    a.classcode '今年班別',

    e.sysscore '今年T3成績',

    f.sysscore '今年T2成績',

    g.sysscore '今年T1成績',

    h.classcode '1年前班別',

    i.sysscore '1年前T3成績',

    j.sysscore '1年前T2成績',

    k.sysscore '1年前T1成績',

    l.classcode '2年前班別',

    m.sysscore '2年前T3成績',

    n.sysscore '2年前T2成績',

    o.sysscore '2年前T1成績'

    from wsadmin.VW_STU_LATESTSTUDENT a

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA e

    on a.SUID = e.SUID and a.SCHLVL = e.SCHLEVEL and a.SCHSESS = e.SCHSESSION and a.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and e.TIMESEQ = 1300 and e.subjcode=?

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA f

    on a.SUID = f.SUID and a.SCHLVL = f.SCHLEVEL and a.SCHSESS = f.SCHSESSION and a.SCHYEAR = f.SCHYEAR and a.STUID = f.STUID and f.TIMESEQ = 1200 and f.subjcode=e.subjcode

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA g

    on a.SUID = g.SUID and a.SCHLVL = g.SCHLEVEL and a.SCHSESS = g.SCHSESSION and a.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and g.TIMESEQ = 1100 and g.subjcode=e.subjcode

    left outer join

    wsadmin.VW_STU_LATESTSTUDENT h

    on a.SUID = h.SUID and a.SCHLVL = h.SCHLVL and a.SCHSESS = h.SCHSESS and a.SCHYEAR-1 = h.SCHYEAR and a.STUID = h.STUID

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA i

    on a.SUID = i.SUID and a.SCHLVL = i.SCHLEVEL and a.SCHSESS = i.SCHSESSION and a.SCHYEAR-1 = i.SCHYEAR and a.STUID = i.STUID and i.TIMESEQ = 1300 and i.subjcode=e.subjcode

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA j

    on a.SUID = j.SUID and a.SCHLVL = j.SCHLEVEL and a.SCHSESS = j.SCHSESSION and a.SCHYEAR-1 = j.SCHYEAR and a.STUID = j.STUID and j.TIMESEQ = 1200 and j.subjcode=e.subjcode

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA k

    on a.SUID = k.SUID and a.SCHLVL = k.SCHLEVEL and a.SCHSESS = k.SCHSESSION and a.SCHYEAR-1 = k.SCHYEAR and a.STUID = k.STUID and k.TIMESEQ = 1100 and k.subjcode=e.subjcode

    left outer join

    wsadmin.VW_STU_LATESTSTUDENT l

    on a.SUID = l.SUID and a.SCHLVL = l.SCHLVL and a.SCHSESS = l.SCHSESS and a.SCHYEAR-2 = l.SCHYEAR and a.STUID = l.STUID

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA m

    on a.SUID = m.SUID and a.SCHLVL = m.SCHLEVEL and a.SCHSESS = m.SCHSESSION and a.SCHYEAR-2 = m.SCHYEAR and a.STUID = m.STUID and m.TIMESEQ = 1300 and m.subjcode=e.subjcode

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA n

    on a.SUID = n.SUID and a.SCHLVL = n.SCHLEVEL and a.SCHSESS = n.SCHSESSION and a.SCHYEAR-2 = n.SCHYEAR and a.STUID = n.STUID and n.TIMESEQ = 1200 and n.subjcode=e.subjcode

    left outer join wsadmin.TB_ASR_SUBJASSESSDATA o

    on a.SUID = o.SUID and a.SCHLVL = o.SCHLEVEL and a.SCHSESS = o.SCHSESSION and a.SCHYEAR-2 = o.SCHYEAR and a.STUID = o.STUID and o.TIMESEQ = 1100 and o.subjcode=e.subjcode

    left outer join tb_hse_common c

    on a.suid=c.suid and e.subjcode=c.code_id and c.tb_id='SBJ'

    where a.SCHYEAR=? and a.classlvl=?

    order by a.CLASSCODE,a.CLASSNO




    想咁改. .唔知可唔可以呢？

    select

    a.REGNO ‘學生註冊編號’

    a.CLASSCODE '班別',

    a.CLASSNO '班號',

    a.CHNAME '中文姓名',

    a.ENNAME '英文姓名',

    a.SCHYEAR '學年',

    a.classcode '今年班別',

    g. SYSGRADECONVCOMPCOD '今年T1成績中文13分卷分數',

    g. omclasslvl '今年T1成績中文13分卷名次

    h.classcode '1年前班別',

    k. SYSGRADECONVCOMPCOD '1年前T1成績中文13分卷分數',

    k. omclasslvl '1年前T1成績中文13分卷名次'

    l.classcode '2年前班別',

    o. SYSGRADECONVCOMPCOD '2年前T1成績中文13分卷分數'

    o. omclasslvl '2年前T1成績中文13分卷名次
     
    #1 ITA-SAU, 2023-04-12
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    302
    讚:
    0
    你好

    是否想顯示中文分卷的GRADE 及名次??
    請先提供貴校的中文分卷碼，在"代碼管理 > 編修"內找科目分卷，就可以找到。
     
    #2 edb-catherinewschan, 2023-04-13
  3. 10419806

    ITA-SAU
    Expand Collapse

    文章:
    2
    讚:
    0
    是否想顯示中文分卷的GRADE 及名次?? ( 想顯示中文80 分卷13 閱讀乙卷 分數 及 分數名次 )
    請先提供貴校的中文分卷碼，在"代碼管理 > 編修"內找科目分卷，就可以找到。
     

    附件文件:

    • 8013.jpg
      8013.jpg
      文件大小:
      109.5 KB
      瀏覽:
      133
    #3 ITA-SAU, 2023-04-13
  4. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    302
    讚:
    0
    你好

    由於分卷是用TB_ASR_SUBJCOMASSESSDATA，所以一律取代TB_ASR_SUBJASSESSDATA。
    另外，由於你只抽取T1成績，所以把不用的連結刪除，即是所以抽取T2和T3的連結。

    我再優化SQL，令到任由用戶輸入級別、科目、科目分卷、年份和考績的資料。自動抽取資料，而不用更改SQL語句。

    可以參考以下的SQL

    select
    a.REGNO '學生註冊編號',
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    a.CHNAME '中文姓名',
    a.ENNAME '英文姓名',
    a.SCHYEAR '學年',
    c.en_des 'Subject',
    c.ch_des '分卷科目',

    a.classcode '今年班別',
    g.SYSGRADECONVCOMPCODE '今年T1成績中文13分卷分數', g.omclasslvl '今年T1成績中文13分卷名次',

    h.classcode '1年前班別',
    k.SYSGRADECONVCOMPCODE '1年前T1成績中文13分卷分數', k.omclasslvl '1年前T1成績中文13分卷名次',

    l.classcode '2年前班別',
    o.SYSGRADECONVCOMPCODE '2年前T1成績中文13分卷分數', o.omclasslvl '2年前T1成績中文13分卷名次'

    from wsadmin.VW_STU_LATESTSTUDENT a
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA g
    on a.SUID = g.SUID and a.SCHLVL = g.SCHLEVEL and a.SCHSESS = g.SCHSESSION and a.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and g.TIMESEQ = ?
    and g.subjcode=? and g.subjcompcode=?

    left outer join wsadmin.VW_STU_LATESTSTUDENT h
    on a.SUID = h.SUID and a.SCHLVL = h.SCHLVL and a.SCHSESS = h.SCHSESS and a.SCHYEAR-1 = h.SCHYEAR and a.STUID = h.STUID
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA k
    on a.SUID = k.SUID and a.SCHLVL = k.SCHLEVEL and a.SCHSESS = k.SCHSESSION and a.SCHYEAR-1 = k.SCHYEAR and a.STUID = k.STUID and k.TIMESEQ = g.TIMESEQ
    and k.subjcode=g.subjcode and k.subjcompcode=g.subjcompcode

    left outer join wsadmin.VW_STU_LATESTSTUDENT l
    on a.SUID = l.SUID and a.SCHLVL = l.SCHLVL and a.SCHSESS = l.SCHSESS and a.SCHYEAR-2 = l.SCHYEAR and a.STUID = l.STUID
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA o
    on a.SUID = o.SUID and a.SCHLVL = o.SCHLEVEL and a.SCHSESS = o.SCHSESSION and a.SCHYEAR-2 = o.SCHYEAR and a.STUID = o.STUID and o.TIMESEQ = g.TIMESEQ
    and o.subjcode=g.subjcode and o.subjcompcode=g.subjcompcode

    left outer join wsadmin.TB_HSE_SBJCMP c
    on a.suid=c.suid and g.subjcode=c.sbj_code and g.subjcompcode=c.code_id and c.tb_id='SBJCMP'

    where a.SCHYEAR=? and a.classlvl=?
    order by a.CLASSCODE,a.CLASSNO
     
    #4 edb-catherinewschan, 2023-04-18
    Last edited: 2023-04-18
  5. 59619044

    YUEN-SAU
    Expand Collapse

    文章:
    6
    讚:
    0
    根據以上的SQL, 已成功抽取資料，但想要中文分卷的分數，而不是grade，請問如何解決？謝謝！
     

    附件文件:

    #5 YUEN-SAU, 2023-07-12
  6. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    302
    讚:
    0
    由於之前你提供的SQL是用GRADE，所以出現了GRADE的分數。

    只要把SYSGRADECONVCOMPCODE 更改為 SYSSCORE ，就可以出分數了。

    select
    a.REGNO '學生註冊編號',
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    a.CHNAME '中文姓名',
    a.ENNAME '英文姓名',
    a.SCHYEAR '學年',
    c.en_des 'Subject',
    c.ch_des '分卷科目',

    a.classcode '今年班別',
    g.SYSGRADECONVCOMPCODE '今年T1成績中文13分卷分數', g.omclasslvl '今年T1成績中文13分卷名次',

    h.classcode '1年前班別',
    k.SYSGRADECONVCOMPCODE '1年前T1成績中文13分卷分數', k.omclasslvl '1年前T1成績中文13分卷名次',

    l.classcode '2年前班別',
    o.SYSGRADECONVCOMPCODE '2年前T1成績中文13分卷分數', o.omclasslvl '2年前T1成績中文13分卷名次'

    from wsadmin.VW_STU_LATESTSTUDENT a
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA g
    on a.SUID = g.SUID and a.SCHLVL = g.SCHLEVEL and a.SCHSESS = g.SCHSESSION and a.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and g.TIMESEQ = ?
    and g.subjcode=? and g.subjcompcode=?

    left outer join wsadmin.VW_STU_LATESTSTUDENT h
    on a.SUID = h.SUID and a.SCHLVL = h.SCHLVL and a.SCHSESS = h.SCHSESS and a.SCHYEAR-1 = h.SCHYEAR and a.STUID = h.STUID
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA k
    on a.SUID = k.SUID and a.SCHLVL = k.SCHLEVEL and a.SCHSESS = k.SCHSESSION and a.SCHYEAR-1 = k.SCHYEAR and a.STUID = k.STUID and k.TIMESEQ = g.TIMESEQ
    and k.subjcode=g.subjcode and k.subjcompcode=g.subjcompcode

    left outer join wsadmin.VW_STU_LATESTSTUDENT l
    on a.SUID = l.SUID and a.SCHLVL = l.SCHLVL and a.SCHSESS = l.SCHSESS and a.SCHYEAR-2 = l.SCHYEAR and a.STUID = l.STUID
    left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA o
    on a.SUID = o.SUID and a.SCHLVL = o.SCHLEVEL and a.SCHSESS = o.SCHSESSION and a.SCHYEAR-2 = o.SCHYEAR and a.STUID = o.STUID and o.TIMESEQ = g.TIMESEQ
    and o.subjcode=g.subjcode and o.subjcompcode=g.subjcompcode

    left outer join wsadmin.TB_HSE_SBJCMP c
    on a.suid=c.suid and g.subjcode=c.sbj_code and g.subjcompcode=c.code_id and c.tb_id='SBJCMP'

    where a.SCHYEAR=? and a.classlvl=?
    order by a.CLASSCODE,a.CLASSNO
     
    #6 edb-catherinewschan, 2023-07-13
上一篇 下一篇
正在加載...
(您必須登入後才可以張貼。)