SQL 請教以SQL 輸出學生資料

你好，請問可否協助在這個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

 

Thx a lot