SQL抽取合格科目數目

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

  1. 55034016

    Moon
    Expand Collapse

    文章:
    16
    讚:
    0
    請問各位如何利用SQL抽取下學期考試學生合格科目的數目呢, 謝謝!
     
  2. 45331766

    wkc-cc
    Expand Collapse

    文章:
    0
    讚:
    0

    學生成績下的及格科目統計報告 (R-ASR055-C) 是否合用?

    Charles

     
  3. 55034016

    Moon
    Expand Collapse

    文章:
    16
    讚:
    0
    謝謝你, 但是我需要<FONT color=#ff0000>每個學生</FONT>合格科目的數目.
     
  4. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0

    Moon君:

    可參考以下語句。
    ﹝參數輸入例子:schyear=2005,timeseq=1200,classlvl=P1﹞

    select
      b.classcode, b.classno, b.chname, b.enname,
      ifnull(p.passing,0,p.passing) as 'passing'
    from tb_stu_student b

    left outer join
     (select d.suid, d.stuid, count(d.stuid) as 'passing'
      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'
      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


    系統及資訊管理組

     
    #4 EDB-EddieKwan, 2006-07-05
  5. 55034016

    Moon
    Expand Collapse

    文章:
    16
    讚:
    0
    謝謝你, 如果想統計合格科目時<FONT color=#ff0000>不計算用grade的科目</FONT>, 應該怎樣呢?
     
  6. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0

    Moon君:

    可試試以下語句。

    select
      b.classcode, b.classno, b.chname, b.enname,
      ifnull(p.passing,0,p.passing) as 'passing'
    from tb_stu_student b

    left outer join
     (select d.suid, d.stuid, count(d.stuid) as 'passing'
      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_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
     ) p
    on b.suid=p.suid and b.stuid=p.stuid
    where
      b.classlvl=? and
      b.classcode<>''
    order by b.classcode, b.classno

    系統及資訊管理組

     
    #6 EDB-EddieKwan, 2006-07-06
  7. csk-auco

    csk-auco
    Expand Collapse

    [parsehtml]<P><FONT color=#006600>請問 timeseq 代表甚麼? timeseq=1200 代表甚麼?</FONT></P> <P><FONT color=#006600>如果想同時顯示中英數三科及平均分,應如可修改?</FONT></P>[/parsehtml]
     
    #7 csk-auco, 2006-07-07
  8. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0

    csk-auco君:

    timeseq是用來表示哪一個考績、學期或年終,而1200則代表第二學期。

    至於你提出的要求,可試試以下語句:

    select
      b.classcode, b.classno, b.chname, b.enname,
      a1.sysscore as 'Chi', a2.sysscore as 'Eng', a3.sysscore as 'Math',
      d1.syspercscore as 'Average',
      ifnull(p.passing,0,p.passing) as 'Pass'
    from tb_stu_student b

    left outer join
     (select d.suid, d.stuid, d.schyear, d.timeseq,
             count(d.stuid) as 'passing'
      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_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
    left outer join tb_asr_subjassessdata a1
      on b.suid=a1.suid and b.stuid=a1.stuid and a1.subjcode='080' and
         p.schyear=a1.schyear and p.timeseq=a1.timeseq
    left outer join tb_asr_subjassessdata a2
      on b.suid=a2.suid and b.stuid=a2.stuid and a2.subjcode='165' and
         p.schyear=a2.schyear and p.timeseq=a2.timeseq
    left outer join tb_asr_subjassessdata a3
      on b.suid=a3.suid and b.stuid=a3.stuid and a3.subjcode='280' and
         p.schyear=a3.schyear and p.timeseq=a3.timeseq

    where
      b.classlvl=? and
      b.classcode<>''
    order by b.classcode, b.classno


    系統及資訊管理組

     
    #8 EDB-EddieKwan, 2006-07-07
  9. csk-auco

    csk-auco
    Expand Collapse

    如果想再加操行及級名次, 可不可以?
     
    #9 csk-auco, 2006-07-10
  10. 45335977

    CCtest
    Expand Collapse

    文章:
    0
    讚:
    0
  11. 45335977

    CCtest
    Expand Collapse

    文章:
    0
    讚:
    0

    操行和級名次可在 table: TB_ASR_STUDASSESSDATA 的這兩個 fields

    1)OMCLASSLVL

    2)OVERCONDGRADECONVERSIONCOMPCODE

    內找到,妳可試試把它們加進SQL 內再做JOIN 得出

     
  12. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0

    csk-auco君:

    可試試以下SQL語句。

    select
      c.classcode, c.classno, b.chname, b.enname,
      a1.sysscore as 'Chi', a2.sysscore as 'Eng', a3.sysscore as 'Math',
      d1.syspercscore as 'Average', d1.omclasslvl as 'OMF',
      d1.overcondgradeconversioncompcode as 'Conduct',
      ifnull(p.passing,0,p.passing) as 'Pass'
    from tb_stu_student b

    left outer join tb_stu_stuschrec c
      on b.suid=c.suid and b.stuid=c.stuid

    join
     (select d.suid, d.stuid, d.schyear, d.schlevel, d.schsession, d.timeseq,
             count(d.stuid) as 'passing'
      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_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.schlevel, d.schsession, d.timeseq
     ) p
    on b.suid=p.suid and b.stuid=p.stuid and
       c.schyear=p.schyear and c.schlvl=p.schlevel and c.schsess=p.schsession

    left outer join tb_asr_studassessdata d1
      on b.suid=d1.suid and b.stuid=d1.stuid and
         c.schyear=d1.schyear and c.schlvl=d1.schlevel and
         c.schsess=d1.schsession and p.timeseq=d1.timeseq
    left outer join tb_asr_subjassessdata a1
      on b.suid=a1.suid and b.stuid=a1.stuid and a1.subjcode='080' and
         c.schyear=a1.schyear and c.schlvl=a1.schlevel and
         c.schsess=a1.schsession and p.timeseq=a1.timeseq
    left outer join tb_asr_subjassessdata a2
      on b.suid=a2.suid and b.stuid=a2.stuid and a2.subjcode='165' and
         c.schyear=a2.schyear and c.schlvl=a2.schlevel and
         c.schsess=a2.schsession and p.timeseq=a2.timeseq
    left outer join tb_asr_subjassessdata a3
      on b.suid=a3.suid and b.stuid=a3.stuid and a3.subjcode='280' and
         c.schyear=a3.schyear and c.schlvl=a3.schlevel and
         c.schsess=a3.schsession and p.timeseq=a3.timeseq

    where
      c.classlvl=?
    order by c.classcode, c.classno


    系統及資訊管理組 

     
    #12 EDB-EddieKwan, 2006-07-10
  13. csk-auco

    csk-auco
    Expand Collapse

    [parsehtml]<P><FONT color=#006600>timeseq= 1000 輸出全年的成績. 但不能顯示操行.</FONT></P> <P><FONT color=#006600>因為我們的操行只輸入T1及T2, 沒有輸入全年.</FONT></P> <P>&nbsp;</P>[/parsehtml]
     
    #13 csk-auco, 2006-07-11
  14. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0

    csk-auco君:

    閣下可再試試以下語句。

    select
      c.classcode, c.classno, b.chname, b.enname,
      a1.sysscore as 'Chi', a2.sysscore as 'Eng', a3.sysscore as 'Math',
      d1.syspercscore as 'Average', d1.omclasslvl as 'OMF',
      dc1.overcondgradeconversioncompcode as 'Conduct T1',
      dc2.overcondgradeconversioncompcode as 'Conduct T2',
      ifnull(p.passing,0,p.passing) as 'Pass'
    from tb_stu_student b

    left outer join tb_stu_stuschrec c
      on b.suid=c.suid and b.stuid=c.stuid

    join
     (select d.suid, d.stuid, d.schyear,
             d.schlevel, d.schsession, d.classlevel,
             d.timeseq, count(d.stuid) as 'passing'
      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.schlevel=a.schlevel and
           d.schsession=a.schsession and d.classlevel=a.classlevel
           and d.timeseq=a.timeseq
      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=1000
      group by d.suid, d.stuid, d.schyear,
               d.schlevel, d.schsession, d.classlevel, d.timeseq
     ) p
    on b.suid=p.suid and b.stuid=p.stuid and
       c.schyear=p.schyear and c.schlvl=p.schlevel and
       c.schsess=p.schsession and c.classlvl=p.classlevel

    left outer join tb_asr_studassessdata d1
      on b.suid=d1.suid and b.stuid=d1.stuid and
         c.schyear=d1.schyear and c.schlvl=d1.schlevel and
         c.schsess=d1.schsession and p.timeseq=d1.timeseq

    left outer join tb_asr_studassessdata dc1
      on b.suid=dc1.suid and b.stuid=dc1.stuid and
         c.schyear=dc1.schyear and c.schlvl=dc1.schlevel and
         c.schsess=dc1.schsession and c.classlvl=dc1.classlevel and
         dc1.timeseq=1100
    left outer join tb_asr_studassessdata dc2
      on b.suid=dc2.suid and b.stuid=dc2.stuid and
         c.schyear=dc2.schyear and c.schlvl=dc2.schlevel and
         c.schsess=dc2.schsession and c.classlvl=dc2.classlevel and
         dc2.timeseq=1200

    left outer join tb_asr_subjassessdata a1
      on b.suid=a1.suid and b.stuid=a1.stuid and a1.subjcode='080' and
         c.schyear=a1.schyear and c.schlvl=a1.schlevel and
         c.schsess=a1.schsession and c.classlvl=a1.classlevel and
         p.timeseq=a1.timeseq
    left outer join tb_asr_subjassessdata a2
      on b.suid=a2.suid and b.stuid=a2.stuid and a2.subjcode='165' and
         c.schyear=a2.schyear and c.schlvl=a2.schlevel and
         c.schsess=a2.schsession and c.classlvl=a2.classlevel and
         p.timeseq=a2.timeseq
    left outer join tb_asr_subjassessdata a3
      on b.suid=a3.suid and b.stuid=a3.stuid and a3.subjcode='280' and
         c.schyear=a3.schyear and c.schlvl=a3.schlevel and
         c.schsess=a3.schsession and c.classlvl=a3.classlevel and
         p.timeseq=a3.timeseq
    where
      c.classlvl=?
    order by c.classcode, c.classno

    系統及資訊管理組

     
    #14 EDB-EddieKwan, 2006-07-11
  15. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0

    csk-auco君:

    上面的SQL語句已稍作修改,閣下可再行試試。

    select
      c.classcode, c.classno, b.chname, b.enname,
      a1.sysscore as 'Chi', a2.sysscore as 'Eng', a3.sysscore as 'Math',
      d1.syspercscore as 'Average', d1.omclasslvl as 'OMF',
      dc1.overcondgradeconversioncompcode as 'Conduct T1',
      dc2.overcondgradeconversioncompcode as 'Conduct T2',
      isnull(p.pass,0) as 'Pass'
    from tb_stu_student b

    left outer join tb_stu_stuschrec c
      on b.suid=c.suid and b.stuid=c.stuid

    left outer join tb_asr_studassessdata d1
      on c.suid=d1.suid and c.stuid=d1.stuid and
         c.schyear=d1.schyear and c.schlvl=d1.schlevel and
         c.schsess=d1.schsession and c.classlvl=d1.classlevel and
         d1.timeseq=1000

    left outer join tb_asr_subjassessdata a1
      on c.suid=a1.suid and c.stuid=a1.stuid and a1.subjcode='080' and
         c.schyear=a1.schyear and c.schlvl=a1.schlevel and
         c.schsess=a1.schsession and c.classlvl=a1.classlevel and
         a1.timeseq=1000
    left outer join tb_asr_subjassessdata a2
      on c.suid=a2.suid and c.stuid=a2.stuid and a2.subjcode='165' and
         c.schyear=a2.schyear and c.schlvl=a2.schlevel and
         c.schsess=a2.schsession and c.classlvl=a2.classlevel and
         a2.timeseq=1000
    left outer join tb_asr_subjassessdata a3
      on c.suid=a3.suid and c.stuid=a3.stuid and a3.subjcode='280' and
         c.schyear=a3.schyear and c.schlvl=a3.schlevel and
         c.schsess=a3.schsession and c.classlvl=a3.classlevel and
         a3.timeseq=1000

    left outer join tb_asr_studassessdata dc1
      on c.suid=dc1.suid and c.stuid=dc1.stuid and
         c.schyear=dc1.schyear and c.schlvl=dc1.schlevel and
         c.schsess=dc1.schsession and c.classlvl=dc1.classlevel and
         dc1.timeseq=1100
    left outer join tb_asr_studassessdata dc2
      on c.suid=dc2.suid and c.stuid=dc2.stuid and
         c.schyear=dc2.schyear and c.schlvl=dc2.schlevel and
         c.schsess=dc2.schsession and c.classlvl=dc2.classlevel and
         dc2.timeseq=1200

    left outer join
     (select a.suid, a.stuid, a.schyear,
             a.schlevel, a.schsession, a.classlevel,
             count(a.stuid) as 'pass'
      from tb_asr_subjassessdata a
      left outer join tb_asr_clslvlsubjsetting s
        on a.suid=s.suid and a.schyear=s.schyear and
           a.schlevel=s.schlevel and a.schsession=s.schsession and
           a.classlevel=s.classlevel and a.timeseq=s.timeseq and
           a.subjcode=s.subjcode
      left outer join tb_asr_gradeconversioncomp g
        on a.suid=g.suid and a.schyear=g.schyear and
           a.sysgradeconvseq=g.gradeconversionseq and
           a.sysgradeconvcompcode=g.gradeconversioncompcode
      left outer join tb_asr_gradeconversioncomp gp
        on a.suid=gp.suid and a.schyear=gp.schyear and
           a.sysgradeconvseq=gp.gradeconversionseq and
           gp.passinggradeind='Y'
      where s.bygradeind='N' and
            g.absolutegrademark>=gp.absolutegrademark and
            a.timeseq=1000
      group by a.suid, a.stuid, a.schyear, a.schlevel, a.schsession, a.classlevel
     ) p
    on c.suid=p.suid and c.stuid=p.stuid and
       c.schyear=p.schyear and c.schlvl=p.schlevel and
       c.schsess=p.schsession and c.classlvl=p.classlevel

    where c.schyear=? and c.classlvl=?
    order by c.classcode, c.classno


    系統及資訊管理組

     
    #15 EDB-EddieKwan, 2006-07-12
  16. 55717559

    tr-gilbertng
    Expand Collapse

    文章:
    11
    讚:
    0
    謝謝系統及資訊管理組的同工,

    假若我想要 "每班"的合格科目數目, 但不計算用grade的科目, SQL應怎寫呢?

    Gilbert
     
    #16 tr-gilbertng, 2013-06-28
  17. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    老師請參考以下的 SQL 語句。

    select
    b.classlvl,
    b.classcode,
    sum(ifnull(p.passing,0,p.passing)) 'passing'
    from tb_stu_student b
    left outer join (select d.suid, d.stuid, count(d.stuid) 'passing'
    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_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) p
    on b.suid=p.suid and b.stuid=p.stuid
    where b.classcode <> ''
    group by b.classlvl, b.classcode
    order by b.classlvl, b.classcode
     
    #17 edb-石頭, 2013-07-02
  18. 55717559

    tr-gilbertng
    Expand Collapse

    文章:
    11
    讚:
    0
    這兩年都相安無事, 但今年當我執行以上SQL時, 中四合格科目的數目便不對了, 久經搜尋之下便發現今年中四出現了數學科(必修)有中文班及英文班
    ,即同一級某些班別使用中文, 另一些用英文(不知是否這原因), 請問以上SQL是否和此有關係, (他們使用相同的code) ? 謝謝!
     
    #18 tr-gilbertng, 2014-07-02
  19. 57686791

    edb-marmel
    Expand Collapse

    文章:
    187
    讚:
    0
    請提供中四合格科目內容出現問題的資料(例如:合格科目列為不合格科目/不合格科目列為合格科目等),若有學生的個人資料,煩請先刪除,謝謝!
     
    #19 edb-marmel, 2014-07-03
  20. 55717559

    tr-gilbertng
    Expand Collapse

    文章:
    11
    讚:
    0

    我使用了如下的sql來抽取學生合格的科目(只計算有分數的, 不計算以grade做結果的科目)
    代碼:
    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(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
    但出來的結果是:
    4A 1 xxx M 化學,中國語文,經濟,英國語文,數學(必修部分),數學(必修部分),通識教育
    4A 2 xxx M 化學,中國語文,經濟,英國語文,數學(必修部分),數學(必修部分),通識教育
    4A 3 xxx M 企業、會計與財務概論,經濟,英國語文,通識教育
    4A 4 xxx M 化學,中國語文,英國語文,數學(必修部分),數學(必修部分),通識教育,物理
    4A 5 xxx M 英國語文,地理,數學(必修部分),數學(必修部分),旅遊與款待
    4A 6 xxx M 化學,中國語文,英國語文,數學(必修部分),數學(必修部分),通識教育,物理
    4A 7 xxx 化學,中國語文,英國語文,數學(必修部分),數學(必修部分),通識教育,物理

    數學(必修部分)出現了兩次

    是什麼地方出了問題? 中五沒有此問題 (兩級的分別是中五全級用中文作教學語言, 中四則有3班英文,1班中文)

    謝謝
     
    #20 tr-gilbertng, 2014-07-07