SQL Form 6 merits of the past six years

Could you please tell me how to extract the merits information of current form 6 students in the past six years?

Thank you very much!

 

老師請參考以下SQL:

 

Thanks a lot!

 

Is it possible to include the English description of each event and the event code?
Many thanks!

 

老師請參考以下sql:

 

如果想只抽取某一學生
例如
1) 以2014年度6A 23號輸入

2) 另外以STRN輸入
報表1
報表2
2種方式輸入找出資料,謝謝!

 

回覆 6# Stephen

老師請參考以下SQL:
1.
select
z.schyear,
z.classcode,
z.classno,
z.chname,
a.schyear,
a.classcode,
a.classno,
a.anptype,
a.duration,
a.event,
a.enevent,
a.post,
a.NetANP,
a.NetENANP
from wsadmin.vw_stu_lateststudent z
join
(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) 'anptype',
(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) 'event',
(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 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.EN_DES
when f.NAAPOSTCODE is not null and f.NAAPERFCODE is null
then i.EN_DES + j.EN_DES
when f.NAAPOSTCODE is null and f.NAAPERFCODE is not null
then i.EN_DES + ' (' + k.EN_DES + ')'
else i.EN_DES + j.EN_DES + ' (' + k.EN_DES + ')'
end)
else (case when a.DESCRIPTIONCODE is not null and trim(a.DESCRIPTIONCODE) <> ''
then trim(h.EN_DES)
else trim(a.DESCRIPTIONSTR)
end)
end) 'enevent',
(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) 'NetANP',
(case when d.SCHEME = 'M'
then (case when a.LVL1>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD1ENGDESC
else n.PUNISH1ENGDESC end) + ' ' + cast(a.LVL1 as text) + ' 次'
else ''
end) +
(case when a.LVL2>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD2ENGDESC
else n.PUNISH2ENGDESC end) + ' ' + cast(a.LVL2 as text) + ' 次'
else ''
end) +
(case when a.LVL3>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD3ENGDESC
else n.PUNISH3ENGDESC end) + ' ' + cast(a.LVL3 as text) + ' 次'
else ''
end) +
(case when a.LVL4>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD4ENGDESC
else n.PUNISH4ENGDESC end) + ' ' + cast(a.LVL4 as text) + ' 次'
else ''
end) +
(case when a.LVL5>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD5ENGDESC
else n.PUNISH5ENGDESC end) + ' ' + cast(a.LVL5 as text) + ' 次'
else ''
end)
else ''
end) 'NetENANP'
from wsadmin.tb_stu_anpsturec a
join wsadmin.vw_stu_lateststudent b
on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear
join wsadmin.tb_stu_anpscheme d
on a.suid=d.suid and a.schyear=d.schyear
left outer join wsadmin.tb_naa_naaoffered e
on a.suid=e.suid and a.schyear=e.schyear and a.naaofferedid=e.naaofferedid
left outer join wsadmin.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 wsadmin.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 wsadmin.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 wsadmin.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 wsadmin.tb_hse_common j
on a.suid=j.suid and f.naapostcode=j.code_id and j.tb_id='ECAPST'
left outer join wsadmin.tb_hse_common k
on a.suid=k.suid and f.naaperfcode=k.code_id and k.tb_id='ECAPFM'
left outer join wsadmin.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=? and z.classcode=? and z.classno=?
order by z.classcode, z.classno, z.enname, a.schyear,a.anptype DESC

 

2.
select
z.schyear,
z.classcode,
z.classno,
z.chname,
a.schyear,
a.classcode,
a.classno,
a.anptype,
a.duration,
a.event,
a.enevent,
a.post,
a.NetANP,
a.NetENANP
from wsadmin.vw_stu_lateststudent z
join
(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) 'anptype',
(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) 'event',
(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 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.EN_DES
when f.NAAPOSTCODE is not null and f.NAAPERFCODE is null
then i.EN_DES + j.EN_DES
when f.NAAPOSTCODE is null and f.NAAPERFCODE is not null
then i.EN_DES + ' (' + k.EN_DES + ')'
else i.EN_DES + j.EN_DES + ' (' + k.EN_DES + ')'
end)
else (case when a.DESCRIPTIONCODE is not null and trim(a.DESCRIPTIONCODE) <> ''
then trim(h.EN_DES)
else trim(a.DESCRIPTIONSTR)
end)
end) 'enevent',
(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) 'NetANP',
(case when d.SCHEME = 'M'
then (case when a.LVL1>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD1ENGDESC
else n.PUNISH1ENGDESC end) + ' ' + cast(a.LVL1 as text) + ' 次'
else ''
end) +
(case when a.LVL2>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD2ENGDESC
else n.PUNISH2ENGDESC end) + ' ' + cast(a.LVL2 as text) + ' 次'
else ''
end) +
(case when a.LVL3>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD3ENGDESC
else n.PUNISH3ENGDESC end) + ' ' + cast(a.LVL3 as text) + ' 次'
else ''
end) +
(case when a.LVL4>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD4ENGDESC
else n.PUNISH4ENGDESC end) + ' ' + cast(a.LVL4 as text) + ' 次'
else ''
end) +
(case when a.LVL5>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD5ENGDESC
else n.PUNISH5ENGDESC end) + ' ' + cast(a.LVL5 as text) + ' 次'
else ''
end)
else ''
end) 'NetENANP'
from wsadmin.tb_stu_anpsturec a
join wsadmin.vw_stu_lateststudent b
on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear
join wsadmin.tb_stu_anpscheme d
on a.suid=d.suid and a.schyear=d.schyear
left outer join wsadmin.tb_naa_naaoffered e
on a.suid=e.suid and a.schyear=e.schyear and a.naaofferedid=e.naaofferedid
left outer join wsadmin.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 wsadmin.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 wsadmin.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 wsadmin.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 wsadmin.tb_hse_common j
on a.suid=j.suid and f.naapostcode=j.code_id and j.tb_id='ECAPST'
left outer join wsadmin.tb_hse_common k
on a.suid=k.suid and f.naaperfcode=k.code_id and k.tb_id='ECAPFM'
left outer join wsadmin.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.strn=?
order by z.classcode, z.classno, z.enname, a.schyear,a.anptype DESC

 

以上兩個報表需以中文顯示,謝謝!!

 

你們協助製作的是英文,我校需要是中文顯示

 

如果想用REG NO(註冊編號)只抽取某一學生
例如
3) 以REG NO輸入 (201501011)

報表3
謝謝!

 

使用報表2,以STRN輸入,出現結果是重覆及錯亂
如以下情況

 

使用報表2,以STRN輸入,出現結果是重覆及錯亂
如以下情況

 

2.
select
z.schyear,
z.classcode,
z.classno,
z.chname,
a.schyear,
a.classcode,
a.classno,
a.anptype,
a.duration,
a.event,
a.enevent,
a.post,
a.NetANP,
a.NetENANP
from wsadmin.vw_stu_lateststudent z
join
(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) 'anptype',
(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) 'event',
(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 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.EN_DES
when f.NAAPOSTCODE is not null and f.NAAPERFCODE is null
then i.EN_DES + j.EN_DES
when f.NAAPOSTCODE is null and f.NAAPERFCODE is not null
then i.EN_DES + ' (' + k.EN_DES + ')'
else i.EN_DES + j.EN_DES + ' (' + k.EN_DES + ')'
end)
else (case when a.DESCRIPTIONCODE is not null and trim(a.DESCRIPTIONCODE) <> ''
then trim(h.EN_DES)
else trim(a.DESCRIPTIONSTR)
end)
end) 'enevent',
(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) 'NetANP',
(case when d.SCHEME = 'M'
then (case when a.LVL1>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD1ENGDESC
else n.PUNISH1ENGDESC end) + ' ' + cast(a.LVL1 as text) + ' 次'
else ''
end) +
(case when a.LVL2>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD2ENGDESC
else n.PUNISH2ENGDESC end) + ' ' + cast(a.LVL2 as text) + ' 次'
else ''
end) +
(case when a.LVL3>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD3ENGDESC
else n.PUNISH3ENGDESC end) + ' ' + cast(a.LVL3 as text) + ' 次'
else ''
end) +
(case when a.LVL4>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD4ENGDESC
else n.PUNISH4ENGDESC end) + ' ' + cast(a.LVL4 as text) + ' 次'
else ''
end) +
(case when a.LVL5>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD5ENGDESC
else n.PUNISH5ENGDESC end) + ' ' + cast(a.LVL5 as text) + ' 次'
else ''
end)
else ''
end) 'NetENANP'
from wsadmin.tb_stu_anpsturec a
join wsadmin.vw_stu_lateststudent b
on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear
join wsadmin.tb_stu_anpscheme d
on a.suid=d.suid and a.schyear=d.schyear
left outer join wsadmin.tb_naa_naaoffered e
on a.suid=e.suid and a.schyear=e.schyear and a.naaofferedid=e.naaofferedid
left outer join wsadmin.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 wsadmin.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 wsadmin.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 wsadmin.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 wsadmin.tb_hse_common j
on a.suid=j.suid and f.naapostcode=j.code_id and j.tb_id='ECAPST'
left outer join wsadmin.tb_hse_common k
on a.suid=k.suid and f.naaperfcode=k.code_id and k.tb_id='ECAPFM'
left outer join wsadmin.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 a.schyear=z.schyear and z.strn=?
order by z.classcode, z.classno, z.enname, a.schyear,a.anptype DESC

 

3.
select
z.schyear,
z.classcode,
z.classno,
z.chname,
a.schyear,
a.classcode,
a.classno,
a.anptype,
a.duration,
a.event,
a.enevent,
a.post,
a.NetANP,
a.NetENANP
from wsadmin.vw_stu_lateststudent z
join
(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) 'anptype',
(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) 'event',
(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 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.EN_DES
when f.NAAPOSTCODE is not null and f.NAAPERFCODE is null
then i.EN_DES + j.EN_DES
when f.NAAPOSTCODE is null and f.NAAPERFCODE is not null
then i.EN_DES + ' (' + k.EN_DES + ')'
else i.EN_DES + j.EN_DES + ' (' + k.EN_DES + ')'
end)
else (case when a.DESCRIPTIONCODE is not null and trim(a.DESCRIPTIONCODE) <> ''
then trim(h.EN_DES)
else trim(a.DESCRIPTIONSTR)
end)
end) 'enevent',
(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) 'NetANP',
(case when d.SCHEME = 'M'
then (case when a.LVL1>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD1ENGDESC
else n.PUNISH1ENGDESC end) + ' ' + cast(a.LVL1 as text) + ' 次'
else ''
end) +
(case when a.LVL2>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD2ENGDESC
else n.PUNISH2ENGDESC end) + ' ' + cast(a.LVL2 as text) + ' 次'
else ''
end) +
(case when a.LVL3>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD3ENGDESC
else n.PUNISH3ENGDESC end) + ' ' + cast(a.LVL3 as text) + ' 次'
else ''
end) +
(case when a.LVL4>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD4ENGDESC
else n.PUNISH4ENGDESC end) + ' ' + cast(a.LVL4 as text) + ' 次'
else ''
end) +
(case when a.LVL5>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD5ENGDESC
else n.PUNISH5ENGDESC end) + ' ' + cast(a.LVL5 as text) + ' 次'
else ''
end)
else ''
end) 'NetENANP'
from wsadmin.tb_stu_anpsturec a
join wsadmin.vw_stu_lateststudent b
on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear
join wsadmin.tb_stu_anpscheme d
on a.suid=d.suid and a.schyear=d.schyear
left outer join wsadmin.tb_naa_naaoffered e
on a.suid=e.suid and a.schyear=e.schyear and a.naaofferedid=e.naaofferedid
left outer join wsadmin.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 wsadmin.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 wsadmin.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 wsadmin.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 wsadmin.tb_hse_common j
on a.suid=j.suid and f.naapostcode=j.code_id and j.tb_id='ECAPST'
left outer join wsadmin.tb_hse_common k
on a.suid=k.suid and f.naaperfcode=k.code_id and k.tb_id='ECAPFM'
left outer join wsadmin.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 a.schyear=z.schyear and z.regno=?
order by z.classcode, z.classno, z.enname, a.schyear,a.anptype DESC

 

但是結果顯示為:
schyear ,classcode.classno.chname,schyear,classcode,classno,anptype,duration,event,enevent,post,NetANP,NetENANP

以上兩個報表2及3,可否結果只顯示為以下:
chname, schyear, classcode,classno,anptype,duration,event,enevent,post,NetANP,NetENANP

 

如果想利用學生的中文姓名或英文姓名，抽取單一學生的歷年獎懲，可以怎樣做？
表5：以中文姓名輸入抽取
表6：以英文姓名輸入抽取

 

老師可自行更改SQL以減少欄位(藍色部份)
並以不同欄位去選取資料(紅色部份)
select
z.schyear,
z.classcode,
z.classno,
z.chname,
a.schyear,
a.classcode,
a.classno,
a.anptype,
a.duration,
a.event,
a.enevent,
a.post,
a.NetANP,
a.NetENANP
from wsadmin.vw_stu_lateststudent z
join
(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) 'anptype',
(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) 'event',
(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 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.EN_DES
when f.NAAPOSTCODE is not null and f.NAAPERFCODE is null
then i.EN_DES + j.EN_DES
when f.NAAPOSTCODE is null and f.NAAPERFCODE is not null
then i.EN_DES + ' (' + k.EN_DES + ')'
else i.EN_DES + j.EN_DES + ' (' + k.EN_DES + ')'
end)
else (case when a.DESCRIPTIONCODE is not null and trim(a.DESCRIPTIONCODE) <> ''
then trim(h.EN_DES)
else trim(a.DESCRIPTIONSTR)
end)
end) 'enevent',
(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) 'NetANP',
(case when d.SCHEME = 'M'
then (case when a.LVL1>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD1ENGDESC
else n.PUNISH1ENGDESC end) + ' ' + cast(a.LVL1 as text) + ' 次'
else ''
end) +
(case when a.LVL2>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD2ENGDESC
else n.PUNISH2ENGDESC end) + ' ' + cast(a.LVL2 as text) + ' 次'
else ''
end) +
(case when a.LVL3>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD3ENGDESC
else n.PUNISH3ENGDESC end) + ' ' + cast(a.LVL3 as text) + ' 次'
else ''
end) +
(case when a.LVL4>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD4ENGDESC
else n.PUNISH4ENGDESC end) + ' ' + cast(a.LVL4 as text) + ' 次'
else ''
end) +
(case when a.LVL5>0
then ' ' + (case when a.ANPTYPE = 'A' then n.AWD5ENGDESC
else n.PUNISH5ENGDESC end) + ' ' + cast(a.LVL5 as text) + ' 次'
else ''
end)
else ''
end) 'NetENANP'
from wsadmin.tb_stu_anpsturec a
join wsadmin.vw_stu_lateststudent b
on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear
join wsadmin.tb_stu_anpscheme d
on a.suid=d.suid and a.schyear=d.schyear
left outer join wsadmin.tb_naa_naaoffered e
on a.suid=e.suid and a.schyear=e.schyear and a.naaofferedid=e.naaofferedid
left outer join wsadmin.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 wsadmin.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 wsadmin.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 wsadmin.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 wsadmin.tb_hse_common j
on a.suid=j.suid and f.naapostcode=j.code_id and j.tb_id='ECAPST'
left outer join wsadmin.tb_hse_common k
on a.suid=k.suid and f.naaperfcode=k.code_id and k.tb_id='ECAPFM'
left outer join wsadmin.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 a.schyear=z.schyear and z.regno=?
order by z.classcode, z.classno, z.enname, a.schyear,a.anptype DESC