SQL 比較今學年上學期(S4 T1)及上學年下學期(S3 T2)中英數通的standard score

本文由 JOHNTSE3 在 2021-04-12 發表於 "WebSAMS 討論區" 討論區

標籤:
  1. 57391345

    JOHNTSE3
    Expand Collapse

    文章:
    2
    讚:
    0
    請問如何比較今學年上學期(S4 T1)及上學年下學期(S3 T2)中英數通的standard score? Thank you!
     
    #1 JOHNTSE3, 2021-04-12
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    你好,

    可以參考以下SQL

    select
    a.SCHYEAR '學年',
    d.TIMESEQ '上學期考試',
    a.classlvl'級別',
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    b.CHNAME '姓名',
    c.SCHYEAR '1200_學年',
    c1.SYSSCORE '1200_中文',
    c2.SYSSCORE '1200_英文',
    c3.SYSSCORE '1200_數學',
    c4.SYSSCORE '1200_通識',
    d.SCHYEAR '1100_學年',
    d1.SYSSCORE '1100_中文',
    d2.SYSSCORE '1100_英文',
    d3.SYSSCORE '1100_數學',
    d4.SYSSCORE '1100_通識',
    convert(numeric(10,2), avg(c.SYSSCORE)) '1200_考試分',
    convert(numeric(10,2), avg(d.SYSSCORE)) '1100_考試分',
    convert(numeric(10,2), avg(d.SYSSCORE)-avg(c.SYSSCORE)) '1100_考試分-1200_考試分'
    from wsadmin.VW_STU_LATESTSTUSCHREC a
    join wsadmin.TB_STU_STUDENT b
    on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = ?
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA c
    on a.SUID = c.SUID and a.STUID = c.STUID and c.SUBJCODE in ('080','165','280','265') and
    (a.SCHYEAR-1 = c.SCHYEAR) and c.TIMESEQ = '1200'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA c1
    on a.SUID = c1.SUID and a.STUID = c1.STUID and c1.SUBJCODE ='080' and
    (a.SCHYEAR-1 = c1.SCHYEAR) and c1.TIMESEQ = '1200'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA c2
    on a.SUID = c2.SUID and a.STUID = c2.STUID and c2.SUBJCODE ='165' and
    (a.SCHYEAR-1 = c2.SCHYEAR) and c2.TIMESEQ = '1200'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA c3
    on a.SUID = c3.SUID and a.STUID = c3.STUID and c3.SUBJCODE ='280' and
    (a.SCHYEAR-1 = c3.SCHYEAR) and c3.TIMESEQ = '1200'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA c4
    on a.SUID = c4.SUID and a.STUID = c4.STUID and c4.SUBJCODE ='265' and
    (a.SCHYEAR-1 = c4.SCHYEAR) and c4.TIMESEQ = '1200'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA d
    on a.SUID = d.SUID and a.STUID = d.STUID and d.SUBJCODE in ('080','165','280','265') and
    a.SCHYEAR = d.SCHYEAR and d.TIMESEQ = '1100'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA d1
    on a.SUID = d1.SUID and a.STUID = d1.STUID and d1.SUBJCODE ='080' and
    a.SCHYEAR = d1.SCHYEAR and d1.TIMESEQ = '1100'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA d2
    on a.SUID = d2.SUID and a.STUID = d2.STUID and d2.SUBJCODE ='165' and
    a.SCHYEAR = d2.SCHYEAR and d2.TIMESEQ = '1100'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA d3
    on a.SUID = d3.SUID and a.STUID = d3.STUID and d3.SUBJCODE ='280' and
    a.SCHYEAR = d3.SCHYEAR and d3.TIMESEQ = '1100'
    left outer join wsadmin.TB_ASR_SUBJASSESSDATA d4
    on a.SUID = d4.SUID and a.STUID = d4.STUID and d4.SUBJCODE ='265' and
    a.SCHYEAR = d4.SCHYEAR and d4.TIMESEQ = '1100'
    where a.classlvl in ('S3','S4')
    group by a.SCHYEAR,d.TIMESEQ ,a.classlvl, a.CLASSCODE, a.CLASSNO, b.CHNAME, c.SCHYEAR, d.SCHYEAR,
    c1.SYSSCORE,c2.SYSSCORE,c3.SYSSCORE,c4.SYSSCORE,d1.SYSSCORE,d2.SYSSCORE,d3.SYSSCORE,d4.SYSSCORE
    order by 19 desc
     
    #2 edb-catherinewschan, 2021-04-14