本人先前用以下SQL抽取學生優點及校內頒發獎項資料, select b.classcode as '班級', b.classno as '班號', b.chname as '中文姓名', dateformat(a.ANPDATE, 'DD/MM/YYYY') as '日期', (case when a.DESCRIPTIONSTR is null then h.ch_des when trim(a.DESCRIPTIONCODE)<>'' then c.ch_des else a.DESCRIPTIONSTR end) as '事件',a.lvl1 as '優點',d.name_chi as '負責人', (case when a.printind='1' then 'Y' else 'N' end) as '列印示標' from tb_STU_ANPSTUREC a join vw_STU_LATESTSTUDENT b on a.stuid=b.stuid and a.suid=b.suid and a.schyear=b.schyear left outer join (select suid, CODE_ID, CH_DES from TB_HSE_COMMON where TB_ID = 'PRIZE') c on a.DESCRIPTIONCODE = c.CODE_ID and a.suid=c.suid left outer join TB_STF_MASTER d on a.staffcode=d.staffcode and a.suid=d.suid left outer join (select f.suid, f.NAAOFFEREDID, g.ch_des from TB_NAA_NAAOFFERED f left outer join TB_HSE_COMMON g ON f.NAACode = g.Code_ID AND f.SUID = g.SUID AND ((g.Tb_ID = 'ECACD' and f.NAAType = 'E') OR (g.Tb_ID = 'SEVPST' and f.NAAType = 'S') OR (g.Tb_ID = 'INTERSCH' and f.NAAType = 'I')) ) h on a.suid=h.suid and a.NAAOFFEREDID = h.NAAOFFEREDID where a.schyear=? and a.anptype='a' order by b.classcode, b.classno 但當中的列印示標,只能看到成績表可讀取示標,想請教各位如何可再加一欄以顯示學生學習概覽可讀取示標的內容, 謝謝。
老師請參考以下SQL: select b.classcode as '班級', b.classno as '班號', b.chname as '中文姓名', dateformat(a.ANPDATE, 'DD/MM/YYYY') as '日期', (case when a.DESCRIPTIONSTR is null then h.ch_des when trim(a.DESCRIPTIONCODE)<>'' then c.ch_des else a.DESCRIPTIONSTR end) as '事件',a.lvl1 as '優點',d.name_chi as '負責人', (case when a.printind='1' then 'Y' else 'N' end) as '列印示標', (case when a.SLPREADIND='1' then 'Y' else 'N' end) as '學生學習概覽可讀取示標' from tb_STU_ANPSTUREC a join vw_STU_LATESTSTUDENT b on a.stuid=b.stuid and a.suid=b.suid and a.schyear=b.schyear left outer join (select suid, CODE_ID, CH_DES from TB_HSE_COMMON where TB_ID = 'PRIZE') c on a.DESCRIPTIONCODE = c.CODE_ID and a.suid=c.suid left outer join TB_STF_MASTER d on a.staffcode=d.staffcode and a.suid=d.suid left outer join (select f.suid, f.NAAOFFEREDID, g.ch_des from TB_NAA_NAAOFFERED f left outer join TB_HSE_COMMON g ON f.NAACode = g.Code_ID AND f.SUID = g.SUID AND ((g.Tb_ID = 'ECACD' and f.NAAType = 'E') OR (g.Tb_ID = 'SEVPST' and f.NAAType = 'S') OR (g.Tb_ID = 'INTERSCH' and f.NAAType = 'I')) ) h on a.suid=h.suid and a.NAAOFFEREDID = h.NAAOFFEREDID where a.schyear=? and a.anptype='a' order by b.classcode, b.classno
請老師參考以下SQL,由於獎項資料沒有分學期輸入系統,所以只能以日期提取: select b.classcode as '班級', b.classno as '班號', b.chname as '中文姓名', dateformat(a.ANPDATE, 'DD/MM/YYYY') as '日期', (case when a.DESCRIPTIONSTR is null then h.ch_des when trim(a.DESCRIPTIONCODE)<>'' then c.ch_des else a.DESCRIPTIONSTR end) as '事件',a.lvl1 as '優點' from tb_STU_ANPSTUREC a join vw_STU_LATESTSTUDENT b on a.stuid=b.stuid and a.suid=b.suid and a.schyear=b.schyear left outer join (select suid, CODE_ID, CH_DES from TB_HSE_COMMON where TB_ID = 'PRIZE') c on a.DESCRIPTIONCODE = c.CODE_ID and a.suid=c.suid left outer join TB_STF_MASTER d on a.staffcode=d.staffcode and a.suid=d.suid left outer join (select f.suid, f.NAAOFFEREDID, g.ch_des from TB_NAA_NAAOFFERED f left outer join TB_HSE_COMMON g ON f.NAACode = g.Code_ID AND f.SUID = g.SUID AND ((g.Tb_ID = 'ECACD' and f.NAAType = 'E') OR (g.Tb_ID = 'SEVPST' and f.NAAType = 'S') OR (g.Tb_ID = 'INTERSCH' and f.NAAType = 'I')) ) h on a.suid=h.suid and a.NAAOFFEREDID = h.NAAOFFEREDID where a.schyear=? and a.anptype='a' order by b.classcode, b.classno
如想查核某學生歷年的優點及校內頒發獎項資料(可以用 REG NO 或 STRN 查閱) 例 年度 班級 班號 中文姓名 時間 事件 優點 2014 6A 1 陳大文 2013 5A 1 陳大文 2012 4A 1 陳大文 2011 3A 1 陳大文 2010 2A 1 陳大文 2009 1A 1 陳大文
如想查核某學生歷年的優點及校內頒發獎項資料(可以用 REG NO 或 STRN 查閱) 例 年度 班級 班號 中文姓名 時間 事件 優點 2014 6A 1 陳大文 2013 5A 1 陳大文 2012 4A 1 陳大文 2011 3A 1 陳大文 2010 2A 1 陳大文 2009 1A 1 陳大文