抽取學生在學時之所有課外活動及服務以製作 testimonials

本文由 tr-msyim 在 2007-06-04 發表於 "WebSAMS 討論區" 討論區

  1. 55346824

    tr-msyim
    Expand Collapse

    文章:
    0
    讚:
    0

    我校急需抽取學生在學時之所有課外活動及服務以製作 testimonials
    但我校之課外活動一概沒有輸入學生表現的,
    故此,我把以下的 SQL 抄入 WebSAMS 內。
    語法似無誤,但當要真正抽取(如只是兩班)學生之課外活動及服務時,
    整個系統便立即當機。
    現煩請各位同工細閱以下 SQL
    看看可否幫忙告知問題出自何處。
    謝謝。

     

    select

        b.CLASSLVL,

        b.CLASSCODE,

        b.CLASSNO,

        c.ENNAME,

        c.CHNAME,

        a.SCHYEAR,

        a.PROGRAMME_EN,

        a.POST_EN

    from

    (

        select

          b.SUID,

          b.SCHYEAR,

          b.DURATION,

          a.STUID,

          b.NAATYPE,

          b.NAACODE,

          c.CH_DES 'PROGRAMME_CH',

          c.EN_DES 'PROGRAMME_EN',

          d.CH_DES 'POST_CH',

          d.EN_DES 'POST_EN'

        from TB_NAA_STUDENTNAA a

          left outer join TB_NAA_NAAOFFERED b on

            a.SUID = b.SUID

            and a.NAAOFFEREDID = b.NAAOFFEREDID

          left outer join TB_HSE_COMMON c on

            b.NAACODE = c.CODE_ID

            and a.SUID = c.SUID

            and ((c.TB_ID = 'ECACD' and b.NAATYPE = 'E') or

                (c.TB_ID = 'INTERSCH' and b.NAATYPE = 'I') or

                (c.TB_ID = 'SEVPST' and b.NAATYPE = 'S'))

          left outer join TB_HSE_COMMON d on

            a.SUID = d.SUID

            and d.TB_ID = 'ECAPST'

            and a.NAAPOSTCODE = d.CODE_ID

    )a

    left outer join TB_STU_STUSCHREC b on

        a.SUID = b.SUID

        and a.STUID = b.STUID

    left outer join TB_STU_STUDENT c on

        a.SUID = c.SUID

        and a.STUID = c.STUID

    where

        b.CLASSCODE >= ?

        and b.CLASSCODE <= ?

    order by

        b.CLASSLVL,

        b.CLASSCODE,

        b.CLASSNO,

        a.SCHYEAR desc,

        a.PROGRAMME_EN

     

    請注意:
    我校之課外活動及服務的 codes 有重覆,
    例如,5120既是課外活動的「關注中國組」亦是服務的「Prefect」。

     
    #1 tr-msyim, 2007-06-04
  2. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0
    <FONT color=#006600>tr-msyim 君:<BR><BR>經過測試,並沒有出現當機的問題。可能只是速度較慢而已。<BR><BR>系統及資訊管理組</FONT>
     
    #2 EDB-EddieKwan, 2007-06-05
  3. 45331765

    EDB-EddieKwan
    Expand Collapse

    文章:
    0
    讚:
    0

    tr-msyim 君:

    閣下可試試以下稍作修改的 SQL 語句。


    select
        f.CLASSLVL,
        f.CLASSCODE,
        f.CLASSNO,
        f.ENNAME,
        f.CHNAME,
        b.SCHYEAR,
        c.EN_DES,
        d.EN_DES
    from TB_NAA_STUDENTNAA a

    left outer join TB_NAA_NAAOFFERED b
      on a.SUID = b.SUID and a.NAAOFFEREDID = b.NAAOFFEREDID

    left outer join TB_HSE_COMMON c
      on b.NAACODE = c.CODE_ID and b.SUID = c.SUID and
         ((c.TB_ID = 'ECACD' and b.NAATYPE = 'E') or
          (c.TB_ID = 'INTERSCH' and b.NAATYPE = 'I') or
          (c.TB_ID = 'SEVPST' and b.NAATYPE = 'S'))

    left outer join TB_HSE_COMMON d
      on a.SUID = d.SUID and d.TB_ID = 'ECAPST' and
         a.NAAPOSTCODE = d.CODE_ID

    left outer join TB_STU_STUDENT f on
        a.SUID = f.SUID and a.STUID = f.STUID
    where
        f.CLASSCODE >= ?
        and f.CLASSCODE <= ?
    order by
        f.CLASSLVL,
        f.CLASSCODE,
        f.CLASSNO,
        b.SCHYEAR desc,
        c.EN_DES

    系統及資訊管理組

     
    #3 EDB-EddieKwan, 2007-06-05
  4. 55169024

    EDB-Jeff Tong
    Expand Collapse

    文章:
    0
    讚:
    0
    你忘了加入School year,所以歷年來的班都被抽出。

    在where子句加入以下一行:

    and b.SCHYEAR = ?

    或使用我的修訂:

    select
        b.SCHYEAR '學年',
        b.CLASSLVL '級別',
        b.CLASSCODE '班別',
        b.CLASSNO '班號',
        c.CHNAME '中文姓名',
        c.ENNAME '英文姓名',
        a.SCHYEAR '活動學年',
        d.CLASSLVL '活動學年級別',
        d.CLASSCODE '活動學年班別', 
        a.DURATION '時段',
        a.PROGRAMME_CH '活動名稱',
        a.POST_CH '職位'
    from
    (
        select
          b.SUID, b.SCHYEAR, b.DURATION, a.STUID, b.NAATYPE, b.NAACODE,
          c.CH_DES 'PROGRAMME_CH', c.EN_DES 'PROGRAMME_EN',
          d.CH_DES 'POST_CH', d.EN_DES 'POST_EN'
        from TB_NAA_STUDENTNAA a
          left outer join TB_NAA_NAAOFFERED b on a.SUID = b.SUID
            and a.NAAOFFEREDID = b.NAAOFFEREDID
          left outer join TB_HSE_COMMON c on
            b.NAACODE = c.CODE_ID and a.SUID = c.SUID and
            ((c.TB_ID = 'ECACD' and b.NAATYPE = 'E') or
            (c.TB_ID = 'INTERSCH' and b.NAATYPE = 'I') or
            (c.TB_ID = 'SEVPST' and b.NAATYPE = 'S'))
          left outer join TB_HSE_COMMON d on
            a.SUID = d.SUID and d.TB_ID = 'ECAPST' and
            a.NAAPOSTCODE = d.CODE_ID
    )a
    left outer join TB_STU_STUSCHREC b
        on a.SUID = b.SUID and a.STUID = b.STUID
    left outer join TB_STU_STUDENT c
        on a.SUID = c.SUID and a.STUID = c.STUID
    left outer join TB_STU_STUSCHREC d
        on a.SUID = d.SUID and a.STUID = d.STUID and a.SCHYEAR = d.SCHYEAR
    where b.SCHYEAR = ? and b.CLASSLVL = ?
    order by b.SCHSESS, b.CLASSLVL, b.CLASSCODE, b.CLASSNO, a.SCHYEAR, a.DURATION, a.NAACODE
    

    Jeff Tong

     
    #4 EDB-Jeff Tong, 2007-06-05
  5. 55346824

    tr-msyim
    Expand Collapse

    文章:
    0
    讚:
    0
    <FONT size=4>謝謝兩位高人指點,問題經已解決。感激萬分。</FONT>
     
    #5 tr-msyim, 2007-06-05
  6. 10873736

    ps-ncw
    Expand Collapse

    文章:
    108
    讚:
    0
    回覆 4# EMB-Jeff Tong


    請問可否加入「表現」嗎?
     
  7. 57678715

    edb-escm
    Expand Collapse

    文章:
    434
    讚:
    0
    #7 edb-escm, 2015-08-24
  8. 57678715

    edb-escm
    Expand Collapse

    文章:
    434
    讚:
    0