請試試下列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.performancefrom vw_stu_lateststudent zjoin (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 aleft outer join tb_naa_naaoffered b on a.suid=b.suid and a.naaofferedid=b.naaofferedidleft 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 ajoin vw_stu_lateststudent b on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyearjoin tb_stu_anpscheme d on a.suid=d.suid and a.schyear=d.schyearleft outer join tb_naa_naaoffered e on a.suid=e.suid and a.schyear=e.schyear and a.naaofferedid=e.naaofferedidleft outer join tb_naa_studentnaa f on a.suid=f.suid and a.stuid=f.stuid and a.naaofferedid=f.naaofferedidleft 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.anprecidleft 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=中一)