你好,請問可否協助在這個SQL再加上六項資料,包括(學生編號,父親姓名,父親電話,母親姓名,母親電話及緊急聯絡電話2),謝謝你們﹗ 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
你好 可以參考以下的SQL select b.classname '班別', z.CLASSLVL '級別', z.classno '學號', z.chname '中文姓名', z.enname '英文姓名', z.STRN '學生編號', 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, w.chname '父親姓名', w.phone '父親聯絡電話', w.emergencyphone '父親緊急聯絡電話', x.chname '母親姓名', x.phone '母親聯絡電話', x.emergencyphone '母親緊急聯絡電話' from wsadmin.vw_stu_lateststudent z left outer join wsadmin.tb_sch_schclass b on b.classcode=z.classcode and b.schyear=z.schyear and b.suid = z.suid left outer join wsadmin.tb_hse_common c on c.code_id=z.schhouse and c.tb_id='SCHHUS' and c.suid = z.suid left outer join wsadmin.tb_stu_parent d on d.stuid = z.stuid and d.guardianind='1' and d.suid = z.suid left outer join wsadmin.tb_hse_common e on e.code_id=z.areacode and e.tb_id='AREACD' and e.suid = z.suid left outer join wsadmin.tb_stu_parent w on z.suid=w.suid and z.stuid=w.stuid and w.relation='01' left outer join wsadmin.tb_stu_parent x on z.suid=x.suid and z.stuid=x.stuid and x.relation='02' where z.schyear=? order by z.classcode, z.classno