SQL 計算某幾科總分的進步生sql

本文由 KC 在 2020-06-01 發表於 "WebSAMS 討論區" 討論區

  1. 10517336

    KC
    Expand Collapse

    文章:
    22
    讚:
    0
    你好:
    因停課關係我校只想計算上,下學期中英數常主科總分進步2分及操行是B以上的進步生,想請教如何寫SQL?謝謝
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    124
    讚:
    0
    你好

    可以參考以下SQL

    select
    a.classcode,
    a.classno,
    a.chname,
    d.ch_des,
    b.omclass '1100',
    e.overcondgradeconversioncompcode 'T1 Conduct',
    c.omclass '1200',
    f.overcondgradeconversioncompcode 'T2 Conduct',
    (b.omclass - c.omclass) 'Diff'
    from wsadmin.vw_stu_lateststudent a
    left outer join wsadmin.tb_asr_subjassessdata b
    on a.suid=b.suid and a.schlvl=b.schlevel and a.schsess=b.schsession and a.classlvl=b.classlevel and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=1100 and b.subjcode in ('080', '165', '280', '205')
    left outer join wsadmin.tb_asr_subjassessdata c
    on a.suid=c.suid and a.schlvl=c.schlevel and a.schsess=c.schsession and a.classlvl=c.classlevel and a.schyear=c.schyear and a.stuid=c.stuid and b.subjcode=c.subjcode and c.timeseq=1200
    left outer join wsadmin.tb_hse_common d
    on a.suid=d.suid and b.subjcode=d.code_id and d.tb_id='SBJ'
    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 = 1100 and e.overcondgradeconversioncompcode in ('A','A+', 'A-', 'B+')
    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 = 1200 and f.overcondgradeconversioncompcode in ('A','A+', 'A-', 'B+')
    where a.schyear=? and a.classlvl=? and (Diff is not null and Diff>0)
    order by a.schlvl, a.schsess, a.classlvl, a.classcode, d.ch_des, 7 desc, a.classno


    參數:a.schyear=學年(e.g.2009)、a.classlvl=級別(s1=中一、p1=小一)
     
    #2 edb-catherinewschan, 2020-06-02
  3. 10517336

    KC
    Expand Collapse

    文章:
    22
    讚:
    0
    想請教如只計算中,英文科某幾份分卷(如中文:01, 02), 英文(01. 03, 04)及數常總分,應如何改寫?
     
  4. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    124
    讚:
    0
    你好,

    只需要把
    1, tb_asr_subjassessdata 改為 tb_asr_subjcomassessdata
    2, 再加上 "and b.subjcompcode in ('01','02')" 的分卷代碼條件

    select
    a.classcode,
    a.classno,
    a.chname,
    d.ch_des,
    b.omclass '1100',
    e.overcondgradeconversioncompcode 'T1 Conduct',
    c.omclass '1200',
    f.overcondgradeconversioncompcode 'T2 Conduct',
    (b.omclass - c.omclass) 'Diff'
    from wsadmin.vw_stu_lateststudent a
    left outer join wsadmin.tb_asr_subjcomassessdata b
    on a.suid=b.suid and a.schlvl=b.schlevel and a.schsess=b.schsession and a.classlvl=b.classlevel and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=1100 and b.subjcode in ('080', '165', '280', '205') and b.subjcompcode in ('01','02')
    left outer join wsadmin.tb_asr_subjcomassessdata c
    on a.suid=c.suid and a.schlvl=c.schlevel and a.schsess=c.schsession and a.classlvl=c.classlevel and a.schyear=c.schyear and a.stuid=c.stuid and b.subjcode=c.subjcode and b.subjcompcode=c.subjcompcode and c.timeseq=1200
    left outer join wsadmin.tb_hse_common d
    on a.suid=d.suid and b.subjcode=d.code_id and d.tb_id='SBJ'
    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 = 1100 and e.overcondgradeconversioncompcode in ('A','A+', 'A-', 'B+')
    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 = 1200 and f.overcondgradeconversioncompcode in ('A','A+', 'A-', 'B+')
    where a.schyear=? and a.classlvl=? and (Diff is not null and Diff>0)
    order by a.schlvl, a.schsess, a.classlvl, a.classcode, d.ch_des, 7 desc, a.classno
     
    #4 edb-catherinewschan, 2020-07-22