select b.classname '班別', a.CLASSLVL '級別', a.classno '學號', a.chname '中文姓名', a.enname '英文姓名', a.regno '學生註冊編號', a.hkid '身份證號碼', a.pob '出生地點', dateformat(a.dob, 'DD/MM/YYYY') as dob, a.sex '性別', a.MOBILENO '學生手提電話', a.email '學生電郵', d.chname as 監護人姓名 , d.OCCUPATION '監護人職業', a.hometel '住宅電話', d.EMERGENCYPHONE '緊急聯絡電話', (case when e.ch_des is not null then e.ch_des else '' end) + (case when trim(a.chdistrict)<>'' then trim(a.chdistrict) else '' end)+ (case when trim(a.chstreet)<>'' then trim(a.chstreet) else '' end)+ (case when trim(a.chvillageestate)<>'' then trim(a.chvillageestate) else '' end)+ (case when trim(a.chbuilding)<>'' then trim(a.chbuilding) else '' end)+ (case when trim(a.chblkno)<>'' then trim(a.chblkno)+'座' else '' end)+ (case when trim(a.chfloorno)<>'' then trim(a.chfloorno)+'層' else '' end)+ (case when trim(a.chflatno)<>'' then trim(a.chflatno)+'室' else '' end) as address from vw_stu_lateststudent a left outer join tb_sch_schclass b on b.classcode=a.classcode and b.schyear=a.schyear and b.suid = a.suid left outer join tb_hse_common c on c.code_id=a.schhouse and c.tb_id='SCHHUS' and c.suid = a.suid left outer join tb_stu_parent d on d.stuid = a.stuid and d.guardianind='1' and d.suid = a.suid left outer join tb_hse_common e on e.code_id=a.areacode and e.tb_id='AREACD' and e.suid = a.suid where a.schyear=? order by a.classcode, a.classno
老師,請參考以下, 只需要把table 的alias a. 改為 z. 就可以成功抽取資料。 select b.classname '班別', z.CLASSLVL '級別', z.classno '學號', z.chname '中文姓名', z.enname '英文姓名', z.regno '學生註冊編號', z.hkid '身份證號碼', z.pob '出生地點', dateformat(z.dob, 'DD/MM/YYYY') as dob, z.sex '性別', z.MOBILENO '學生手提電話', z.email '學生電郵', d.chname as 監護人姓名 , d.OCCUPATION '監護人職業', z.hometel '住宅電話', d.EMERGENCYPHONE '緊急聯絡電話', (case when e.ch_des is not null then e.ch_des else '' end) + (case when trim(z.chdistrict)<>'' then trim(z.chdistrict) else '' end)+ (case when trim(z.chstreet)<>'' then trim(z.chstreet) else '' end)+ (case when trim(z.chvillageestate)<>'' then trim(z.chvillageestate) else '' end)+ (case when trim(z.chbuilding)<>'' then trim(z.chbuilding) else '' end)+ (case when trim(z.chblkno)<>'' then trim(z.chblkno)+'座' else '' end)+ (case when trim(z.chfloorno)<>'' then trim(z.chfloorno)+'層' else '' end)+ (case when trim(z.chflatno)<>'' then trim(z.chflatno)+'室' else '' end) as address from vw_stu_lateststudent z left outer join tb_sch_schclass b on b.classcode=z.classcode and b.schyear=z.schyear and b.suid = z.suid left outer join tb_hse_common c on c.code_id=z.schhouse and c.tb_id='SCHHUS' and c.suid = z.suid left outer join tb_stu_parent d on d.stuid = z.stuid and d.guardianind='1' and d.suid = z.suid left outer join tb_hse_common e on e.code_id=z.areacode and e.tb_id='AREACD' and e.suid = z.suid where z.schyear=? order by z.classcode, z.classno