SQL 跨年比較學生考試分數之差

本文由 tr-wong_sir 在 2020-03-18 發表於 "WebSAMS 討論區" 討論區

  1. 55019624

    tr-wong_sir
    Expand Collapse

    文章:
    64
    讚:
    0
    本校原先已有一個sql用來比較同一年度同一學生的
    上學期與下學期的考試分數之差

    現希望有一個sql

    可計算學生跨年度:
    例如2019年度上學期考試(1100) 2018年度下學期考試(1200)

    中英數常四科分數總和的平均分之差。

    原有的sql 如下:


    select
    a.SCHYEAR '
    學年',
    c.TIMESEQ '
    上學期測考',
    a.classlvl'
    級別',
    a.CLASSCODE '
    班別',
    a.CLASSNO '
    班號',
    b.CHNAME '
    姓名',
    convert(numeric(10,2), avg(c.SYSSCORE)) '
    測驗分',
    convert(numeric(10,2), avg(d.SYSSCORE)) '
    考試分',

    convert(numeric(10,2), avg(d.SYSSCORE)-avg(c.SYSSCORE)) '
    考試分-測驗分'


    from VW_STU_LATESTSTUSCHREC a

    join TB_STU_STUDENT b
    on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = ?

    left outer join TB_ASR_SUBJASSESSDATA c
    on a.SUID = c.SUID and a.STUID = c.STUID and c.SUBJCODE in ('080','165','280','205') and
    a.SCHYEAR = c.SCHYEAR and c.TIMESEQ = '1101'

    left outer join TB_ASR_SUBJASSESSDATA d
    on a.SUID = d.SUID and a.STUID = d.STUID and d.SUBJCODE in ('080','165','280','205') and
    a.SCHYEAR = d.SCHYEAR and d.TIMESEQ = '1102'

    where a.classlvl in ('p4','p5','p6') AND a.classno <50
    group by a.SCHYEAR,c.TIMESEQ ,a.classlvl, a.CLASSCODE, a.CLASSNO, b.CHNAME
    order by 9 desc
     
    #1 tr-wong_sir, 2020-03-18
  2. 57873296

    edb-rn
    Expand Collapse

    文章:
    231
    讚:
    0
    老師,請參考以下,

    select
    a.SCHYEAR '學年',
    d.TIMESEQ '上學期考試',
    a.classlvl'級別',
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    b.CHNAME '姓名',
    c.SCHYEAR '1200_學年',
    d.SCHYEAR '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 VW_STU_LATESTSTUSCHREC a
    join TB_STU_STUDENT b
    on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = ?
    left outer join TB_ASR_SUBJASSESSDATA c
    on a.SUID = c.SUID and a.STUID = c.STUID and c.SUBJCODE in ('080','165','280','205') and
    (a.SCHYEAR-1 = c.SCHYEAR) and c.TIMESEQ = '1200'
    left outer join TB_ASR_SUBJASSESSDATA d
    on a.SUID = d.SUID and a.STUID = d.STUID and d.SUBJCODE in ('080','165','280','205') and
    a.SCHYEAR = d.SCHYEAR and d.TIMESEQ = '1100'
    where a.classlvl in ('p4','p5','p6') AND a.classno <50
    group by a.SCHYEAR,d.TIMESEQ ,a.classlvl, a.CLASSCODE, a.CLASSNO, b.CHNAME, c.SCHYEAR, d.SCHYEAR
    order by 11 desc
     
  3. 55019624

    tr-wong_sir
    Expand Collapse

    文章:
    64
    讚:
    0

    謝謝協助

    可否在上表

    顯示
    2018年度及2019年度,中、英、數、常各科的分數



     
    #3 tr-wong_sir, 2020-03-20
  4. 55019624

    tr-wong_sir
    Expand Collapse

    文章:
    64
    讚:
    0
     
    #4 tr-wong_sir, 2020-03-20
  5. 57873296

    edb-rn
    Expand Collapse

    文章:
    231
    讚:
    0
    老師,請參考以下,

    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 VW_STU_LATESTSTUSCHREC a
    join TB_STU_STUDENT b
    on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = ?
    left outer join TB_ASR_SUBJASSESSDATA c
    on a.SUID = c.SUID and a.STUID = c.STUID and c.SUBJCODE in ('080','165','280','205') and
    (a.SCHYEAR-1 = c.SCHYEAR) and c.TIMESEQ = '1200'
    left outer join 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 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 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 TB_ASR_SUBJASSESSDATA c4
    on a.SUID = c4.SUID and a.STUID = c4.STUID and c4.SUBJCODE ='205' and
    (a.SCHYEAR-1 = c4.SCHYEAR) and c4.TIMESEQ = '1200'

    left outer join TB_ASR_SUBJASSESSDATA d
    on a.SUID = d.SUID and a.STUID = d.STUID and d.SUBJCODE in ('080','165','280','205') and
    a.SCHYEAR = d.SCHYEAR and d.TIMESEQ = '1100'
    left outer join 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 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 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 TB_ASR_SUBJASSESSDATA d4
    on a.SUID = d4.SUID and a.STUID = d4.STUID and d4.SUBJCODE ='205' and
    a.SCHYEAR = d4.SCHYEAR and d4.TIMESEQ = '1100'

    where a.classlvl in ('p4','p5','p6') AND a.classno <50
    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
     
  6. 55019624

    tr-wong_sir
    Expand Collapse

    文章:
    64
    讚:
    0
    謝謝協助
    已能取得名單
     
    #6 tr-wong_sir, 2020-03-25