以下是SQL，不好意思比較亂，先謝！！ select a.classcode '班別', a.classno '班號', a.chname '中文姓名', a.enname '英文姓名', a.sex '性別', a.strn '學生編號', dateformat(a.dob, 'DD/MM/YYYY') '出生日期', a.schfrom '過往學校資料', a.hometel '住宅電話', (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) '中文地址', (case when trim(a.enflatno)<>'' then 'Room ' + trim(a.enflatno) + ', ' else '' end) + (case when trim(a.enfloorno)<>'' then trim(a.enfloorno) + '/F, ' else '' end) + (case when trim(a.enblkno)<>'' then 'Block ' + trim(a.enblkno) + ', ' else '' end) + (case when trim(a.enbuilding)<>'' then trim(a.enbuilding) + ', ' else '' end) + (case when trim(a.envillageestate)<>'' then trim(a.envillageestate) + ', ' else '' end) + (case when trim(a.enstreet)<>'' then trim(a.enstreet) + ', ' else '' end) + (case when trim(a.endistrict)<>'' then trim(a.endistrict) + '.' else '' end) '英文地址', b.chname '父親姓名', b.occupation '父親職業', b.phone '父親聯絡電話', b.emergencyphone '父親緊急聯絡電話', c.chname '母親姓名', c.occupation '母親職業', c.phone '母親聯絡電話', c.emergencyphone '母親緊急聯絡電話', d.chname '監護人姓名', e.ch_des '監護人關係', d.occupation '監護人職業', d.phone '監護人聯絡電話', d.emergencyphone '監護人緊急聯絡電話', (select list(string(SIBC.CLASSNAME, ' ', SIB.CHNAME), ', ') from VW_STU_LATESTSTUDENT SIB left outer join TB_SCH_SCHCLASS SIBC on SIBC.SUID=SIB.SUID and SIBC.SCHYEAR=SIB.SCHYEAR and SIBC.SCHLEVEL=SIB.SCHLVL and SIBC.SCHSESSION=SIB.SCHSESS and SIBC.CLASSLEVEL=SIB.CLASSLVL and SIBC.CLASSCODE=SIB.CLASSCODE where SIB.SUID=a.SUID and SIB.SCHYEAR=a.SCHYEAR and SIB.SIBGRP<>-1 and SIB.SIBGRP=a.SIBGRP and SIB.STUID<>a.STUID) '兄弟姊妹', g.userdefinedfield1 '用戶設定欄位1', h.userdefinedfield2 '用戶設定欄位2', f.illnesstype '疾病類別', f.condition '情況' from VW_STU_LATESTSTUDENT a left outer join tb_stu_parent b on a.suid=b.suid and a.stuid=b.stuid and b.relation='01' left outer join tb_stu_parent c on a.suid=c.suid and a.stuid=c.stuid and c.relation='02' left outer join tb_stu_parent d on a.suid=d.suid and a.stuid=d.stuid and d.guardianind='1' left outer join tb_hse_common e on a.suid=e.suid and d.relation=e.code_id and e.tb_id='RELATE' left outer join tb_stu_health f on a.suid=f.suid and a.stuid=f.stuid left outer join (select suid, stuid, trim(stuuserfield) 'userdefinedfield1' from tb_stu_stuuserfield where stuuserfieldid=0) g on a.suid=g.suid and a.stuid=g.stuid left outer join (select suid, stuid, trim(stuuserfield) 'userdefinedfield2' from tb_stu_stuuserfield where stuuserfieldid=1) h on a.suid=h.suid and a.stuid=h.stuid where a.schyear=? order by classcode,a.classno
伙好`, 如果想剔除所有離校生，可以在WHERE句子中，加入 NOT IN 離校學生資料(TB_STU_DEPARTURE)。 你參考以下紅色highlight的句子就可以了。 select a.classcode '班別', a.classno '班號', a.chname '中文姓名', a.enname '英文姓名', a.sex '性別', a.strn '學生編號', dateformat(a.dob, 'DD/MM/YYYY') '出生日期', a.schfrom '過往學校資料', a.hometel '住宅電話', (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) '中文地址', (case when trim(a.enflatno)<>'' then 'Room ' + trim(a.enflatno) + ', ' else '' end) + (case when trim(a.enfloorno)<>'' then trim(a.enfloorno) + '/F, ' else '' end) + (case when trim(a.enblkno)<>'' then 'Block ' + trim(a.enblkno) + ', ' else '' end) + (case when trim(a.enbuilding)<>'' then trim(a.enbuilding) + ', ' else '' end) + (case when trim(a.envillageestate)<>'' then trim(a.envillageestate) + ', ' else '' end) + (case when trim(a.enstreet)<>'' then trim(a.enstreet) + ', ' else '' end) + (case when trim(a.endistrict)<>'' then trim(a.endistrict) + '.' else '' end) '英文地址', b.chname '父親姓名', b.occupation '父親職業', b.phone '父親聯絡電話', b.emergencyphone '父親緊急聯絡電話', c.chname '母親姓名', c.occupation '母親職業', c.phone '母親聯絡電話', c.emergencyphone '母親緊急聯絡電話', d.chname '監護人姓名', e.ch_des '監護人關係', d.occupation '監護人職業', d.phone '監護人聯絡電話', d.emergencyphone '監護人緊急聯絡電話', (select list(string(SIBC.CLASSNAME, ' ', SIB.CHNAME), ', ') from wsadmin.VW_STU_LATESTSTUDENT SIB left outer join wsadmin.TB_SCH_SCHCLASS SIBC on SIBC.SUID=SIB.SUID and SIBC.SCHYEAR=SIB.SCHYEAR and SIBC.SCHLEVEL=SIB.SCHLVL and SIBC.SCHSESSION=SIB.SCHSESS and SIBC.CLASSLEVEL=SIB.CLASSLVL and SIBC.CLASSCODE=SIB.CLASSCODE where SIB.SUID=a.SUID and SIB.SCHYEAR=a.SCHYEAR and SIB.SIBGRP<>-1 and SIB.SIBGRP=a.SIBGRP and SIB.STUID<>a.STUID) '兄弟姊妹', g.userdefinedfield1 '用戶設定欄位1', h.userdefinedfield2 '用戶設定欄位2', f.illnesstype '疾病類別', f.condition '情況' from wsadmin.VW_STU_LATESTSTUDENT a left outer join wsadmin.tb_stu_parent b on a.suid=b.suid and a.stuid=b.stuid and b.relation='01' left outer join wsadmin.tb_stu_parent c on a.suid=c.suid and a.stuid=c.stuid and c.relation='02' left outer join wsadmin.tb_stu_parent d on a.suid=d.suid and a.stuid=d.stuid and d.guardianind='1' left outer join wsadmin.tb_hse_common e on a.suid=e.suid and d.relation=e.code_id and e.tb_id='RELATE' left outer join wsadmin.tb_stu_health f on a.suid=f.suid and a.stuid=f.stuid left outer join (select suid, stuid, trim(stuuserfield) 'userdefinedfield1' from wsadmin.tb_stu_stuuserfield where stuuserfieldid=0) g on a.suid=g.suid and a.stuid=g.stuid left outer join (select suid, stuid, trim(stuuserfield) 'userdefinedfield2' from wsadmin.tb_stu_stuuserfield where stuuserfieldid=1) h on a.suid=h.suid and a.stuid=h.stuid where a.schyear=? and a.stuid not in (select stuid from wsadmin.TB_STU_DEPARTURE) order by classcode,a.classno