SQL 學生資料(提取中文地址)

本文由 chautc 在 2020-10-23 發表於 "WebSAMS 討論區" 討論區

  1. 10004315

    chautc
    Expand Collapse

    文章:
    60
    讚:
    0
    下列 SQL 未能提取學生的中文地址?

    select
    a.SCHFROM '以前就讀學校',a.LASTCLASSLVL '以前就讀班別', a.Schyear '年度', a.CLASSCODE '班別', a.CLASSNO '班號',
    a.CHNAME as '學生姓名', a.ENNAME as '英文姓名', dateformat(a.admdate,'DD/MM/YYYY') as '入校日期',
    d.CH_DES '社', a.REGNO as'註冊號數', a.STRN, a.HKID '身份證號碼', dateformat(a.DOB, 'DD/MM/YYYY') as '出生日期',
    (datediff(day, a.dob, now())/365) '年齡', b.CH_DES'宗教',
    p1.CHNAME as '父親姓名', p2.CHNAME as '母親姓名',p1.chname as '監護人姓名',
    (case when trim(a.enflatno)<>'' then
    'RM '+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)+ (case when a.areacode=1 then 'HONG KONG' ELSE (case when a.areacode=2 then 'KOWLOON' else 'NEW TERRITORIES' end) end)+'.'
    as '住址', a.HOMETEL '電話',
    p1.EMERGENCYPHONE as'父親緊急聯絡電話',
    p2.EMERGENCYPHONE as '母親緊急聯絡電話',
    a.CCC as '中文姓名電碼',
    h.CH_DES'出生地點',
    g.CH_DES'區議會分區',
    (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) '兄弟姊妹',
    a.mobileno '學生手提電話'
    from VW_STU_LATESTSTUDENT a
    left outer join TB_HSE_COMMON b
    on b.CODE_ID = a.RELIGION and b.TB_ID = 'RELIG' and b.SUID = a.SUID
    left outer join TB_HSE_COMMON d
    on a.SUID=d.SUID and d.CODE_ID=a.SCHHOUSE and d.TB_ID='SCHHUS'
    left outer join TB_HSE_COMMON h
    on h.CODE_ID=a.POB and h.TB_ID='BIRCTY' and h.SUID=a.SUID
    left outer join TB_HSE_COMMON j
    on j.CODE_ID=a.NATIONALITY and j.TB_ID='NATION' and j.SUID=a.SUID
    left outer join TB_HSE_COMMON g
    on g.CODE_ID=a. DISTRICTCOUNCIL and g.TB_ID='HOMEDB' and g.SUID=a.SUID
    left outer join TB_STU_PARENT p1
    on p1.STUID = a.STUID and p1.SUID = a.SUID and P1.RELATION='01'
    left outer join TB_STU_PARENT p2
    on p2.STUID = a.STUID and p2.SUID = a.SUID and P2.RELATION='02'
    where a.SCHYEAR = ?
    order by a.CLASSCODE, a.CLASSNO
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    156
    讚:
    0
    你好,

    地址有分中文和英文, 英文只是EN字頭, 而中文是CH字頭

    可以參考以下的SQL

    select
    a.SCHFROM '以前就讀學校',a.LASTCLASSLVL '以前就讀班別', a.Schyear '年度', a.CLASSCODE '班別', a.CLASSNO '班號',
    a.CHNAME as '學生姓名', a.ENNAME as '英文姓名', dateformat(a.admdate,'DD/MM/YYYY') as '入校日期',
    d.CH_DES '社', a.REGNO as'註冊號數', a.STRN, a.HKID '身份證號碼', dateformat(a.DOB, 'DD/MM/YYYY') as '出生日期',
    (datediff(day, a.dob, now())/365) '年齡', b.CH_DES'宗教',
    p1.CHNAME as '父親姓名', p2.CHNAME as '母親姓名',p1.chname as '監護人姓名',
    (case when trim(a.enflatno)<>'' then
    'RM '+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)+ (case when a.areacode=1 then 'HONG KONG' ELSE (case when a.areacode=2 then 'KOWLOON' else 'NEW TERRITORIES' end) end)+'.'
    as '英文住址',
    (case when trim(a.chflatno)<>'' then
    trim(a.enflatno)+' 室'+', ' else '' end) +
    (case when trim(a.chfloorno)<>'' then
    trim(a.enfloorno)+ ' 樓, 'else '' end)+
    (case when trim(a.chblkno)<>'' then
    trim(a.enblkno)+' 座'+', ' else '' end)+
    (case when trim(a.chbuilding)<>''
    then trim(a.enbuilding)+', ' else '' end) +
    (case when trim(a.chvillageestate)<>'' then
    trim(a.envillageestate)+', ' else '' end) +
    (case when trim(a.chstreet)<>'' then
    trim(a.enstreet)+', ' else '' end) +
    (case when trim(a.chdistrict)<>'' then
    (trim(a.chdistrict)+', ') else '' end)+ (case when a.areacode=1 then '香港' ELSE (case when a.areacode=2 then '九龍' else '新界' end) end)+'.'
    as '中文住址',

    a.HOMETEL '電話',
    p1.EMERGENCYPHONE as'父親緊急聯絡電話',
    p2.EMERGENCYPHONE as '母親緊急聯絡電話',
    a.CCC as '中文姓名電碼',
    h.CH_DES'出生地點',
    g.CH_DES'區議會分區',
    (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) '兄弟姊妹',
    a.mobileno '學生手提電話'
    from wsadmin.VW_STU_LATESTSTUDENT a
    left outer join wsadmin.TB_HSE_COMMON b
    on b.CODE_ID = a.RELIGION and b.TB_ID = 'RELIG' and b.SUID = a.SUID
    left outer join wsadmin.TB_HSE_COMMON d
    on a.SUID=d.SUID and d.CODE_ID=a.SCHHOUSE and d.TB_ID='SCHHUS'
    left outer join wsadmin.TB_HSE_COMMON h
    on h.CODE_ID=a.POB and h.TB_ID='BIRCTY' and h.SUID=a.SUID
    left outer join wsadmin.TB_HSE_COMMON j
    on j.CODE_ID=a.NATIONALITY and j.TB_ID='NATION' and j.SUID=a.SUID
    left outer join wsadmin.TB_HSE_COMMON g
    on g.CODE_ID=a. DISTRICTCOUNCIL and g.TB_ID='HOMEDB' and g.SUID=a.SUID
    left outer join wsadmin.TB_STU_PARENT p1
    on p1.STUID = a.STUID and p1.SUID = a.SUID and P1.RELATION='01'
    left outer join wsadmin.TB_STU_PARENT p2
    on p2.STUID = a.STUID and p2.SUID = a.SUID and P2.RELATION='02'
    where a.SCHYEAR = ?
    order by a.CLASSCODE, a.CLASSNO
     
    #2 edb-catherinewschan, 2020-10-28