學生資料 抽取居住在特定大廈的學生名單

本文由 鄭中李Sir 在 2021-01-12 發表於 "WebSAMS 討論區" 討論區

標籤:
  1. 10115714

    鄭中李Sir
    Expand Collapse

    文章:
    2
    讚:
    0
    請問是否有抽取居住在特定大廈的學生名單的SQL。
     
    #1 鄭中李Sir, 2021-01-12
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    163
    讚:
    0
    你好,

    可以參考以下的POST
    https://forum.hkedcity.net/index.php?threads/學生資料-提取中文地址.180297/

    只要把SQL的WHERE 句子內,加上大廈名的要求,就可以了...
    請參考以下的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 = ? and a.chbuilding=?
    order by a.CLASSCODE, a.CLASSNO
     
    #2 edb-catherinewschan, 2021-01-13 , 5:22 下午