SQL 抽取監護人資料

本文由 LQ1230 在 2019-09-06 發表於 "WebSAMS 討論區" 討論區

標籤:
  1. 10436144

    LQ1230
    Expand Collapse

    文章:
    2
    讚:
    0
    學生資料一般列有父及母資料,並可指定誰是聯絡人。
    如有第三個監護人,應如何抽取其資料?

    以下是用來製作學籍表的SQL,請問如何修改取第三個監護人資料?

    select
    a.schyear '學年',
    a.REGNO '註冊編號',
    a.STRN '學生編號STRN',
    a.classcode '班別',
    a.classno '班號',
    a.ENNAME '英名姓名',
    a.CHNAME '中文姓名',
    a.SEX '性別',
    dateformat(a.DOB, 'DD/MM/YYYY') as '出生日期',
    r.CH_DES '宗教',
    a.PLACEOFORIGIN '籍貫',
    j.CH_DES '中文出生地點',
    a.HOMETEL '家居電話',

    f.chname '父親姓名',
    f.phone '父親電話',
    f.EMERGENCYPHONE '父親緊急聯絡電話',

    m.chname '母親姓名',
    m.phone '母親電話',
    m.EMERGENCYPHONE '母親緊急聯絡電話',

    guard.CHNAME '緊急聯絡人姓名',
    guard.EMERGENCYPHONE '緊急聯絡人電話',
    guard1.ch_des '緊急聯絡人關係',


    (select list(string( ' ', SIBC.CLASSNAME), ', ')
    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
    ) '兄弟姊妹班別',

    (select list(string( ' ', 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
    ) '兄弟姊妹中文姓名',

    (select list(string( ' ', SIB.enNAME), ', ')
    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
    ) '兄弟姊妹英文姓名',

    (select list(string( ' ', SIB.SEX), ', ')
    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
    ) '兄弟姊妹性別',

    (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) '家居地址',

    ('Flat ' + case when trim(a.ENFLATNO)<>' ' then trim(a.ENFLATNO) else ' ' end) +
    (', Floor ' + case when trim(a.ENFLOORNO)<>' ' then trim(a.ENFLOORNO) else ' ' end)+
    (', Block ' + case when trim(a.ENBLKNO)<>' ' then 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) '英文家居地址'

    from VW_STU_LATESTSTUDENT a

    left outer join TB_SCH_SCHCLASS s
    on s.SUID=a.SUID and s.SCHYEAR=a.SCHYEAR and s.SCHLEVEL=a.SCHLVL and s.SCHSESSION=a.SCHSESS and s.CLASSLEVEL=a.CLASSLVL and s.CLASSCODE=a.CLASSCODE

    left outer join TB_STU_PARENT guard
    on guard.SUID = a.SUID and guard.STUID=a.STUID and guard.GUARDIANIND='1'

    left outer join TB_HSE_COMMON guard1
    on a.SUID=guard1.SUID and guard1.CODE_ID=guard.relation and guard1.TB_ID='RELATE'
    left outer join TB_STU_PARENT m on
    a.SUID = m.SUID and a.STUID = m.STUID and m.RELATION = '02'
    left outer join TB_STU_PARENT f
    on a.SUID = f.SUID and a.STUID = f.STUID and f.RELATION = '01'
    left outer join TB_STU_STUSCHREC t
    on a.SUID=t.SUID and a.STUID=t.STUID and t.SCHYEAR=(SELECT MIN(SCHYEAR)FROM TB_STU_STUSCHREC U WHERE t.SUID = U.SUID AND t.STUID = U.STUID)
    left outer join TB_HSE_COMMON n
    on a.SUID=n.SUID and n.CODE_ID=a.Nationality and n.TB_ID='NATION'
    left outer join TB_HSE_COMMON h
    on a.SUID=h.SUID and h.CODE_ID=a.SCHHOUSE and h.TB_ID='SCHHUS'
    left outer join


    tb_hse_common j on j.SUID=a.SUID and j.tb_id='BIRCTY' and j.code_id=a.pob
    left outer join wsadmin.TB_HSE_COMMON r
    on a.SUID=r.SUID and r.CODE_ID=a.RELIGION and r.TB_ID='RELIG'
    left outer join (select STUID, min(FIRSTATTDATE) 'FIRSTATTDATE' from TB_STU_STUSCHREC group by STUID) d
    on d.STUID = a.STUID
    left outer join TB_HSE_COMMON a1 on
    a1.SUID=a.SUID and a1.CODE_ID=a.DOCTYPE and a1.TB_ID='TVLDOC'
    where a.SCHYEAR=? and a.CLASSLVL=?

    order by a.SCHLVL, a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO
     
    #1 LQ1230, 2019-09-06
    Last edited: 2019-09-06
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    73
    讚:
    0
    #2 edb-catherinewschan, 2019-09-09