SLP Assessment Data for JUPAS Academic Performance

本文由 Keith Ip 在 2012-02-13 發表於 "WebSAMS 討論區" 討論區

  1. 102242

    Keith Ip
    Expand Collapse

    文章:
    3
    讚:
    0
    我校將利用學生的中五級期終考試、中六級的平時分及中六級的畢業試成績計算總分,並用作計算 Percentile 以供 JUPAS Academic Performance 用,請問如何寫SQL 以得出以下結果:

    HKID,Last Name,First Name,Class,CLASSNO,Subject Code,SUBJECT,S5T2A3 System Score,S6T1A1 System Score,S6T1A3 System Score

    謝謝。
     
    #1 Keith Ip, 2012-02-13
  2. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    老師請參考以下的 SQL 語句 或 THIS

    select
    b.hkid 'HKID',
    left(b.ENNAME, locate(b.ENNAME, ' ',1)-1) 'Last Name',
    substr(b.ENNAME, locate(b.ENNAME, ' ',1)+1) 'First Name',
    b.CLASSCODE 'Class',
    b.CLASSNO 'Class No',
    a.subjcode 'Subject Code',
    d.en_DES 'Subject',
    a2.SYSSCORE 'S5T2A3 System Score',
    a.SYSSCORE 'S6T1A1 System Score',
    a1.SYSSCORE 'S6T1A3 System Score'
    from wsadmin.VW_STU_LATESTSTUDENT b
    join tb_asr_subjassessdata a
    on a.suid = b.suid and a.stuid = b.stuid and a.schyear = b.schyear and a.TIMESEQ = 1101
    join wsadmin.TB_HSE_COMMON d
    on a.SUID = d.SUID and d.TB_ID = 'SBJ' and a.SUBJCODE = d.CODE_ID
    left outer join tb_asr_subjassessdata a1
    on a1.suid = a.suid and a1.stuid = a.stuid and a1.schyear = a.schyear and a1.SUBJCODE = a.SUBJCODE and a1.TIMESEQ = 1103
    left outer join tb_asr_subjassessdata a2
    on a2.suid = a.suid and a2.stuid = a.stuid and a2.schyear = a.schyear-1 and a2.SUBJCODE = a.SUBJCODE and a2.TIMESEQ = 1203
    where b.CLASSLVL = 'S6' and b.SCHYEAR = ?
    order by b.SCHLVL, b.SCHSESS, b.CLASSLVL, b.CLASSCODE, b.CLASSNO, a.subjcode

    註: subjects 是以s6t1a1為主
     
    #2 edb-石頭, 2012-02-13