SQL 成績 SQL

本文由 55094568 在 2020-07-10 發表於 "WebSAMS 討論區" 討論區

  1. 55094568

    55094568
    Expand Collapse

    文章:
    34
    讚:
    0
    因初中改用945 通識 及有非華語學生

    1. 所以想額外加多2欄分數 分別係 初中通識 及 中文-非

    2.想問有無一條比較好的公式不用科目編號 而可以 抽哂所以科目既SQL
    (不想因轉 科目編號 ,而用不到公式????? )

    已用紅字表示想加的地方

    謝謝








    select

    a.SCHLVL 'SCHLV',
    a.SCHSESS 'SESSION',
    a.CLASSLVL 'CLASSLVL',


    a.REGNO 'REGNO',
    a.enname '英文姓名',

    a.classcode '班別',
    a.classno '班號',
    a.chname '中文姓名',
    a.sex '性別',
    a.SCHHOUSE 'SCHHUS',

    b.syspercscore 'T1A1 總平均分',
    c.syspercscore 'T1A2 總平均分',
    g.syspercscore 'T1總平均分',
    g.OVERCONDGRADECONVERSIONCOMPCODE 'T1操行',
    n.namechi '中文版T1操行',
    g.omclass 'T1班名次',
    g.omclasslvl 'T1級名次',

    d.syspercscore 'T2A1 總平均分',
    e.syspercscore 'T2A2 總平均分',
    h.syspercscore 'T2總平均分',
    h.OVERCONDGRADECONVERSIONCOMPCODE 'T2操行',
    o.namechi '中文版T2操行',
    h.omclass 'T2班名次',
    h.omclasslvl 'T2級名次',

    f.omclass '全年班名次',
    f.omclasslvl '全年級名次',
    f.syspercscore '全年總平均分',
    f.OVERCONDGRADECONVERSIONCOMPCODE '全年總操行',

    p.namechi '中文版全年總操行',
    i.sysscore '中文年終分數',
    j.sysscore '英文年終分數',
    k.sysscore '數學年終分數',
    m.sysscore '通識年終分數'

    2欄想加這裡




    from vw_stu_lateststudent a
    left outer join tb_asr_studassessdata b
    on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear and b.timeseq=1101

    left outer join tb_asr_studassessdata c
    on a.suid=c.suid and a.stuid=c.stuid and a.schyear=c.schyear and c.timeseq=1102

    left outer join tb_asr_studassessdata d
    on a.suid=d.suid and a.stuid=d.stuid and a.schyear=d.schyear and d.timeseq=1201

    left outer join tb_asr_studassessdata e
    on a.suid=e.suid and a.stuid=e.stuid and a.schyear=e.schyear and e.timeseq=1202

    left outer join tb_asr_studassessdata f
    on a.suid=f.suid and a.stuid=f.stuid and a.schyear=f.schyear and f.timeseq=1000

    left outer join tb_asr_studassessdata g
    on a.suid=g.suid and a.stuid=g.stuid and a.schyear=g.schyear and g.timeseq=1100

    left outer join tb_asr_studassessdata h
    on a.suid=h.suid and a.stuid=h.stuid and a.schyear=h.schyear and h.timeseq=1200

    left outer join tb_asr_subjassessdata i
    on a.suid=i.suid and a.schlvl=i.schlevel and a.schsess=i.schsession and a.classlvl=i.classlevel
    and a.schyear=i.schyear and a.stuid=i.stuid and i.subjcode = '080' and i.timeseq=1000

    left outer join tb_asr_subjassessdata j
    on a.suid=j.suid and a.schlvl=j.schlevel and a.schsess=j.schsession and a.classlvl=j.classlevel
    and a.schyear=j.schyear and a.stuid=j.stuid and j.subjcode = '165' and j.timeseq=1000

    left outer join tb_asr_subjassessdata k
    on a.suid=k.suid and a.schlvl=k.schlevel and a.schsess=k.schsession and a.classlvl=k.classlevel
    and a.schyear=k.schyear and a.stuid=k.stuid and k.subjcode in ('280','22S') and k.timeseq=1000

    left outer join tb_asr_subjassessdata m
    on a.suid=m.suid and a.schlvl=m.schlevel and a.schsess=m.schsession and a.classlvl=m.classlevel
    and a.schyear=m.schyear and a.stuid=m.stuid and m.subjcode = '265' and m.timeseq=1000

    left outer join tb_asr_gradeconversioncomp n
    on g.suid=n.suid and g.schyear=n.schyear and g.overcondgradeconversionseq = n.gradeconversionseq
    and g.overcondgradeconversioncompcode = n.gradeconversioncompcode

    left outer join tb_asr_gradeconversioncomp o
    on h.suid=o.suid and h.schyear=o.schyear and h.overcondgradeconversionseq = o.gradeconversionseq
    and h.overcondgradeconversioncompcode = o.gradeconversioncompcode

    left outer join tb_asr_gradeconversioncomp p
    on f.suid=p.suid and f.schyear=p.schyear and f.overcondgradeconversionseq = p.gradeconversionseq
    and f.overcondgradeconversioncompcode = p.gradeconversioncompcode
    where a.schyear=?
    order by a.classlvl,a.classcode,a.classno
     
    #1 55094568, 2020-07-10
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    145
    讚:
    0
    1, 初中通識 及 中文-非華語學生 的科目代碼是什麼 ?
     
    #2 edb-catherinewschan, 2020-07-14
  3. 55094568

    55094568
    Expand Collapse

    文章:
    34
    讚:
    0

    945 & 997



    2.有無一條比較好的公式不用科目編號 而可以 抽哂所以科目既SQL
    (不想因轉 科目編號 ,而用不到公式????? )

    已用紅字表示想加的地方

    謝謝
     
    #3 55094568, 2020-07-15
  4. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    145
    讚:
    0
    你好,請參考以下SQL 及 看看紅色字的部份。

    select

    a.SCHLVL 'SCHLV',
    a.SCHSESS 'SESSION',
    a.CLASSLVL 'CLASSLVL',
    a.REGNO 'REGNO',
    a.enname '英文姓名',
    a.classcode '班別',
    a.classno '班號',
    a.chname '中文姓名',
    a.sex '性別',
    a.SCHHOUSE 'SCHHUS',

    b.syspercscore 'T1A1 總平均分',
    c.syspercscore 'T1A2 總平均分',
    g.syspercscore 'T1總平均分',
    g.OVERCONDGRADECONVERSIONCOMPCODE 'T1操行',
    n.namechi '中文版T1操行',
    g.omclass 'T1班名次',
    g.omclasslvl 'T1級名次',

    d.syspercscore 'T2A1 總平均分',
    e.syspercscore 'T2A2 總平均分',
    h.syspercscore 'T2總平均分',
    h.OVERCONDGRADECONVERSIONCOMPCODE 'T2操行',
    o.namechi '中文版T2操行',
    h.omclass 'T2班名次',
    h.omclasslvl 'T2級名次',

    f.omclass '全年班名次',
    f.omclasslvl '全年級名次',
    f.syspercscore '全年總平均分',
    f.OVERCONDGRADECONVERSIONCOMPCODE '全年總操行',

    p.namechi '中文版全年總操行',
    i.sysscore '中文年終分數',
    j.sysscore '英文年終分數',
    k.sysscore '數學年終分數',
    m.sysscore '通識年終分數',

    q.sysscore '初中通識年終分數',
    r.sysscore '中文-非華語學生終分數'


    from wsadmin.vw_stu_lateststudent a
    left outer join wsadmin.tb_asr_studassessdata b
    on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear and b.timeseq=1101

    left outer join wsadmin.tb_asr_studassessdata c
    on a.suid=c.suid and a.stuid=c.stuid and a.schyear=c.schyear and c.timeseq=1102

    left outer join wsadmin.tb_asr_studassessdata d
    on a.suid=d.suid and a.stuid=d.stuid and a.schyear=d.schyear and d.timeseq=1201

    left outer join wsadmin.tb_asr_studassessdata e
    on a.suid=e.suid and a.stuid=e.stuid and a.schyear=e.schyear and e.timeseq=1202

    left outer join wsadmin.tb_asr_studassessdata f
    on a.suid=f.suid and a.stuid=f.stuid and a.schyear=f.schyear and f.timeseq=1000

    left outer join wsadmin.tb_asr_studassessdata g
    on a.suid=g.suid and a.stuid=g.stuid and a.schyear=g.schyear and g.timeseq=1100

    left outer join wsadmin.tb_asr_studassessdata h
    on a.suid=h.suid and a.stuid=h.stuid and a.schyear=h.schyear and h.timeseq=1200

    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.classlvl=i.classlevel
    and a.schyear=i.schyear and a.stuid=i.stuid and i.subjcode = '080' and i.timeseq=1000

    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.classlvl=j.classlevel
    and a.schyear=j.schyear and a.stuid=j.stuid and j.subjcode = '165' and j.timeseq=1000

    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.classlvl=k.classlevel
    and a.schyear=k.schyear and a.stuid=k.stuid and k.subjcode in ('280','22S') and k.timeseq=1000

    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.classlvl=m.classlevel
    and a.schyear=m.schyear and a.stuid=m.stuid and m.subjcode = '265' and m.timeseq=1000

    left outer join wsadmin.tb_asr_subjassessdata q
    on a.suid=q.suid and a.schlvl=q.schlevel and a.schsess=q.schsession and a.classlvl=q.classlevel
    and a.schyear=q.schyear and a.stuid=q.stuid and q.subjcode = '945' and q.timeseq=1000

    left outer join wsadmin.tb_asr_subjassessdata r
    on a.suid=r.suid and a.schlvl=r.schlevel and a.schsess=r.schsession and a.classlvl=r.classlevel
    and a.schyear=r.schyear and a.stuid=r.stuid and r.subjcode = '997' and r.timeseq=1000


    left outer join wsadmin.tb_asr_gradeconversioncomp n
    on g.suid=n.suid and g.schyear=n.schyear and g.overcondgradeconversionseq = n.gradeconversionseq
    and g.overcondgradeconversioncompcode = n.gradeconversioncompcode

    left outer join wsadmin.tb_asr_gradeconversioncomp o
    on h.suid=o.suid and h.schyear=o.schyear and h.overcondgradeconversionseq = o.gradeconversionseq
    and h.overcondgradeconversioncompcode = o.gradeconversioncompcode

    left outer join wsadmin.tb_asr_gradeconversioncomp p
    on f.suid=p.suid and f.schyear=p.schyear and f.overcondgradeconversionseq = p.gradeconversionseq
    and f.overcondgradeconversioncompcode = p.gradeconversioncompcode
    where a.schyear=?
    order by a.classlvl,a.classcode,a.classno
     
    #4 edb-catherinewschan, 2020-07-15
  5. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    145
    讚:
    0
    其實指定欄位要用科目編號寫死,才可以出到指定科目的欄位。

    如果你不想指定科目,可以把所有科目向下列出來....
    可以參考 網上校管系統 — 常用SQL參考庫 https://www.websams.edb.gov.hk//sql/html/SQL_Q&A.html#
    學生成績 (下載)
    看看 7. 抽取每名學生往年每科的全年成績。
     
    #5 edb-catherinewschan, 2020-07-15