如何用SQL抽取學生歷年在校時曾擔任的服務,獲得的獎項及獎懲資料

本文由 發奮 在 2009-10-23 發表於 "WebSAMS 討論區" 討論區

  1. 55002479

    發奮
    Expand Collapse

    文章:
    74
    讚:
    0
    如何用SQL抽取學生歷年在校時曾擔任的服務,獲得的獎項及獎懲資料(需顯示當時的學年及就讀的班別,並按本學年就讀的班別及學號排列)?謝謝!
     
  2. 56127239

    EDB-Wayne
    Expand Collapse

    文章:
    0
    讚:
    0

    請試試下列sql是否合用:

    select
       z.schyear,
       z.classcode,
       z.classno,
       z.chname,
       a.schyear,
       a.classcode,
       a.classno,
       a.naatype,
       a.duration,
       a.programme,
       a.post,
       a.performance
    from vw_stu_lateststudent z
    join
    (select
       b.suid,
       b.schyear,
       f.classcode,
       f.classno,
       f.chname,
       '學期' || cast(b.duration as text) 'duration',
       a.stuid,
       (case when b.naatype='E' then '課外活動'
                when b.naatype='I' then '校際活動'
                when b.naatype='S' then '服務'
                 else '' end) 'naatype',
       b.naacode,
       c.ch_des 'programme',
       d.ch_des 'post',
       e.ch_des 'performance'
    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 a.suid=c.suid and b.naacode=c.code_id 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 a.naapostcode=d.code_id and d.tb_id='ECAPST'
    left outer join tb_hse_common e
       on a.suid=e.suid and a.naaperfcode=e.code_id and e.tb_id='ECAPFM'
    join vw_stu_lateststudent f
       on a.suid=f.suid and a.stuid=f.stuid and b.schyear=f.schyear

    union all
     
    select
       b.suid,
       b.schyear,
       b.classcode,
       b.classno,
       b.chname,
       dateformat(a.anpdate, 'DD/MM/YYYY') 'duration',
       a.stuid,
       (case when a.anptype='A' then '獎勵'
                when a.anptype='P' then '懲罰'
                 else '' end),
       null,
       (case when trim(a.PREFIX) <> ''
                   then trim(a.PREFIX)
                   else ''
              end) +
             (case when trim(a.PREFIX) <> '' and
                       (right(trim(a.PREFIX),1) between char(33) and char(126) or
                        left(trim(a.DESCRIPTIONSTR),1) between char(33) and char(126) or
                        left(trim(h.CH_DES),1) between char(33) and char(126))
                   then ' '
                   else ''
              end) +    
             (case when g.TOTAL>0
                   then (case when g.NONATTTYPE = 'ABSNT'
                              then '缺席' + cast(cast(g.TOTAL as numeric(10,1)) as text) + '天'
                              else '遲到' + cast(g.TOTAL as text) + '次'
                         end)
                   when a.NAAOFFEREDID>0
                   then (case when f.NAAPOSTCODE is null and f.NAAPERFCODE is null
                              then i.CH_DES
                              when f.NAAPOSTCODE is not null and f.NAAPERFCODE is null
                              then i.CH_DES + j.CH_DES
                              when f.NAAPOSTCODE is null and f.NAAPERFCODE is not null
                              then i.CH_DES + ' (' + k.CH_DES + ')'
                              else i.CH_DES + j.CH_DES + ' (' + k.CH_DES + ')'
                         end)
                   else (case when a.DESCRIPTIONCODE is not null and trim(a.DESCRIPTIONCODE) <> ''
                              then trim(h.CH_DES)
                              else trim(a.DESCRIPTIONSTR)
                         end)
              end) 'programme',
       (case when trim(a.POSITION) <> '' and
                        (right(trim(a.DESCRIPTIONSTR),1) between char(33) and char(126) or
                         right(trim(h.CH_DES),1) between char(33) and char(126) or
                         left(trim(a.POSITION),1) between char(33) and char(126))
                   then ' '
                   else ''
              end) + 
             (case when trim(a.POSITION) <> ''
                   then trim(a.POSITION)
                   else ''
              end) 'post',
        (case when d.SCHEME = 'M'
                   then (case when a.LVL1>0
                              then ' ' + (case when a.ANPTYPE = 'A' then n.AWD1CHIDESC
                                   else n.PUNISH1CHIDESC end) + ' ' + cast(a.LVL1 as text) + ' 次'
                              else ''
                         end) +
                        (case when a.LVL2>0
                              then ' ' + (case when a.ANPTYPE = 'A' then n.AWD2CHIDESC
                                   else n.PUNISH2CHIDESC end) + ' ' + cast(a.LVL2 as text) + ' 次'
                              else ''
                         end) +
                        (case when a.LVL3>0
                              then ' ' + (case when a.ANPTYPE = 'A' then n.AWD3CHIDESC
                                   else n.PUNISH3CHIDESC end) + ' ' + cast(a.LVL3 as text) + ' 次'
                              else ''
                         end) +
                        (case when a.LVL4>0
                              then ' ' + (case when a.ANPTYPE = 'A' then n.AWD4CHIDESC
                                   else n.PUNISH4CHIDESC end) + ' ' + cast(a.LVL4 as text) + ' 次'
                              else ''
                         end) +
                        (case when a.LVL5>0
                              then ' ' + (case when a.ANPTYPE = 'A' then n.AWD5CHIDESC
                                   else n.PUNISH5CHIDESC end) + ' ' + cast(a.LVL5 as text) + ' 次'
                              else ''
                         end)        
                   else ''
              end) 'performance'
    from tb_stu_anpsturec a
    join vw_stu_lateststudent b
       on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear
    join tb_stu_anpscheme d
       on a.suid=d.suid and a.schyear=d.schyear
    left outer join tb_naa_naaoffered e
       on a.suid=e.suid and a.schyear=e.schyear and a.naaofferedid=e.naaofferedid
    left outer join tb_naa_studentnaa f
       on a.suid=f.suid and a.stuid=f.stuid and a.naaofferedid=f.naaofferedid
    left outer join (select suid, schyear, stuid, anprecid, nonatttype, nonattcount, sum(nonattcount) 'total'
                           from tb_att_nonatt
                           where anprecid is not null
                           group by suid, schyear, stuid, anprecid, nonatttype, nonattcount) g
       on a.suid=g.suid and a.schyear=g.schyear and a.stuid=g.stuid and a.anprecid=g.anprecid
    left outer join tb_hse_common h
       on a.suid=h.suid and a.descriptioncode=h.code_id and ((a.anptype='A' and h.tb_id='PRIZE') or (a.anptype='P' and h.tb_id='PSHMNT'))
    left outer join tb_hse_common i
       on a.suid=i.suid and e.naacode=i.code_id and ((i.tb_id='ECACD' and e.naatype='E') or (i.tb_id='INTERSCH' and e.naatype='I') or (i.tb_id='SEVPST' and e.naatype='S'))
    left outer join tb_hse_common j
       on a.suid=j.suid and f.naapostcode=j.code_id and j.tb_id='ECAPST'
    left outer join tb_hse_common k
       on a.suid=k.suid and f.naaperfcode=k.code_id and k.tb_id='ECAPFM'
    left outer join tb_stu_anpschemedetailmd n
       on a.suid=n.suid and a.schyear=n.schyear) a
       on a.suid=z.suid and a.stuid=z.stuid and z.schyear=? and z.classlvl=?
    order by z.classcode, z.classno, z.enname, a.schyear

    參數:z.schyear=本學年,z.classlvl=級別(p1=小一、s1=中一)

     
    #2 EDB-Wayne, 2009-10-27
  3. 10218215

    Mr.Stone
    Expand Collapse

    文章:
    29
    讚:
    0
    上述sql可成功出到學生歷年的資料,我想詢問一下,如我我不需要「課外活動、校際活動及服務」等資料,只需要獎懲的學生歷年記錄,可以把sql如何刪減編輯?

    謝。
     
    #3 Mr.Stone, 2015-05-15
  4. 57678715

    edb-escm
    Expand Collapse

    文章:
    434
    讚:
    0
  5. 10873736

    ps-ncw
    Expand Collapse

    文章:
    108
    讚:
    0
    回覆 2# EDB-Wayne


    你好, 我試用這個sql, 但發覺執行了超過15分鐘, 仍未有結果, 請為何呢?
    謝謝!
     
  6. 58019970

    edb-jackal
    Expand Collapse

    文章:
    465
    讚:
    0
    老師請聯絡websams helpdesk 3125 8510 ,以方便跟進問題。
     
    #6 edb-jackal, 2016-03-02