SQL抽取合格科目數目

本文由 Moon 在 2006-07-05 發表於 "WebSAMS 討論區" 討論區

  1. 57686791

    edb-marmel
    Expand Collapse

    文章:
    187
    讚:
    0
    回覆 20# tr-gilbertng


    請加入 distinct 以除去重覆的資料,謝謝!

    select
    b.classcode 'class', b.classno, b.enname, b.chname,
    b.sex, p.failed
    from tb_stu_student b

    left outer join
    (select d.suid, d.stuid, d.schyear, d.timeseq,
    list(distinct sn.CH_DES) as 'failed'
    from tb_asr_studassessdata d
    left outer join tb_asr_subjassessdata a
    on d.suid=a.suid and d.stuid=a.stuid and
    d.schyear=a.schyear and d.timeseq=a.timeseq
    left outer join tb_hse_common sn
    on sn.suid=a.suid and sn.TB_ID='SBJ' and
    sn.CODE_ID=a.SUBJCODE
    left outer join tb_asr_clslvlsubjsetting s
    on d.suid=s.suid and d.schyear=s.schyear and
    d.schlevel=s.schlevel and d.schsession=s.schsession and
    d.classlevel=s.classlevel and d.timeseq=s.timeseq and
    a.subjcode=s.subjcode
    left outer join tb_asr_gradeconversioncomp g
    on d.suid=g.suid and d.schyear=g.schyear and
    a.sysgradeconvseq=g.gradeconversionseq and
    a.sysgradeconvcompcode=g.gradeconversioncompcode
    left outer join tb_asr_gradeconversioncomp gp
    on d.suid=gp.suid and d.schyear=gp.schyear and
    a.sysgradeconvseq=gp.gradeconversionseq and
    gp.passinggradeind='Y'
    where s.bygradeind='N' and
    g.absolutegrademark >= gp.absolutegrademark and
    d.schyear=? and
    d.timeseq=?
    group by d.suid, d.stuid, d.schyear, d.timeseq
    ) p
    on b.suid=p.suid and b.stuid=p.stuid

    left outer join tb_asr_studassessdata d1
    on b.suid=d1.suid and b.stuid=d1.stuid and
    p.schyear=d1.schyear and p.timeseq=d1.timeseq

    where
    b.classlvl=? and
    b.classcode is not null and b.classcode<>''
    order by
    b.SUID, b.SCHLVL, b.SCHSESS, b.CLASSLVL,
    b.classcode, b.classno
     
    #21 edb-marmel, 2014-07-08
  2. 55717559

    tr-gilbertng
    Expand Collapse

    文章:
    11
    讚:
    0

    謝謝你的幫忙, 如果要將取得這些合格科目的數值, 是否要使用Count? 語法是.....? 謝謝!
     
    #22 tr-gilbertng, 2014-07-08
  3. 57686791

    edb-marmel
    Expand Collapse

    文章:
    187
    讚:
    0
    回覆 22# tr-gilbertng

    請問是指那些(只有不合格)科目或全部及格科目的數值,還有是以每班或每級作統計,謝謝!
     
    #23 edb-marmel, 2014-07-08
  4. 55717559

    tr-gilbertng
    Expand Collapse

    文章:
    11
    讚:
    0
    我想要的是在以上的表增加多一欄, 該欄是顯示該學生的合格科目數目:

    班別 學號 姓名 合格科目 合格科目數目
    4A 1 xxx M 化學,中國語文,經濟,英國語文,數學(必修部分),通識教育 (6)
    4A 2 xxx M 化學,中國語文,經濟,英國語文,數學(必修部分)(5)

    謝謝
     
    #24 tr-gilbertng, 2015-02-02
  5. 57686791

    edb-marmel
    Expand Collapse

    文章:
    187
    讚:
    0
    回覆 24# tr-gilbertng

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

    select
    b.classcode '班別',
    b.classno '學號',
    b.chname '中文姓名',
    b.enname '英文姓名',
    p.passed '合格科目',
    ifnull(p.passing,0,p.passing) as '合格科目數目'
    from tb_stu_student b

    left outer join
    (select d.suid, d.stuid, count(distinct sn.CH_DES) as 'passing', list(distinct sn.CH_DES) as 'passed'
    from tb_asr_studassessdata d
    left outer join tb_asr_subjassessdata a
    on d.suid=a.suid and d.stuid=a.stuid and d.timeseq=a.timeseq and d.schyear=a.schyear
    left outer join tb_asr_gradeconversioncomp g on d.suid=g.suid and d.schyear=g.schyear and
    a.sysgradeconvseq=g.gradeconversionseq and a.sysgradeconvcompcode=g.gradeconversioncompcode
    left outer join tb_asr_gradeconversioncomp gp on d.suid=gp.suid and d.schyear=gp.schyear and
    a.sysgradeconvseq=gp.gradeconversionseq and gp.passinggradeind='Y'
    left outer join tb_hse_common sn
    on sn.suid=a.suid and sn.TB_ID='SBJ' and sn.CODE_ID=a.SUBJCODE
    left outer join tb_asr_clslvlsubjsetting s
    on d.suid=s.suid and d.schyear=s.schyear and d.schlevel=s.schlevel and d.schsession=s.schsession and
    d.classlevel=s.classlevel and d.timeseq=s.timeseq and
    a.subjcode=s.subjcode
    where g.absolutegrademark >= gp.absolutegrademark and d.schyear=? and d.timeseq=?
    group by d.suid, d.stuid) p
    on b.suid=p.suid and b.stuid=p.stuid
    where b.classlvl=? and b.classcode<>''
    order by b.classcode, b.classno
     
    #25 edb-marmel, 2015-02-05