學生資料 加入身份証明文件類型及相關資料

本文由 tr-wong_sir 在 2019-09-04 發表於 "WebSAMS 討論區" 討論區

  1. 55019624

    tr-wong_sir
    Expand Collapse

    文章:
    55
    讚:
    0
    希望可在下列sql
    加入

    1) '
    出生證明書',
    2) '
    香港身份證',
    3) '
    身份證明文件',
    4)'
    身份證明文件號碼',
    5)'
    來港日期[FONT=Helvetica, sans-serif]',[/FONT]

    [FONT=Helvetica, sans-serif]原先sql 如下:[/FONT]

    [FONT=Helvetica, sans-serif]select
    s.CLASSNAME '班別',
    a.CLASSNO '學號',
    a.strn,
    a.regno,
    a.HKID,

    a.ENNAME '英名姓名',
    a.CHNAME '中文姓名',
    a.SEX '性別',

    dateformat(a.DOB, 'DD/MM/YYYY') as '出生日期',
    a.STRN '學生編號',
    a.HOMETEL '家居電話',
    guard.CHNAME as '監護人',
    guard.EMERGENCYPHONE '緊急電話',

    f.chname '父親姓名',
    f.phone '父親電話',

    m.chname '母親姓名',
    m.phone '母親電話',

    (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) '家居地址',
    a.schfrom '來自幼稚園',
    (select list(string(SIB.CHNAME, ' ', SIB.CLASSCODE), ', ')
    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
    ) '兄弟姊妹'
    from wsadmin.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_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 wsadmin.TB_STU_STUSCHREC t on
    a.SUID=t.SUID and a.STUID=t.STUID and a.FIRSTATTDATE = t.FIRSTATTDATE and t.SCHYEAR=(SELECT MIN(SCHYEAR)FROM wsadmin.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
    (select STUID, min(FIRSTATTDATE) 'FIRSTATTDATE' from wsadmin.TB_STU_STUSCHREC group by STUID) d
    on d.STUID = a.STUID
    where a.SCHYEAR=?
    order by a.SUID, a.SCHLVL, a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO[/FONT]
     
    #1 tr-wong_sir, 2019-09-04
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    73
    讚:
    0
    老師 , 你好,

    已加入要求的資料, 請參考以下的SQL:-


    select
    s.CLASSNAME '班別',
    a.CLASSNO '學號',
    a.strn,
    a.regno,
    a.HKID '香港身份證',
    a.birthcert '出生證明書',
    dtyp.ch_des '身份證明文件類型',
    a.docno '身份證明文件號碼',
    dateformat(a.DATEFROMMAINLAND,'yyyy-mm-dd') '來港日期',


    a.ENNAME '英名姓名',
    a.CHNAME '中文姓名',
    a.SEX '性別',

    dateformat(a.DOB, 'DD/MM/YYYY') as '出生日期',
    a.STRN '學生編號',
    a.HOMETEL '家居電話',
    guard.CHNAME as '監護人',
    guard.EMERGENCYPHONE '緊急電話',

    f.chname '父親姓名',
    f.phone '父親電話',

    m.chname '母親姓名',
    m.phone '母親電話',

    (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) '家居地址',
    a.schfrom '來自幼稚園',
    (select list(string(SIB.CHNAME, ' ', SIB.CLASSCODE), ', ')
    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
    ) '兄弟姊妹'
    from wsadmin.VW_STU_LATESTSTUDENT a
    left outer join wsadmin.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 wsadmin.TB_STU_PARENT guard
    on guard.SUID = a.SUID and guard.STUID=a.STUID and
    guard.GUARDIANIND='1'
    left outer join wsadmin.TB_STU_PARENT m on
    a.SUID = m.SUID and a.STUID = m.STUID and m.RELATION = '02'
    left outer join wsadmin.TB_STU_PARENT f on
    a.SUID = f.SUID and a.STUID = f.STUID and f.RELATION = '01'
    left outer join wsadmin.TB_STU_STUSCHREC t on
    a.SUID=t.SUID and a.STUID=t.STUID and a.FIRSTATTDATE = t.FIRSTATTDATE and t.SCHYEAR=(SELECT MIN(SCHYEAR) FROM wsadmin.TB_STU_STUSCHREC U WHERE t.SUID = U.SUID AND t.STUID = U.STUID)
    left outer join wsadmin.TB_HSE_COMMON n on
    a.SUID=n.SUID and n.CODE_ID=a.Nationality and n.TB_ID='NATION'
    left outer join wsadmin.TB_HSE_COMMON h on
    a.SUID=h.SUID and h.CODE_ID=a.SCHHOUSE and
    h.TB_ID='SCHHUS'
    left outer join wsadmin.TB_HSE_COMMON dtyp on
    a.SUID=dtyp.SUID and dtyp.CODE_ID=a.doctype and dtyp.TB_ID='TVLDOC'
    left outer join
    (select STUID, min(FIRSTATTDATE) 'FIRSTATTDATE' from wsadmin.TB_STU_STUSCHREC group by STUID) d
    on d.STUID = a.STUID
    where a.SCHYEAR=?
    order by a.SUID, a.SCHLVL, a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO
     
    #2 edb-catherinewschan, 2019-09-05