SQL 請教以SQL 輸出學生資料

本文由 鄭耀文 在 2021-09-20 發表於 "WebSAMS 討論區" 討論區

  1. 58969163

    鄭耀文
    Expand Collapse

    文章:
    3
    讚:
    0
    你好,請問可否協助在這個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
     
    #1 鄭耀文, 2021-09-20
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    213
    讚:
    0
    你好

    可以參考以下的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
     
    #2 edb-catherinewschan, 2021-09-23