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

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

 

請試試下列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=中一)

 

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

謝。

 

老師請參考
http://forum.hkedcity.net/viewthread.php?tid=143237&extra=page=1&fid=71

 

回覆 2# EDB-Wayne


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

 

老師請聯絡websams helpdesk 3125 8510 ，以方便跟進問題。