老師請參考以下的 SQL 語句。 select schoolyear '學年', classlevel '級別', classcode '班別', classno '班號', chname '中文姓名', enname '英文姓名', Types, years '日期', description '說明' from (select b.schyear 'Schoolyear', b.classlvl 'classlevel', b.classcode 'classcode', b.classno 'classno', b.chname 'chname', b.enname 'enname', '校內' 'Types', cast(dateformat(a.anpdate, 'YYYY') as text) 'years', ((case when trim(a.prefix)<>'' then a.prefix else '' end) + (case when trim(a.descriptionstr)<>'' then a.descriptionstr else '' end) + (case when trim(a.descriptioncode)<>'' then c.ch_des else '' end) + (case when trim(a.position)<>'' then a.position else '' end) + (case when trim(a.naaofferedid)<>'' then e.ch_des else '' end)) 'description' 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 left outer join tb_naa_naaoffered d on a.suid=d.suid and a.schyear=d.schyear and a.naaofferedid=d.naaofferedid left outer join tb_hse_common e on a.suid=e.suid and d.naacode=e.code_id and ((e.tb_id='ECACD' and d.naatype='E') or (e.tb_id='SEVPST' and d.naatype='S') or (e.tb_id='INTERSCH' and d.naatype='I')) left outer join tb_hse_common c on a.suid=c.suid and a.descriptioncode=c.code_id and c.tb_id='PRIZE' where a.anptype='A' union all select d1.schyear, d1.classlvl , d1.classcode , d1.classno , d1.chname , d1.enname , '校外' , c1.STARTYR , c1.PROG || ' - ' ||c1.PROG_DES from TB_SLP_STU_PERFME_OUTSCH c1 join vw_stu_lateststudent d1. on c1.suid=d1.suid and c1.stuid=d1.stuid) act where schoolyear=? and classlevel = ? order by classcode, classno
老師請再參考以下的 SQL 語句。 select schoolyear '學年', classlevel '級別', classcode '班別', classno '班號', chname '中文姓名', enname '英文姓名', Types, years '日期', description '說明' from (select b.schyear 'Schoolyear', b.classlvl 'classlevel', b.classcode 'classcode', b.classno 'classno', b.chname 'chname', b.enname 'enname', '校內' 'Types', cast(dateformat(a.anpdate, 'YYYY') as text) 'years', ((case when trim(a.prefix)<>'' then a.prefix else '' end) + (case when trim(a.descriptionstr)<>'' then a.descriptionstr else '' end) + (case when trim(a.descriptioncode)<>'' then c.ch_des else '' end) + (case when trim(a.position)<>'' then a.position else '' end) + (case when trim(a.naaofferedid)<>'' then e.ch_des else '' end)) 'description' 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 left outer join tb_naa_naaoffered d on a.suid=d.suid and a.schyear=d.schyear and a.naaofferedid=d.naaofferedid left outer join tb_hse_common e on a.suid=e.suid and d.naacode=e.code_id and ((e.tb_id='ECACD' and d.naatype='E') or (e.tb_id='SEVPST' and d.naatype='S') or (e.tb_id='INTERSCH' and d.naatype='I')) left outer join tb_hse_common c on a.suid=c.suid and a.descriptioncode=c.code_id and c.tb_id='PRIZE' where a.anptype='A' union all select d1.schyear, d1.classlvl , d1.classcode , d1.classno , d1.chname , d1.enname , '校外' , c1.STARTYR , c1.PROG || ' - ' ||c1.PROG_DES from TB_SLP_STU_PERFME_OUTSCH c1 join vw_stu_lateststudent d1 on c1.suid=d1.suid and c1.stuid=d1.stuid) act where schoolyear=? and classlevel = ? order by classcode, classno
"校內獎項"是抽取"獎懲資料"模組內所輸入的學生獎項資料, 而 SLP 報表是用 stored procedure 抽取資料, SQL 並不能像 stored procedure 般抽取資料, 應該比較"獎懲資料"模組內的資料.
1)請老師提供操行的詳細資料 2) award1,award2,act1,act2 etc...SQL並不支援varable columns 功能, 建議老師用crystal report中cross tab功能.