用sql抽取學生的操行紀錄

本文由 準備 在 2009-06-28 發表於 "WebSAMS 討論區" 討論區

  1. 10372659

    準備
    Expand Collapse

    文章:
    34
    讚:
    0

    本人想利用sql抽取學生的操行細項的紀錄,結果如下:

    班別   學號       姓名       紀律    禮貌     好學    盡責
     1A     01       陳 一        A        A-       A       A-  
     1A     01       陳二         B        B-       B       B 

    謝謝!!!!

     

     

     
  2. 56127239

    EDB-Wayne
    Expand Collapse

    文章:
    0
    讚:
    0

    相信例子中的兩位學生的學號應是01及02?請試試下列sql:

    select
       a.classlvl '級別',
       a.classcode '班別',
       a.classno '學號',
       a.enname '英文姓名',
       a.chname '中文姓名',
       b.overcondgradeconversioncompcode '操行',
       c.gradeconversioncompcode '紀律',
       d.gradeconversioncompcode '禮貌',
       e.gradeconversioncompcode '好學',
       f.gradeconversioncompcode '盡責'
    from vw_stu_lateststudent a
    left outer join tb_asr_studassessdata 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=?
    left outer join (select c1.suid, c1.schlevel, c1.schsession, c1.classlevel, c1.schyear, c1.stuid, c1.timeseq, d1.namechi, c1.gradeconversioncompcode
                     from tb_asr_studcondcompdata c1
                     join tb_asr_condcomp d1
                        on c1.suid=d1.suid and c1.schlevel=d1.schlevel and c1.schsession=d1.schsession and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='紀律') 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 c.timeseq=b.timeseq
    left outer join (select c1.suid, c1.schlevel, c1.schsession, c1.classlevel, c1.schyear, c1.stuid, c1.timeseq, d1.namechi, c1.gradeconversioncompcode
                     from tb_asr_studcondcompdata c1
                     join tb_asr_condcomp d1
                        on c1.suid=d1.suid and c1.schlevel=d1.schlevel and c1.schsession=d1.schsession and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') d
       on a.suid=d.suid and a.schlvl=d.schlevel and a.schsess=d.schsession and a.classlvl=d.classlevel and a.schyear=d.schyear and a.stuid=d.stuid and d.timeseq=b.timeseq
    left outer join (select c1.suid, c1.schlevel, c1.schsession, c1.classlevel, c1.schyear, c1.stuid, c1.timeseq, d1.namechi, c1.gradeconversioncompcode
                     from tb_asr_studcondcompdata c1
                     join tb_asr_condcomp d1
                        on c1.suid=d1.suid and c1.schlevel=d1.schlevel and c1.schsession=d1.schsession and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='好學') e
       on a.suid=e.suid and a.schlvl=e.schlevel and a.schsess=e.schsession and a.classlvl=e.classlevel and a.schyear=e.schyear and a.stuid=e.stuid and e.timeseq=b.timeseq
    left outer join (select c1.suid, c1.schlevel, c1.schsession, c1.classlevel, c1.schyear, c1.stuid, c1.timeseq, d1.namechi, c1.gradeconversioncompcode
                     from tb_asr_studcondcompdata c1
                     join tb_asr_condcomp d1
                        on c1.suid=d1.suid and c1.schlevel=d1.schlevel and c1.schsession=d1.schsession and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='盡責') f
       on a.suid=f.suid and a.schlvl=f.schlevel and a.schsess=f.schsession and a.classlvl=f.classlevel and a.schyear=f.schyear and a.stuid=f.stuid and f.timeseq=b.timeseq
    where a.schyear=?
    order by a.schlvl, a.schsess, a.classlvl, a.classcode, a.classno, a.enname

    參數:a.schyear=學年,b.timeseq=1101(T1A1), 1200(T2), 1000(ANNUAL)

    另外,使用學生成績模組>報告>4.學生考績資料中的R-ASR024報表亦可。

     
    #2 EDB-Wayne, 2009-06-29
  3. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    本校的操行設有分卷:包括學習、服務、家課、禮貌 、友愛及守規,想抽出全校學生上學期、下學期、上下學期操行相差 全年的操行平均值
    構思如下:

    年級 班別 班號 姓名 NAME 上學期操行平均 下學期操行平均 上下學期操行相差 全年操行平均
     
    #3 WEB-STE, 2014-06-26
  4. 57678674

    edb-aswz
    Expand Collapse

    文章:
    51
    讚:
    0
    老師請參考下列code:

    select
    a.classlvl '級別',
    a.classcode '班別',
    a.classno '學號',
    a.chname '中文姓名',
    a.enname '英文姓名',
    b.overcondgradeconversioncompcode '上學期操行平均',
    b2.overcondgradeconversioncompcode '下學期操行平均',
    CONVERT(NUMERIC(10,2),ISNULL(CONVERT(NUMERIC(10,2),b2.overcondgradeconversioncompcode),0) - ISNULL(CONVERT(NUMERIC(10,2),b.overcondgradeconversioncompcode),0)) '上下學期操行相差',
    CONVERT(NUMERIC(10,2),(ISNULL(CONVERT(NUMERIC(10,2),b.overcondgradeconversioncompcode),0) + ISNULL(CONVERT(NUMERIC(10,2),b2.overcondgradeconversioncompcode),0))/2) '全年操行平均'

    from vw_stu_lateststudent a
    left outer join tb_asr_studassessdata b
    on a.suid=b.suid and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=?
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c
    on a.suid=c.suid and a.schyear=c.schyear and a.stuid=c.stuid and c.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d
    on a.suid=d.suid and a.schyear=d.schyear and a.stuid=d.stuid and d.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e
    on a.suid=e.suid and a.schyear=e.schyear and a.stuid=e.stuid and e.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f
    on a.suid=f.suid and a.schyear=f.schyear and a.stuid=f.stuid and f.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g
    on a.suid=g.suid and a.schyear=g.schyear and a.stuid=g.stuid and g.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h
    on a.suid=h.suid and a.schyear=h.schyear and a.stuid=h.stuid and h.timeseq=b.timeseq

    left outer join tb_asr_studassessdata b2
    on a.suid=b2.suid and a.schyear=b2.schyear and a.stuid=b2.stuid and b2.timeseq=?
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c2
    on a.suid=c2.suid and a.schyear=c2.schyear and a.stuid=c2.stuid and c2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d2
    on a.suid=d2.suid and a.schyear=d2.schyear and a.stuid=d2.stuid and d2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e2
    on a.suid=e2.suid and a.schyear=e2.schyear and a.stuid=e2.stuid and e2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f2
    on a.suid=f2.suid and a.schyear=f2.schyear and a.stuid=f2.stuid and f2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g2
    on a.suid=g2.suid and a.schyear=g2.schyear and a.stuid=g2.stuid and g2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h2
    on a.suid=h2.suid and a.schyear=h2.schyear and a.stuid=h2.stuid and h2.timeseq=b2.timeseq

    where a.schyear=?
    order by a.schlvl, a.schsess, a.classlvl, a.classcode, a.classno, a.enname

    老師留意, 輸入的分必須是數字, 非等級.
     
    #4 edb-aswz, 2014-06-27
  5. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    對不起!!如果操行分卷是以等級輸入,如家課 :A- ,可以怎樣處理!!謝謝!!
     
    #5 WEB-STE, 2014-06-28
  6. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    對不起!!如果操行分卷是以等級輸入,如家課 :A- ,可以怎樣處理!本校的操行設有分卷:包括學習、服務、家課、禮貌 、友愛及守規,想抽出全校學生上學期、下學期、全年的操行平均值
    構思如下:

    年級 班別 班號 姓名 NAME 上學期操行平均 下學期操行平均 全年操行平均

    煩請協助制作有關報表,謝謝!!
     
    #6 WEB-STE, 2014-07-02
  7. 57686791

    edb-marmel
    Expand Collapse

    文章:
    187
    讚:
    0
    回覆 6# Stephen

    select
    a.classlvl '級別',
    a.classcode '班別',
    a.classno '學號',
    a.chname '中文姓名',
    a.enname '英文姓名',
    b.overcondgradeconversioncompcode '上學期操行平均',
    b2.overcondgradeconversioncompcode '下學期操行平均',
    (((case when TRIM(b.overcondgradeconversioncompcode)='A' then CAST('5' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='A-' then CAST('4.7' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='B+' then CAST('4.3' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='B' then CAST('4' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='B-' then CAST('3.7' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='C+' then CAST('3.3' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='C' then CAST('3' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='C-' then CAST('2.7' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='D+' then CAST('2.3' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='D' then CAST('2' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='D-' then CAST('1.7' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='E+' then CAST('0.7' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='E' then CAST('1' AS DOUBLE)
    else CAST('0' AS DOUBLE) end)+ (case when TRIM(b2.overcondgradeconversioncompcode)='A' then CAST('5' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='A-' then CAST('4.7' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='B+' then CAST('4.3' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='B' then CAST('4' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='B-' then CAST('3.7' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='C+' then CAST('3.3' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='C' then CAST('3' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='C-' then CAST('2.7' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='D+' then CAST('2.3' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='D' then CAST('2' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='D-' then CAST('1.7' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='E+' then CAST('0.7' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='E' then CAST('1' AS DOUBLE)
    else CAST('0' AS DOUBLE) end))/2)
    '全年操行平均分分數',
    (case when TRIM(全年操行平均分分數)>=5 then CAST('A' AS CHAR)
    when TRIM(全年操行平均分分數)>=4.7 then CAST('A-' AS CHAR)
    when TRIM(全年操行平均分分數)>=4.3 then CAST('B+' AS CHAR)
    when TRIM(全年操行平均分分數)>=4 then CAST('B' AS CHAR)
    when TRIM(全年操行平均分分數)>=3.7 then CAST('B-' AS CHAR)
    when TRIM(全年操行平均分分數)>=3.3 then CAST('C+' AS CHAR)
    when TRIM(全年操行平均分分數)>=3 then CAST('C' AS CHAR)
    when TRIM(全年操行平均分分數)>=2.7 then CAST('C-' AS CHAR)
    when TRIM(全年操行平均分分數)>=2.3 then CAST('D+' AS CHAR)
    when TRIM(全年操行平均分分數)>=2 then CAST('D' AS CHAR)
    when TRIM(全年操行平均分分數)>=1.7 then CAST('D-' AS CHAR)
    when TRIM(全年操行平均分分數)>=1.3 then CAST('E+' AS CHAR)
    when TRIM(全年操行平均分分數)>=1 then CAST('E' AS CHAR)
    else CAST('F' AS CHAR) end) '全年操行平均分'

    from vw_stu_lateststudent a
    left outer join tb_asr_studassessdata b
    on a.suid=b.suid and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=?
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c
    on a.suid=c.suid and a.schyear=c.schyear and a.stuid=c.stuid and c.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d
    on a.suid=d.suid and a.schyear=d.schyear and a.stuid=d.stuid and d.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e
    on a.suid=e.suid and a.schyear=e.schyear and a.stuid=e.stuid and e.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f
    on a.suid=f.suid and a.schyear=f.schyear and a.stuid=f.stuid and f.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g
    on a.suid=g.suid and a.schyear=g.schyear and a.stuid=g.stuid and g.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h
    on a.suid=h.suid and a.schyear=h.schyear and a.stuid=h.stuid and h.timeseq=b.timeseq

    left outer join tb_asr_studassessdata b2
    on a.suid=b2.suid and a.schyear=b2.schyear and a.stuid=b2.stuid and b2.timeseq=?
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c2
    on a.suid=c2.suid and a.schyear=c2.schyear and a.stuid=c2.stuid and c2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d2
    on a.suid=d2.suid and a.schyear=d2.schyear and a.stuid=d2.stuid and d2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e2
    on a.suid=e2.suid and a.schyear=e2.schyear and a.stuid=e2.stuid and e2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f2
    on a.suid=f2.suid and a.schyear=f2.schyear and a.stuid=f2.stuid and f2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g2
    on a.suid=g2.suid and a.schyear=g2.schyear and a.stuid=g2.stuid and g2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h2
    on a.suid=h2.suid and a.schyear=h2.schyear and a.stuid=h2.stuid and h2.timeseq=b2.timeseq

    where a.schyear=?
    order by a.schlvl, a.schsess, a.classlvl, a.classcode, a.classno, a.enname

    若學校積分等級計法不同,請將紅字的計分級別進行更新。以下或許學校需更新有A+的資料:
    when TRIM(b.overcondgradeconversioncompcode)='A+' then CAST('5.3' AS DOUBLE)

    還有,也將以下的SQL加入:
    when TRIM(b2.overcondgradeconversioncompcode)='A-' then CAST('4.7' AS DOUBLE)
    when TRIM(全年操行平均分分數)>=4.7 then CAST('A+' AS CHAR)

    謝謝!
     
    #7 edb-marmel, 2014-07-10
  8. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    SORRY!!唔得,出現以下結果:
    級別 班別 學號 中文姓名 英文姓名 上學期操行平均 下學期操行平均 全年操行平均分分數 全年操行平均分
    P1 1A 1 陳XX Chan XX 0.0 F
    P1 1A 2 張XX ChangXX 0.0 F
    P1 1A 3 鄭XX ChengXX 0.0 F
     
    #8 WEB-STE, 2014-07-24
  9. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    我未修改前及後都出現上面所說的問題
    修改後:
    select
    a.classlvl '級別',
    a.classcode '班別',
    a.classno '學號',
    a.chname '中文姓名',
    a.enname '英文姓名',
    b.overcondgradeconversioncompcode '上學期操行平均',
    b2.overcondgradeconversioncompcode '下學期操行平均',
    (((case when TRIM(b.overcondgradeconversioncompcode)='A' then CAST('95' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='A-' then CAST('90' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='B+' then CAST('85' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='B' then CAST('80' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='B-' then CAST('75' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='C+' then CAST('70' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='C' then CAST('65' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='C-' then CAST('60' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='D+' then CAST('55' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='D' then CAST('50' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='D-' then CAST('45' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='E+' then CAST('40' AS DOUBLE)
    when TRIM(b.overcondgradeconversioncompcode)='E' then CAST('30' AS DOUBLE)
    else CAST('0' AS DOUBLE) end)+ (case when TRIM(b2.overcondgradeconversioncompcode)='A' then CAST('95' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='A-' then CAST('90' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='B+' then CAST('85' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='B' then CAST('80' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='B-' then CAST('75' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='C+' then CAST('70' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='C' then CAST('65' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='C-' then CAST('60' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='D+' then CAST('55' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='D' then CAST('50' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='D-' then CAST('45' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='E+' then CAST('40' AS DOUBLE)
    when TRIM(b2.overcondgradeconversioncompcode)='E' then CAST('35' AS DOUBLE)
    else CAST('0' AS DOUBLE) end))/2)
    '全年操行平均分分數',
    (case when TRIM(全年操行平均分分數)>=95 then CAST('A' AS CHAR)
    when TRIM(全年操行平均分分數)>=90 then CAST('A-' AS CHAR)
    when TRIM(全年操行平均分分數)>=85 then CAST('B+' AS CHAR)
    when TRIM(全年操行平均分分數)>=80 then CAST('B' AS CHAR)
    when TRIM(全年操行平均分分數)>=75 then CAST('B-' AS CHAR)
    when TRIM(全年操行平均分分數)>=70 then CAST('C+' AS CHAR)
    when TRIM(全年操行平均分分數)>=65 then CAST('C' AS CHAR)
    when TRIM(全年操行平均分分數)>=60 then CAST('C-' AS CHAR)
    when TRIM(全年操行平均分分數)>=55 then CAST('D+' AS CHAR)
    when TRIM(全年操行平均分分數)>=50 then CAST('D' AS CHAR)
    when TRIM(全年操行平均分分數)>=45 then CAST('D-' AS CHAR)
    when TRIM(全年操行平均分分數)>=40 then CAST('E+' AS CHAR)
    when TRIM(全年操行平均分分數)>=35 then CAST('E' AS CHAR)
    else CAST('F' AS CHAR) end) '全年操行平均分'

    from vw_stu_lateststudent a
    left outer join tb_asr_studassessdata b
    on a.suid=b.suid and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=?
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c
    on a.suid=c.suid and a.schyear=c.schyear and a.stuid=c.stuid and c.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d
    on a.suid=d.suid and a.schyear=d.schyear and a.stuid=d.stuid and d.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e
    on a.suid=e.suid and a.schyear=e.schyear and a.stuid=e.stuid and e.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f
    on a.suid=f.suid and a.schyear=f.schyear and a.stuid=f.stuid and f.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g
    on a.suid=g.suid and a.schyear=g.schyear and a.stuid=g.stuid and g.timeseq=b.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h
    on a.suid=h.suid and a.schyear=h.schyear and a.stuid=h.stuid and h.timeseq=b.timeseq

    left outer join tb_asr_studassessdata b2
    on a.suid=b2.suid and a.schyear=b2.schyear and a.stuid=b2.stuid and b2.timeseq=?
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c2
    on a.suid=c2.suid and a.schyear=c2.schyear and a.stuid=c2.stuid and c2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d2
    on a.suid=d2.suid and a.schyear=d2.schyear and a.stuid=d2.stuid and d2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e2
    on a.suid=e2.suid and a.schyear=e2.schyear and a.stuid=e2.stuid and e2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f2
    on a.suid=f2.suid and a.schyear=f2.schyear and a.stuid=f2.stuid and f2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g2
    on a.suid=g2.suid and a.schyear=g2.schyear and a.stuid=g2.stuid and g2.timeseq=b2.timeseq
    left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1
    join tb_asr_condcomp d1
    on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h2
    on a.suid=h2.suid and a.schyear=h2.schyear and a.stuid=h2.stuid and h2.timeseq=b2.timeseq

    where a.schyear=?
    order by a.schlvl, a.schsess, a.classlvl, a.classcode, a.classno, a.enname


    瀏覽附件2543 photo.jpg photo.jpg
     
    #9 WEB-STE, 2014-07-24
  10. 57686791

    edb-marmel
    Expand Collapse

    文章:
    187
    讚:
    0
    本帖最後由 edb-marmel 於 2014-7-31 17:04 編輯

    回覆 9# Stephen


    select a.classlvl '級別', a.classcode '班別', a.classno '學號', a.enname '英文姓名',
    a.chname '中文姓名', (s1.minvalueinclusive + s2.minvalueinclusive + s3.minvalueinclusive + s4.minvalueinclusive + s5.minvalueinclusive + s6.minvalueinclusive)/6 '上學期操行平均分數', (s1b.minvalueinclusive + s2b.minvalueinclusive + s3b.minvalueinclusive + s4b.minvalueinclusive + s5b.minvalueinclusive + s6b.minvalueinclusive)/6 '下學期操行平均分數', ((上學期操行平均分數)+(下學期操行平均分數))/2 '全年操行平均分數', (case when TRIM(全年操行平均分數)>=95 then CAST('A' AS CHAR) when TRIM(全年操行平均分數)>=90 then CAST('A-' AS CHAR) when TRIM(全年操行平均分數)>=85 then CAST('B+' AS CHAR) when TRIM(全年操行平均分數)>=80 then CAST('B' AS CHAR) when TRIM(全年操行平均分數)>=75 then CAST('B-' AS CHAR) when TRIM(全年操行平均分數)>=70 then CAST('C+' AS CHAR) when TRIM(全年操行平均分數)>=65 then CAST('C' AS CHAR) when TRIM(全年操行平均分數)>=60 then CAST('C-' AS CHAR) when TRIM(全年操行平均分數)>=55 then CAST('D+' AS CHAR) when TRIM(全年操行平均分數)>=50 then CAST('D' AS CHAR) when TRIM(全年操行平均分數)>=45 then CAST('D-' AS CHAR) when TRIM(全年操行平均分數)>=40 then CAST('E+' AS CHAR) when TRIM(全年操行平均分數)>=35 then CAST('E' AS CHAR) when TRIM(全年操行平均分數)>=30 then CAST('E-' AS CHAR) else CAST('F' AS CHAR) end) '全年操行平均' from vw_stu_lateststudent a left outer join tb_asr_studassessdata 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=? left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c on a.suid=c.suid and a.schyear=c.schyear and a.stuid=c.stuid and c.timeseq=b.timeseq join tb_asr_gradeconversioncomp s1 on c.gradeconversioncompcode=s1.gradeconversioncompcode and c.gradeconversionseq=s1.gradeconversionseq and s1.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d
    on a.suid=d.suid and a.schyear=d.schyear and a.stuid=d.stuid and d.timeseq=b.timeseq join tb_asr_gradeconversioncomp s2 on d.gradeconversioncompcode=s2.gradeconversioncompcode and d.gradeconversionseq=s2.gradeconversionseq and s2.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e on a.suid=e.suid and a.schyear=e.schyear and a.stuid=e.stuid and e.timeseq=b.timeseq join tb_asr_gradeconversioncomp s3 on e.gradeconversioncompcode=s3.gradeconversioncompcode and e.gradeconversionseq=s3.gradeconversionseq and s3.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f
    on a.suid=f.suid and a.schyear=f.schyear and a.stuid=f.stuid and f.timeseq=b.timeseq
    join tb_asr_gradeconversioncomp s4 on f.gradeconversioncompcode=s4.gradeconversioncompcode and f.gradeconversionseq=s4.gradeconversionseq and s4.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g
    on a.suid=g.suid and a.schyear=g.schyear and a.stuid=g.stuid and g.timeseq=b.timeseq join tb_asr_gradeconversioncomp s5 on g.gradeconversioncompcode=s5.gradeconversioncompcode and g.gradeconversionseq=s5.gradeconversionseq and s5.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h on a.suid=h.suid and a.schyear=h.schyear and a.stuid=h.stuid and h.timeseq=b.timeseq join tb_asr_gradeconversioncomp s6 on h.gradeconversioncompcode=s6.gradeconversioncompcode and h.gradeconversionseq=s6.gradeconversionseq and s6.minvalueinclusive!=null
    left outer join tb_asr_studassessdata b2 on a.suid=b2.suid and a.schlvl=b2.schlevel and a.schsess=b2.schsession and a.classlvl=b2.classlevel and a.schyear=b2.schyear and a.stuid=b2.stuid and b2.timeseq=? left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c2 on a.suid=c2.suid and a.schyear=c2.schyear and a.stuid=c2.stuid and c2.timeseq=b2.timeseq join tb_asr_gradeconversioncomp s1b on c2.gradeconversioncompcode=s1b.gradeconversioncompcode and c2.gradeconversionseq=s1b.gradeconversionseq and s1b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d2
    on a.suid=d2.suid and a.schyear=d2.schyear and a.stuid=d2.stuid and d2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s2b on d2.gradeconversioncompcode=s2b.gradeconversioncompcode and d2.gradeconversionseq=s2b.gradeconversionseq and s2b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e2 on a.suid=e2.suid and a.schyear=e2.schyear and a.stuid=e2.stuid and e2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s3b on e2.gradeconversioncompcode=s3b.gradeconversioncompcode and e2.gradeconversionseq=s3b.gradeconversionseq and s3b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f2 on a.suid=f2.suid and a.schyear=f2.schyear and a.stuid=f2.stuid and f2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s4b on f2.gradeconversioncompcode=s4b.gradeconversioncompcode and f2.gradeconversionseq=s4b.gradeconversionseq and s4b.minvalueinclusive!=null left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g2 on a.suid=g2.suid and a.schyear=g2.schyear and a.stuid=g2.stuid and g2.timeseq=b2.timeseq
    join tb_asr_gradeconversioncomp s5b on g2.gradeconversioncompcode=s5b.gradeconversioncompcode and g2.gradeconversionseq=s5b.gradeconversionseq and s5b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h2
    on a.suid=h2.suid and a.schyear=h2.schyear and a.stuid=h2.stuid and h2.timeseq=b2.timeseq join tb_asr_gradeconversioncomp s6b on h2.gradeconversioncompcode=s6b.gradeconversioncompcode and h2.gradeconversionseq=s6b.gradeconversionseq and s6b.minvalueinclusive!=null
    where a.schyear=? order by a.schlvl, a.schsess, a.classlvl, a.classcode, a.classno, a.enname



    上學期操行平均分數: (上學期'學習'分卷 + 上學期'服務'分卷 + 上學期'家課'分卷 + 上學期'禮貌'分卷 + 上學期'友愛'分卷 + 上學期'守規'分卷) / 6
    下學期操行平均分數: (下學期'學習'分卷 + 下學期'服務'分卷 + 下學期'家課'分卷 + 下學期'禮貌'分卷 + 下學期'友愛'分卷 + 下學期'守規'分卷) / 6
    全年操行平均分數: (上學期操行平均分數 + 下學期操行平均分數) / 2
    全年操行平均 : 按「積分/等級互換表」,以互換全年操行平均等級

    謝謝!
     
    #10 edb-marmel, 2014-07-31
  11. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    請問這些是否需要 加入SQL內:
    上學期操行平均分數: (上學期'學習'分卷 + 上學期'服務'分卷 + 上學期'家課'分卷 + 上學期'禮貌'分卷 + 上學期'友愛'分卷 + 上學期'守規'分卷) / 6
    下學期操行平均分數: (下學期'學習'分卷 + 下學期'服務'分卷 + 下學期'家課'分卷 + 下學期'禮貌'分卷 + 下學期'友愛'分卷 + 下學期'守規'分卷) / 6
    全年操行平均分數: (上學期操行平均分數 + 下學期操行平均分數) / 2
    全年操行平均 : 按「積分/等級互換表」,以互換全年操行平均等級
     
    #11 WEB-STE, 2014-08-01
  12. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    請問是否需要以下的才加入SQL內
    select a.classlvl '級別', a.classcode '班別', a.classno '學號', a.enname '英文姓名',
    a.chname '中文姓名', (s1.minvalueinclusive + s2.minvalueinclusive + s3.minvalueinclusive + s4.minvalueinclusive + s5.minvalueinclusive + s6.minvalueinclusive)/6 '上學期操行平均分數', (s1b.minvalueinclusive + s2b.minvalueinclusive + s3b.minvalueinclusive + s4b.minvalueinclusive + s5b.minvalueinclusive + s6b.minvalueinclusive)/6 '下學期操行平均分數', ((上學期操行平均分數)+(下學期操行平均分數))/2 '全年操行平均分數', (case when TRIM(全年操行平均分數)>=95 then CAST('A' AS CHAR) when TRIM(全年操行平均分數)>=90 then CAST('A-' AS CHAR) when TRIM(全年操行平均分數)>=85 then CAST('B+' AS CHAR) when TRIM(全年操行平均分數)>=80 then CAST('B' AS CHAR) when TRIM(全年操行平均分數)>=75 then CAST('B-' AS CHAR) when TRIM(全年操行平均分數)>=70 then CAST('C+' AS CHAR) when TRIM(全年操行平均分數)>=65 then CAST('C' AS CHAR) when TRIM(全年操行平均分數)>=60 then CAST('C-' AS CHAR) when TRIM(全年操行平均分數)>=55 then CAST('D+' AS CHAR) when TRIM(全年操行平均分數)>=50 then CAST('D' AS CHAR) when TRIM(全年操行平均分數)>=45 then CAST('D-' AS CHAR) when TRIM(全年操行平均分數)>=40 then CAST('E+' AS CHAR) when TRIM(全年操行平均分數)>=35 then CAST('E' AS CHAR) when TRIM(全年操行平均分數)>=30 then CAST('E-' AS CHAR) else CAST('F' AS CHAR) end) '全年操行平均' from vw_stu_lateststudent a left outer join tb_asr_studassessdata 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=? left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c on a.suid=c.suid and a.schyear=c.schyear and a.stuid=c.stuid and c.timeseq=b.timeseq join tb_asr_gradeconversioncomp s1 on c.gradeconversioncompcode=s1.gradeconversioncompcode and c.gradeconversionseq=s1.gradeconversionseq and s1.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d
    on a.suid=d.suid and a.schyear=d.schyear and a.stuid=d.stuid and d.timeseq=b.timeseq join tb_asr_gradeconversioncomp s2 on d.gradeconversioncompcode=s2.gradeconversioncompcode and d.gradeconversionseq=s2.gradeconversionseq and s2.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e on a.suid=e.suid and a.schyear=e.schyear and a.stuid=e.stuid and e.timeseq=b.timeseq join tb_asr_gradeconversioncomp s3 on e.gradeconversioncompcode=s3.gradeconversioncompcode and e.gradeconversionseq=s3.gradeconversionseq and s3.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f
    on a.suid=f.suid and a.schyear=f.schyear and a.stuid=f.stuid and f.timeseq=b.timeseq
    join tb_asr_gradeconversioncomp s4 on f.gradeconversioncompcode=s4.gradeconversioncompcode and f.gradeconversionseq=s4.gradeconversionseq and s4.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g
    on a.suid=g.suid and a.schyear=g.schyear and a.stuid=g.stuid and g.timeseq=b.timeseq join tb_asr_gradeconversioncomp s5 on g.gradeconversioncompcode=s5.gradeconversioncompcode and g.gradeconversionseq=s5.gradeconversionseq and s5.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h on a.suid=h.suid and a.schyear=h.schyear and a.stuid=h.stuid and h.timeseq=b.timeseq join tb_asr_gradeconversioncomp s6 on h.gradeconversioncompcode=s6.gradeconversioncompcode and h.gradeconversionseq=s6.gradeconversionseq and s6.minvalueinclusive!=null
    left outer join tb_asr_studassessdata b2 on a.suid=b2.suid and a.schlvl=b2.schlevel and a.schsess=b2.schsession and a.classlvl=b2.classlevel and a.schyear=b2.schyear and a.stuid=b2.stuid and b2.timeseq=? left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c2 on a.suid=c2.suid and a.schyear=c2.schyear and a.stuid=c2.stuid and c2.timeseq=b2.timeseq join tb_asr_gradeconversioncomp s1b on c2.gradeconversioncompcode=s1b.gradeconversioncompcode and c2.gradeconversionseq=s1b.gradeconversionseq and s1b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d2
    on a.suid=d2.suid and a.schyear=d2.schyear and a.stuid=d2.stuid and d2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s2b on d2.gradeconversioncompcode=s2b.gradeconversioncompcode and d2.gradeconversionseq=s2b.gradeconversionseq and s2b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e2 on a.suid=e2.suid and a.schyear=e2.schyear and a.stuid=e2.stuid and e2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s3b on e2.gradeconversioncompcode=s3b.gradeconversioncompcode and e2.gradeconversionseq=s3b.gradeconversionseq and s3b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f2 on a.suid=f2.suid and a.schyear=f2.schyear and a.stuid=f2.stuid and f2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s4b on f2.gradeconversioncompcode=s4b.gradeconversioncompcode and f2.gradeconversionseq=s4b.gradeconversionseq and s4b.minvalueinclusive!=null left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g2 on a.suid=g2.suid and a.schyear=g2.schyear and a.stuid=g2.stuid and g2.timeseq=b2.timeseq
    join tb_asr_gradeconversioncomp s5b on g2.gradeconversioncompcode=s5b.gradeconversioncompcode and g2.gradeconversionseq=s5b.gradeconversionseq and s5b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h2
    on a.suid=h2.suid and a.schyear=h2.schyear and a.stuid=h2.stuid and h2.timeseq=b2.timeseq join tb_asr_gradeconversioncomp s6b on h2.gradeconversioncompcode=s6b.gradeconversioncompcode and h2.gradeconversionseq=s6b.gradeconversionseq and s6b.minvalueinclusive!=null
    where a.schyear=? order by a.schlvl, a.schsess, a.classlvl, a.classcode, a.classno, a.enname
     
    #12 WEB-STE, 2014-08-01
  13. 57686791

    edb-marmel
    Expand Collapse

    文章:
    187
    讚:
    0
    回覆 12# Stephen

    SQL 已經包含了以下的程式,以下的文字程式只供解釋及參考:
    上學期操行平均分數: (上學期'學習'分卷 + 上學期'服務'分卷 + 上學期'家課'分卷 + 上學期'禮貌'分卷 + 上學期'友愛'分卷 + 上學期'守規'分卷) / 6
    SQL: (s1.minvalueinclusive + s2.minvalueinclusive + s3.minvalueinclusive + s4.minvalueinclusive + s5.minvalueinclusive + s6.minvalueinclusive)/6

    下學期操行平均分數: (下學期'學習'分卷 + 下學期'服務'分卷 + 下學期'家課'分卷 + 下學期'禮貌'分卷 + 下學期'友愛'分卷 + 下學期'守規'分卷) / 6
    SQL: (s1b.minvalueinclusive + s2b.minvalueinclusive + s3b.minvalueinclusive + s4b.minvalueinclusive + s5b.minvalueinclusive + s6b.minvalueinclusive)/6

    全年操行平均分數: (上學期操行平均分數 + 下學期操行平均分數) / 2
    SQL: ((上學期操行平均分數)+(下學期操行平均分數))/2

    全年操行平均 : 按「積分/等級互換表」,以互換全年操行平均等級
    SQL: (case when TRIM(全年操行平均分數)>=95 then CAST('A' AS CHAR) when TRIM(全年操行平均分數)>=90 then CAST('A-' AS CHAR) when TRIM(全年操行平均分數)>=85 then CAST('B+' AS CHAR) when TRIM(全年操行平均分數)>=80 then CAST('B' AS CHAR) when TRIM(全年操行平均分數)>=75 then CAST('B-' AS CHAR) when TRIM(全年操行平均分數)>=70 then CAST('C+' AS CHAR) when TRIM(全年操行平均分數)>=65 then CAST('C' AS CHAR) when TRIM(全年操行平均分數)>=60 then CAST('C-' AS CHAR) when TRIM(全年操行平均分數)>=55 then CAST('D+' AS CHAR) when TRIM(全年操行平均分數)>=50 then CAST('D' AS CHAR) when TRIM(全年操行平均分數)>=45 then CAST('D-' AS CHAR) when TRIM(全年操行平均分數)>=40 then CAST('E+' AS CHAR) when TRIM(全年操行平均分數)>=35 then CAST('E' AS CHAR) when TRIM(全年操行平均分數)>=30 then CAST('E-' AS CHAR) else CAST('F' AS CHAR) end)

    謝謝!
     
    #13 edb-marmel, 2014-08-01
  14. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    如果要顯示以下資料,需要怎樣修改?謝謝!!
    級別 班別 學號 英文姓名 中文姓名 上學期操行平均分數 上學期操行平均 下學期操行平均分數 下學期操行平均 全年操行平均分數 全年操行平均
     
    #14 WEB-STE, 2014-08-06
  15. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    如果在有關SQL內加上上學期操行平均及下學期操行平均,以等級表示,可以怎麼修改!!
     
    #15 WEB-STE, 2014-08-06
  16. 57686791

    edb-marmel
    Expand Collapse

    文章:
    187
    讚:
    0
    本帖最後由 edb-marmel 於 2014-8-7 16:24 編輯

    本來只要加上以下的 SQL 句子就可以,但 Websams 只限於8,000字。 若加上後(將會多於8,000字),即使成功儲存,將會刪除了後部份的 SQL 句子或會有 error 情況出現:

    上學期操行平均:
    (case when TRIM(上學期操行平均分數)>=95 then CAST('A' AS CHAR) when TRIM(上學期操行平均分數)>=90 then CAST('A-' AS CHAR) when TRIM(上學期操行平均分數)>=85 then CAST('B+' AS CHAR) when TRIM(上學期操行平均分數)>=80 then CAST('B' AS CHAR) when TRIM(上學期操行平均分數)>=75 then CAST('B-' AS CHAR) when TRIM(上學期操行平均分數)>=70 then CAST('C+' AS CHAR) when TRIM(上學期操行平均分數)>=65 then CAST('C' AS CHAR) when TRIM(上學期操行平均分數)>=60 then CAST('C-' AS CHAR) when TRIM(上學期操行平均分數)>=55 then CAST('D+' AS CHAR) when TRIM(上學期操行平均分數)>=50 then CAST('D' AS CHAR) when TRIM(上學期操行平均分數)>=45 then CAST('D-' AS CHAR) when TRIM(上學期操行平均分數)>=40 then CAST('E+' AS CHAR) when TRIM(上學期操行平均分數)>=35 then CAST('E' AS CHAR) when TRIM(上學期操行平均分數)>=30 then CAST('E-' AS CHAR) else CAST('F' AS CHAR) end) '上學期操行平均'

    下學期操行平均:
    (case when TRIM(下學期操行平均分數)>=95 then CAST('A' AS CHAR) when TRIM(下學期操行平均分數)>=90 then CAST('A-' AS CHAR) when TRIM(下學期操行平均分數)>=85 then CAST('B+' AS CHAR) when TRIM(下學期操行平均分數)>=80 then CAST('B' AS CHAR) when TRIM(下學期操行平均分數)>=75 then CAST('B-' AS CHAR) when TRIM(下學期操行平均分數)>=70 then CAST('C+' AS CHAR) when TRIM(下學期操行平均分數)>=65 then CAST('C' AS CHAR) when TRIM(下學期操行平均分數)>=60 then CAST('C-' AS CHAR) when TRIM(下學期操行平均分數)>=55 then CAST('D+' AS CHAR) when TRIM(下學期操行平均分數)>=50 then CAST('D' AS CHAR) when TRIM(下學期操行平均分數)>=45 then CAST('D-' AS CHAR) when TRIM(下學期操行平均分數)>=40 then CAST('E+' AS CHAR) when TRIM(下學期操行平均分數)>=35 then CAST('E' AS CHAR) when TRIM(下學期操行平均分數)>=30 then CAST('E-' AS CHAR) else CAST('F' AS CHAR) end) '下學期操行平均'

    謝謝!
     
    #16 edb-marmel, 2014-08-07
  17. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    如果只顯示以下資料,需要怎樣修改?謝謝!!
    級別 班別 學號 英文姓名 中文姓名 上學期操行平均 下學期操行平均 全年操行平均
     
    #17 WEB-STE, 2014-08-07
  18. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    如果又分為三個分別顯示以下資料,需要怎樣修改?謝謝!!
    1) 級別 班別 學號 英文姓名 中文姓名 上學期操行平均
    2) 級別 班別 學號 英文姓名 中文姓名 下學期操行平均
    3) 級別 班別 學號 英文姓名 中文姓名 全年操行平均
     
    #18 WEB-STE, 2014-08-07
  19. 57686791

    edb-marmel
    Expand Collapse

    文章:
    187
    讚:
    0
    回覆 18# Stephen

    1) 級別 班別 學號 英文姓名 中文姓名 上學期操行平均
    select a.classlvl '級別', a.classcode '班別', a.classno '學號', a.enname '英文姓名',
    a.chname '中文姓名', (s1.minvalueinclusive + s2.minvalueinclusive + s3.minvalueinclusive + s4.minvalueinclusive + s5.minvalueinclusive + s6.minvalueinclusive)/6 '上學期操行平均分數', (case when TRIM(上學期操行平均分數)>=95 then CAST('A' AS CHAR) when TRIM(上學期操行平均分數)>=90 then CAST('A-' AS CHAR) when TRIM(上學期操行平均分數)>=85 then CAST('B+' AS CHAR) when TRIM(上學期操行平均分數)>=80 then CAST('B' AS CHAR) when TRIM(上學期操行平均分數)>=75 then CAST('B-' AS CHAR) when TRIM(上學期操行平均分數)>=70 then CAST('C+' AS CHAR) when TRIM(上學期操行平均分數)>=65 then CAST('C' AS CHAR) when TRIM(上學期操行平均分數)>=60 then CAST('C-' AS CHAR) when TRIM(上學期操行平均分數)>=55 then CAST('D+' AS CHAR) when TRIM(上學期操行平均分數)>=50 then CAST('D' AS CHAR) when TRIM(上學期操行平均分數)>=45 then CAST('D-' AS CHAR) when TRIM(上學期操行平均分數)>=40 then CAST('E+' AS CHAR) when TRIM(上學期操行平均分數)>=35 then CAST('E' AS CHAR) when TRIM(上學期操行平均分數)>=30 then CAST('E-' AS CHAR) else CAST('F' AS CHAR) end) '上學期操行平均' from vw_stu_lateststudent a left outer join tb_asr_studassessdata 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 left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c on a.suid=c.suid and a.schyear=c.schyear and a.stuid=c.stuid and c.timeseq=b.timeseq join tb_asr_gradeconversioncomp s1 on c.gradeconversioncompcode=s1.gradeconversioncompcode and c.gradeconversionseq=s1.gradeconversionseq and s1.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d
    on a.suid=d.suid and a.schyear=d.schyear and a.stuid=d.stuid and d.timeseq=b.timeseq join tb_asr_gradeconversioncomp s2 on d.gradeconversioncompcode=s2.gradeconversioncompcode and d.gradeconversionseq=s2.gradeconversionseq and s2.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e on a.suid=e.suid and a.schyear=e.schyear and a.stuid=e.stuid and e.timeseq=b.timeseq join tb_asr_gradeconversioncomp s3 on e.gradeconversioncompcode=s3.gradeconversioncompcode and e.gradeconversionseq=s3.gradeconversionseq and s3.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f
    on a.suid=f.suid and a.schyear=f.schyear and a.stuid=f.stuid and f.timeseq=b.timeseq
    join tb_asr_gradeconversioncomp s4 on f.gradeconversioncompcode=s4.gradeconversioncompcode and f.gradeconversionseq=s4.gradeconversionseq and s4.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g
    on a.suid=g.suid and a.schyear=g.schyear and a.stuid=g.stuid and g.timeseq=b.timeseq join tb_asr_gradeconversioncomp s5 on g.gradeconversioncompcode=s5.gradeconversioncompcode and g.gradeconversionseq=s5.gradeconversionseq and s5.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h on a.suid=h.suid and a.schyear=h.schyear and a.stuid=h.stuid and h.timeseq=b.timeseq join tb_asr_gradeconversioncomp s6 on h.gradeconversioncompcode=s6.gradeconversioncompcode and h.gradeconversionseq=s6.gradeconversionseq and s6.minvalueinclusive!=null
    where a.schyear=? order by a.schlvl, a.schsess, a.classlvl, a.classcode, a.classno, a.enname


    2) 級別 班別 學號 英文姓名 中文姓名 下學期操行平均
    select a.classlvl '級別', a.classcode '班別', a.classno '學號', a.enname '英文姓名',
    a.chname '中文姓名', (s1b.minvalueinclusive + s2b.minvalueinclusive + s3b.minvalueinclusive + s4b.minvalueinclusive + s5b.minvalueinclusive + s6b.minvalueinclusive)/6 '下學期操行平均分數', (case when TRIM(下學期操行平均分數)>=95 then CAST('A' AS CHAR) when TRIM(下學期操行平均分數)>=90 then CAST('A-' AS CHAR) when TRIM(下學期操行平均分數)>=85 then CAST('B+' AS CHAR) when TRIM(下學期操行平均分數)>=80 then CAST('B' AS CHAR) when TRIM(下學期操行平均分數)>=75 then CAST('B-' AS CHAR) when TRIM(下學期操行平均分數)>=70 then CAST('C+' AS CHAR) when TRIM(下學期操行平均分數)>=65 then CAST('C' AS CHAR) when TRIM(下學期操行平均分數)>=60 then CAST('C-' AS CHAR) when TRIM(下學期操行平均分數)>=55 then CAST('D+' AS CHAR) when TRIM(下學期操行平均分數)>=50 then CAST('D' AS CHAR) when TRIM(下學期操行平均分數)>=45 then CAST('D-' AS CHAR) when TRIM(下學期操行平均分數)>=40 then CAST('E+' AS CHAR) when TRIM(下學期操行平均分數)>=35 then CAST('E' AS CHAR) when TRIM(下學期操行平均分數)>=30 then CAST('E-' AS CHAR) else CAST('F' AS CHAR) end) '下學期操行平均' from vw_stu_lateststudent a left outer join tb_asr_studassessdata b2 on a.suid=b2.suid and a.schlvl=b2.schlevel and a.schsess=b2.schsession and a.classlvl=b2.classlevel and a.schyear=b2.schyear and a.stuid=b2.stuid and b2.timeseq=1200 left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c2 on a.suid=c2.suid and a.schyear=c2.schyear and a.stuid=c2.stuid and c2.timeseq=b2.timeseq join tb_asr_gradeconversioncomp s1b on c2.gradeconversioncompcode=s1b.gradeconversioncompcode and c2.gradeconversionseq=s1b.gradeconversionseq and s1b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d2
    on a.suid=d2.suid and a.schyear=d2.schyear and a.stuid=d2.stuid and d2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s2b on d2.gradeconversioncompcode=s2b.gradeconversioncompcode and d2.gradeconversionseq=s2b.gradeconversionseq and s2b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e2 on a.suid=e2.suid and a.schyear=e2.schyear and a.stuid=e2.stuid and e2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s3b on e2.gradeconversioncompcode=s3b.gradeconversioncompcode and e2.gradeconversionseq=s3b.gradeconversionseq and s3b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f2 on a.suid=f2.suid and a.schyear=f2.schyear and a.stuid=f2.stuid and f2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s4b on f2.gradeconversioncompcode=s4b.gradeconversioncompcode and f2.gradeconversionseq=s4b.gradeconversionseq and s4b.minvalueinclusive!=null left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g2 on a.suid=g2.suid and a.schyear=g2.schyear and a.stuid=g2.stuid and g2.timeseq=b2.timeseq
    join tb_asr_gradeconversioncomp s5b on g2.gradeconversioncompcode=s5b.gradeconversioncompcode and g2.gradeconversionseq=s5b.gradeconversionseq and s5b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h2
    on a.suid=h2.suid and a.schyear=h2.schyear and a.stuid=h2.stuid and h2.timeseq=b2.timeseq join tb_asr_gradeconversioncomp s6b on h2.gradeconversioncompcode=s6b.gradeconversioncompcode and h2.gradeconversionseq=s6b.gradeconversionseq and s6b.minvalueinclusive!=null
    where a.schyear=? order by a.schlvl, a.schsess, a.classlvl, a.classcode, a.classno, a.enname

    3) 級別 班別 學號 英文姓名 中文姓名 全年操行平均
    select a.classlvl '級別', a.classcode '班別', a.classno '學號', a.enname '英文姓名',
    a.chname '中文姓名', (((s1.minvalueinclusive + s2.minvalueinclusive + s3.minvalueinclusive + s4.minvalueinclusive + s5.minvalueinclusive + s6.minvalueinclusive)/6)+((s1b.minvalueinclusive + s2b.minvalueinclusive + s3b.minvalueinclusive + s4b.minvalueinclusive + s5b.minvalueinclusive + s6b.minvalueinclusive)/6))/2 '全年操行平均分數', (case when TRIM(全年操行平均分數)>=95 then CAST('A' AS CHAR) when TRIM(全年操行平均分數)>=90 then CAST('A-' AS CHAR) when TRIM(全年操行平均分數)>=85 then CAST('B+' AS CHAR) when TRIM(全年操行平均分數)>=80 then CAST('B' AS CHAR) when TRIM(全年操行平均分數)>=75 then CAST('B-' AS CHAR) when TRIM(全年操行平均分數)>=70 then CAST('C+' AS CHAR) when TRIM(全年操行平均分數)>=65 then CAST('C' AS CHAR) when TRIM(全年操行平均分數)>=60 then CAST('C-' AS CHAR) when TRIM(全年操行平均分數)>=55 then CAST('D+' AS CHAR) when TRIM(全年操行平均分數)>=50 then CAST('D' AS CHAR) when TRIM(全年操行平均分數)>=45 then CAST('D-' AS CHAR) when TRIM(全年操行平均分數)>=40 then CAST('E+' AS CHAR) when TRIM(全年操行平均分數)>=35 then CAST('E' AS CHAR) when TRIM(全年操行平均分數)>=30 then CAST('E-' AS CHAR) else CAST('F' AS CHAR) end) '全年操行平均' from vw_stu_lateststudent a left outer join tb_asr_studassessdata 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 left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c on a.suid=c.suid and a.schyear=c.schyear and a.stuid=c.stuid and c.timeseq=b.timeseq join tb_asr_gradeconversioncomp s1 on c.gradeconversioncompcode=s1.gradeconversioncompcode and c.gradeconversionseq=s1.gradeconversionseq and s1.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d
    on a.suid=d.suid and a.schyear=d.schyear and a.stuid=d.stuid and d.timeseq=b.timeseq join tb_asr_gradeconversioncomp s2 on d.gradeconversioncompcode=s2.gradeconversioncompcode and d.gradeconversionseq=s2.gradeconversionseq and s2.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e on a.suid=e.suid and a.schyear=e.schyear and a.stuid=e.stuid and e.timeseq=b.timeseq join tb_asr_gradeconversioncomp s3 on e.gradeconversioncompcode=s3.gradeconversioncompcode and e.gradeconversionseq=s3.gradeconversionseq and s3.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f
    on a.suid=f.suid and a.schyear=f.schyear and a.stuid=f.stuid and f.timeseq=b.timeseq
    join tb_asr_gradeconversioncomp s4 on f.gradeconversioncompcode=s4.gradeconversioncompcode and f.gradeconversionseq=s4.gradeconversionseq and s4.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g
    on a.suid=g.suid and a.schyear=g.schyear and a.stuid=g.stuid and g.timeseq=b.timeseq join tb_asr_gradeconversioncomp s5 on g.gradeconversioncompcode=s5.gradeconversioncompcode and g.gradeconversionseq=s5.gradeconversionseq and s5.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h on a.suid=h.suid and a.schyear=h.schyear and a.stuid=h.stuid and h.timeseq=b.timeseq join tb_asr_gradeconversioncomp s6 on h.gradeconversioncompcode=s6.gradeconversioncompcode and h.gradeconversionseq=s6.gradeconversionseq and s6.minvalueinclusive!=null
    left outer join tb_asr_studassessdata b2 on a.suid=b2.suid and a.schlvl=b2.schlevel and a.schsess=b2.schsession and a.classlvl=b2.classlevel and a.schyear=b2.schyear and a.stuid=b2.stuid and b2.timeseq=1200 left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c2 on a.suid=c2.suid and a.schyear=c2.schyear and a.stuid=c2.stuid and c2.timeseq=b2.timeseq join tb_asr_gradeconversioncomp s1b on c2.gradeconversioncompcode=s1b.gradeconversioncompcode and c2.gradeconversionseq=s1b.gradeconversionseq and s1b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d2
    on a.suid=d2.suid and a.schyear=d2.schyear and a.stuid=d2.stuid and d2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s2b on d2.gradeconversioncompcode=s2b.gradeconversioncompcode and d2.gradeconversionseq=s2b.gradeconversionseq and s2b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e2 on a.suid=e2.suid and a.schyear=e2.schyear and a.stuid=e2.stuid and e2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s3b on e2.gradeconversioncompcode=s3b.gradeconversioncompcode and e2.gradeconversionseq=s3b.gradeconversionseq and s3b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f2 on a.suid=f2.suid and a.schyear=f2.schyear and a.stuid=f2.stuid and f2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s4b on f2.gradeconversioncompcode=s4b.gradeconversioncompcode and f2.gradeconversionseq=s4b.gradeconversionseq and s4b.minvalueinclusive!=null left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g2 on a.suid=g2.suid and a.schyear=g2.schyear and a.stuid=g2.stuid and g2.timeseq=b2.timeseq
    join tb_asr_gradeconversioncomp s5b on g2.gradeconversioncompcode=s5b.gradeconversioncompcode and g2.gradeconversionseq=s5b.gradeconversionseq and s5b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h2
    on a.suid=h2.suid and a.schyear=h2.schyear and a.stuid=h2.stuid and h2.timeseq=b2.timeseq join tb_asr_gradeconversioncomp s6b on h2.gradeconversioncompcode=s6b.gradeconversioncompcode and h2.gradeconversionseq=s6b.gradeconversionseq and s6b.minvalueinclusive!=null
    where a.schyear=? order by a.schlvl, a.schsess, a.classlvl, a.classcode, a.classno, a.enname

    謝謝!
     
    #19 edb-marmel, 2014-08-08
  20. 57686791

    edb-marmel
    Expand Collapse

    文章:
    187
    讚:
    0
    本帖最後由 edb-marmel 於 2014-8-8 10:15 編輯

    回覆 17# Stephen

    由於所有平均等級是從分數計算的,SQL 句子需加上分數資料,SQL 字數也會多於8,000 字 (但 Websams 只限於8,000字)。

    以下的 SQL 字數多於8,000 字,即使成功儲存,將會刪除了後部份的 SQL 句子或會有 error 情況出現:
    級別 班別 學號 英文姓名 中文姓名 上學期操行平均 下學期操行平均 全年操行平均

    select a.classlvl '級別', a.classcode '班別', a.classno '學號', a.enname '英文姓名',
    a.chname '中文姓名', (s1.minvalueinclusive + s2.minvalueinclusive + s3.minvalueinclusive + s4.minvalueinclusive + s5.minvalueinclusive + s6.minvalueinclusive)/6 '上學期操行平均分數', (case when TRIM(上學期操行平均分數)>=95 then CAST('A' AS CHAR) when TRIM(上學期操行平均分數)>=90 then CAST('A-' AS CHAR) when TRIM(上學期操行平均分數)>=85 then CAST('B+' AS CHAR) when TRIM(上學期操行平均分數)>=80 then CAST('B' AS CHAR) when TRIM(上學期操行平均分數)>=75 then CAST('B-' AS CHAR) when TRIM(上學期操行平均分數)>=70 then CAST('C+' AS CHAR) when TRIM(上學期操行平均分數)>=65 then CAST('C' AS CHAR) when TRIM(上學期操行平均分數)>=60 then CAST('C-' AS CHAR) when TRIM(上學期操行平均分數)>=55 then CAST('D+' AS CHAR) when TRIM(上學期操行平均分數)>=50 then CAST('D' AS CHAR) when TRIM(上學期操行平均分數)>=45 then CAST('D-' AS CHAR) when TRIM(上學期操行平均分數)>=40 then CAST('E+' AS CHAR) when TRIM(上學期操行平均分數)>=35 then CAST('E' AS CHAR) when TRIM(上學期操行平均分數)>=30 then CAST('E-' AS CHAR) else CAST('F' AS CHAR) end) '上學期操行平均', (s1b.minvalueinclusive + s2b.minvalueinclusive + s3b.minvalueinclusive + s4b.minvalueinclusive + s5b.minvalueinclusive + s6b.minvalueinclusive)/6 '下學期操行平均分數', (case when TRIM(下學期操行平均分數)>=95 then CAST('A' AS CHAR) when TRIM(下學期操行平均分數)>=90 then CAST('A-' AS CHAR) when TRIM(下學期操行平均分數)>=85 then CAST('B+' AS CHAR) when TRIM(下學期操行平均分數)>=80 then CAST('B' AS CHAR) when TRIM(下學期操行平均分數)>=75 then CAST('B-' AS CHAR) when TRIM(下學期操行平均分數)>=70 then CAST('C+' AS CHAR) when TRIM(下學期操行平均分數)>=65 then CAST('C' AS CHAR) when TRIM(下學期操行平均分數)>=60 then CAST('C-' AS CHAR) when TRIM(下學期操行平均分數)>=55 then CAST('D+' AS CHAR) when TRIM(下學期操行平均分數)>=50 then CAST('D' AS CHAR) when TRIM(下學期操行平均分數)>=45 then CAST('D-' AS CHAR) when TRIM(下學期操行平均分數)>=40 then CAST('E+' AS CHAR) when TRIM(下學期操行平均分數)>=35 then CAST('E' AS CHAR) when TRIM(下學期操行平均分數)>=30 then CAST('E-' AS CHAR) else CAST('F' AS CHAR) end) '下學期操行平均' , ((上學期操行平均分數)+(下學期操行平均分數))/2 '全年操行平均分數', (case when TRIM(全年操行平均分數)>=95 then CAST('A' AS CHAR) when TRIM(全年操行平均分數)>=90 then CAST('A-' AS CHAR) when TRIM(全年操行平均分數)>=85 then CAST('B+' AS CHAR) when TRIM(全年操行平均分數)>=80 then CAST('B' AS CHAR) when TRIM(全年操行平均分數)>=75 then CAST('B-' AS CHAR) when TRIM(全年操行平均分數)>=70 then CAST('C+' AS CHAR) when TRIM(全年操行平均分數)>=65 then CAST('C' AS CHAR) when TRIM(全年操行平均分數)>=60 then CAST('C-' AS CHAR) when TRIM(全年操行平均分數)>=55 then CAST('D+' AS CHAR) when TRIM(全年操行平均分數)>=50 then CAST('D' AS CHAR) when TRIM(全年操行平均分數)>=45 then CAST('D-' AS CHAR) when TRIM(全年操行平均分數)>=40 then CAST('E+' AS CHAR) when TRIM(全年操行平均分數)>=35 then CAST('E' AS CHAR) when TRIM(全年操行平均分數)>=30 then CAST('E-' AS CHAR) else CAST('F' AS CHAR) end) '全年操行平均' from vw_stu_lateststudent a left outer join tb_asr_studassessdata 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 left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c on a.suid=c.suid and a.schyear=c.schyear and a.stuid=c.stuid and c.timeseq=b.timeseq join tb_asr_gradeconversioncomp s1 on c.gradeconversioncompcode=s1.gradeconversioncompcode and c.gradeconversionseq=s1.gradeconversionseq and s1.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d
    on a.suid=d.suid and a.schyear=d.schyear and a.stuid=d.stuid and d.timeseq=b.timeseq join tb_asr_gradeconversioncomp s2 on d.gradeconversioncompcode=s2.gradeconversioncompcode and d.gradeconversionseq=s2.gradeconversionseq and s2.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e on a.suid=e.suid and a.schyear=e.schyear and a.stuid=e.stuid and e.timeseq=b.timeseq join tb_asr_gradeconversioncomp s3 on e.gradeconversioncompcode=s3.gradeconversioncompcode and e.gradeconversionseq=s3.gradeconversionseq and s3.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f
    on a.suid=f.suid and a.schyear=f.schyear and a.stuid=f.stuid and f.timeseq=b.timeseq
    join tb_asr_gradeconversioncomp s4 on f.gradeconversioncompcode=s4.gradeconversioncompcode and f.gradeconversionseq=s4.gradeconversionseq and s4.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g
    on a.suid=g.suid and a.schyear=g.schyear and a.stuid=g.stuid and g.timeseq=b.timeseq join tb_asr_gradeconversioncomp s5 on g.gradeconversioncompcode=s5.gradeconversioncompcode and g.gradeconversionseq=s5.gradeconversionseq and s5.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h on a.suid=h.suid and a.schyear=h.schyear and a.stuid=h.stuid and h.timeseq=b.timeseq join tb_asr_gradeconversioncomp s6 on h.gradeconversioncompcode=s6.gradeconversioncompcode and h.gradeconversionseq=s6.gradeconversionseq and s6.minvalueinclusive!=null
    left outer join tb_asr_studassessdata b2 on a.suid=b2.suid and a.schlvl=b2.schlevel and a.schsess=b2.schsession and a.classlvl=b2.classlevel and a.schyear=b2.schyear and a.stuid=b2.stuid and b2.timeseq=1200 left outer join (select c1.*, d1.namechi
    from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='學習') c2 on a.suid=c2.suid and a.schyear=c2.schyear and a.stuid=c2.stuid and c2.timeseq=b2.timeseq join tb_asr_gradeconversioncomp s1b on c2.gradeconversioncompcode=s1b.gradeconversioncompcode and c2.gradeconversionseq=s1b.gradeconversionseq and s1b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='服務') d2
    on a.suid=d2.suid and a.schyear=d2.schyear and a.stuid=d2.stuid and d2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s2b on d2.gradeconversioncompcode=s2b.gradeconversioncompcode and d2.gradeconversionseq=s2b.gradeconversionseq and s2b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='家課') e2 on a.suid=e2.suid and a.schyear=e2.schyear and a.stuid=e2.stuid and e2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s3b on e2.gradeconversioncompcode=s3b.gradeconversioncompcode and e2.gradeconversionseq=s3b.gradeconversionseq and s3b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='禮貌') f2 on a.suid=f2.suid and a.schyear=f2.schyear and a.stuid=f2.stuid and f2.timeseq= b2.timeseq join tb_asr_gradeconversioncomp s4b on f2.gradeconversioncompcode=s4b.gradeconversioncompcode and f2.gradeconversionseq=s4b.gradeconversionseq and s4b.minvalueinclusive!=null left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='友愛') g2 on a.suid=g2.suid and a.schyear=g2.schyear and a.stuid=g2.stuid and g2.timeseq=b2.timeseq
    join tb_asr_gradeconversioncomp s5b on g2.gradeconversioncompcode=s5b.gradeconversioncompcode and g2.gradeconversionseq=s5b.gradeconversionseq and s5b.minvalueinclusive!=null
    left outer join (select c1.*, d1.namechi from tb_asr_studcondcompdata c1 join tb_asr_condcomp d1 on c1.suid=d1.suid and c1.schyear=d1.schyear and c1.condcompseq=d1.condcompseq and d1.namechi='守規') h2
    on a.suid=h2.suid and a.schyear=h2.schyear and a.stuid=h2.stuid and h2.timeseq=b2.timeseq join tb_asr_gradeconversioncomp s6b on h2.gradeconversioncompcode=s6b.gradeconversioncompcode and h2.gradeconversionseq=s6b.gradeconversionseq and s6b.minvalueinclusive!=null
    where a.schyear=? order by a.schlvl, a.schsess, a.classlvl, a.classcode, a.classno, a.enname
     
    #20 edb-marmel, 2014-08-08