用sql抽取學生的操行紀錄

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

  1. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    明白及多謝!!
     
    #21 WEB-STE, 2014-08-08
  2. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    如果想取某一年級學生,歷年上下學期各操行分卷的成績,可以怎做?謝謝!!

    級別 班別 學號 中文姓名 英文姓名 年度 學期 學習、服務、家課、禮貌 友愛 守規
    P6 6A 1 陳大文 CHAN TAI MAN 2009 上 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 2009 下 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 2010 上 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 2010 下 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 2011 上 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 2011 下 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 2012 上 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 2012 下 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 2013 上 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 2013 下 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 2014 上 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 2014 下 A- A- A- A- A- A-
    P6 6A 2 陳XX CHAN XX 2009 上 A- A- A- A- A- A-
     
    #22 WEB-STE, 2014-10-23
  3. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    又如果只列某一學生的歷年上下學期各操行分卷的成績,可以怎做?謝謝!!
    級別 班別 學號 中文姓名 英文姓名 班級 學期 學習、服務、家課、禮貌 友愛 守規
    P6 6A 1 陳大文 CHAN TAI MAN 1A 上 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 1A 下 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 2A 上 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 2A 下 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 3A 上 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 3A 下 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 4A 上 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 4A 下 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 5A 上 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 5A 下 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 6A 上 A- A- A- A- A- A-
    P6 6A 1 陳大文 CHAN TAI MAN 6A 下 A- A- A- A- A- A-
     
    #23 WEB-STE, 2014-10-27
  4. 57686791

    edb-marmel
    Expand Collapse

    文章:
    187
    讚:
    0
    本帖最後由 edb-marmel 於 2014-10-29 17:09 編輯

    回覆 22# Stephen

    請老師參考以下的 SQL 句子:
    select curr.classlvl '級別', curr.classcode '班別', curr.classno '學號', a.chname '中文姓名', a.enname '英文姓名',
    a.schyear '年度', (CASE WHEN TRIM(b.timeseq)='1100' THEN CAST('上' AS CHAR) ELSE CAST('下' AS CHAR) END) '學期',
    s1.gradeconversioncompcode '學習', s2.gradeconversioncompcode '服務', s3.gradeconversioncompcode '家課',
    s4.gradeconversioncompcode '禮貌', s5.gradeconversioncompcode '友愛', s6.gradeconversioncompcode '守規'
    from vw_stu_lateststudent a join (select e.* from VW_STU_LATESTSTUDENT_CURRYR e) curr
    on curr.suid=a.suid and curr.stuid=a.stuid and curr.classlvl=?
    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' or b.timeseq='1200')
    left outer join (select distinct 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 distinct 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 distinct 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 distinct 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 distinct 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 distinct 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

    group by a.chname, a.schyear, curr.classlvl, curr.classcode, curr.classno, a.enname, b.timeseq, s1.gradeconversioncompcode, s2.gradeconversioncompcode, s3.gradeconversioncompcode, s4.gradeconversioncompcode, s5.gradeconversioncompcode, s6.gradeconversioncompcode
    order by curr.classlvl, curr.classcode, curr.classno, a.chname, a.schyear, a.enname, b.timeseq
     
    #24 edb-marmel, 2014-10-29
  5. 57686791

    edb-marmel
    Expand Collapse

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

    請老師參考以下的 SQL 句子:

    select curr.classlvl '級別', curr.classcode '班別', curr.classno '學號', a.chname '中文姓名', a.enname '英文姓名',
    a.classcode '班級', a.schyear '年度', (CASE WHEN TRIM(b.timeseq)='1100' THEN CAST('上' AS CHAR) ELSE CAST('下' AS CHAR) END) '學期',
    s1.gradeconversioncompcode '學習', s2.gradeconversioncompcode '服務', s3.gradeconversioncompcode '家課',
    s4.gradeconversioncompcode '禮貌', s5.gradeconversioncompcode '友愛', s6.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 a.regno=? and (b.timeseq='1100' or b.timeseq='1200')
    left outer join (select distinct 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 distinct 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 distinct 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 distinct 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 distinct 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 distinct 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
    join (select e.* from VW_STU_LATESTSTUDENT_CURRYR e) curr
    on curr.suid=a.suid and curr.stuid=a.stuid

    group by a.chname, a.schyear, curr.classlvl, curr.classcode, curr.classno, a.enname, b.timeseq, s1.gradeconversioncompcode, s2.gradeconversioncompcode, s3.gradeconversioncompcode, s4.gradeconversioncompcode, s5.gradeconversioncompcode, s6.gradeconversioncompcode, a.classcode
    order by curr.classlvl, curr.classcode, curr.classno, a.chname, a.schyear, a.enname, b.timeseq, a.classcode
     
    #25 edb-marmel, 2014-10-29
  6. 10394813

    WEB-STE
    Expand Collapse

    文章:
    101
    讚:
    0
    只出現10-14年度,未能出現09-10年度資料?謝謝!!
    級別 班別 學號 中文姓名 英文姓名 班級 年度 學期 學習 服務 家課 禮貌 友愛 守規
    P6 6A 1 陳 Chan 2B 2010 上 B+ A- A A- B B
    P6 6A 1 陳 Chan 2B 2010 下 B+ B+ B+ B+ B+ B
    P6 6A 1 陳 Chan 3B 2011 上 B+ B+ A B+ B B+
    P6 6A 1 陳 Chan 3B 2011 下 B+ B+ A B+ B B+
    P6 6A 1 陳 Chan 4A 2012 上 B+ B+ A- B+ B+ B+
    P6 6A 1 陳 Chan 4A 2012 下 B+ B+ B+ B B B-
    P6 6A 1 陳 Chan 5A 2013 上 A- B+ A A- B+ A-
    P6 6A 1 陳 Chan 5A 2013 下 B+ A- A- A- B+ B
     
    #26 WEB-STE, 2014-11-04
  7. 57686791

    edb-marmel
    Expand Collapse

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

    請老師留意有否選擇以下的資料 (2009年):

    ASR_Conduct.png
     
    #27 edb-marmel, 2014-11-04