SQL 很好用抽學生資料SQL

本文由 0004 在 2021-02-08 發表於 "WebSAMS 討論區" 討論區

  1. 55094568

    0004
    Expand Collapse

    文章:
    37
    讚:
    0
    你好

    是否 WEBSAMS UPDATE ,好多時既唔同SQL 開始有問題

    1. 最近抽資料時有幾欄抽出黎資料同學生資料不對應 ~~~~~> 用紅色 顯示 (以往抽取是沒有問題)
    2.想在最後加入以下6欄未知能否因字數限制加入

    大前年班別 大前年學號 前年班別 前年學號 去年班別 去年學號

    3 .因字數限制 有否其他更簡單的SQL 也能抽取我這條 SQL「所有」學生資料

    十分謝謝~~


    select

    a.SCHLVL 'SCHLV',
    a.SCHSESS 'SESSION',
    a.CLASSLVL 'CLASSLVL',

    a.schyear 'SCHYR',
    a.REGNO 'REGNO',
    a.classcode 'CLASS',
    a.classno 'NO',

    a.SCHHOUSE 'SCH',
    a9.CH_DES '學社',

    a.ENNAME 'ENNAME',
    a.CHNAME '中文姓名',

    a.SEX 'SEX',

    SUBSTR(a.ENNAME,1,LOCATE(a.ENNAME,' ')) '英文姓',
    SUBSTR(a.ENNAME,LOCATE(a.ENNAME,' ')+1) '英文名',
    SUBSTR(a.CHNAME,1,1) '中文姓',
    SUBSTR(a.CHNAME,2) '中文名',


    a.HKID 'HKID',
    a.STRN 'STRN',

    dateformat(a.DOB, 'DD/MM/YYYY') as 'DOB',
    (datediff(day, a.dob, now())/365) 'AGE',
    a7.CH_DES '出生地方',
    a.POB 'POB',
    dateformat(a.DATEFROMMAINLAND, 'DD/MM/YYYY') '內地來港日期',
    (case when a.NONCHSPEAKING=0 then 'N'
    else 'Y' end) '非華語學生示標',

    a1.ch_des '身份證明文件類型',
    a.DOCNO '身份證明文件號碼',
    a.PLACEOFORIGIN '籍貫',
    a.BIRTHCERT '出生證明書',
    a4.ch_des '國籍',
    a.Nationality 'Nationality',

    a2.CH_DES '種族',
    a.ETHNICITY 'ETHNICITY',

    a3.CH_DES '家中使用語言',
    a.HOMELANGUAGE 'HOMELANG',

    a10.CH_DES'宗教',
    a.RELIGION 'RELIGION',


    dateformat(t.ADMDATE, 'DD/MM/YYYY') '取錄日期',
    t3.CH_DES '取錄類別',

    t4.CH_DES '其他',

    dateformat(d.FIRSTATTDATE, 'DD/MM/YYYY') as '入學日期',
    t.classcode '入學級別',

    t2.ch_des '在校狀態',
    dateformat(x.LASTATTEND, 'DD/MM/YYYY') '最後出席日期',
    x1.CH_DES '離校原因',



    a.HOMETEL 'HOMETEL',
    a.MOBILENO '學生電話',
    a.email 'EMAIL',

    a.SCHFROM '以前就讀學校',
    a.LASTCLASSLVL '以前就讀級別',

    guard.CHNAME '監護人姓名',
    guard.enNAME '監護人英文姓名',
    guard.HKID '監護人香港身份證號碼',
    guard.PHONE '監護人聯絡電話',
    guard.EMERGENCYPHONE '監護人緊急電話',
    guard.occupation '監護人職業',
    guard1.ch_des '監護人關係',

    f.chname '父親姓名',
    f.enname '父親英文姓名',
    f.HKID '父親香港身份證號碼',
    f.PHONE '父親聯絡電話',
    f.EMERGENCYPHONE '父親緊急聯絡電話',
    f.OCCUPATION '父親職業',

    m.chname '母親姓名',
    m.enname '母親英文姓名',
    m.HKID '母親香港身份證號碼',
    m.PHONE '母親聯絡電話',
    m.EMERGENCYPHONE '母親緊急聯絡電話',
    m.OCCUPATION '母親職業',


    (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) '兄弟姊妹',

    a8.CH_DES '區議會分區',
    a.DISTRICTCOUNCIL'HOMEDB',

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

    (case when trim(a.enflatno)<>'' then 'Room ' + 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) '英文地址'



    from VW_STU_LATESTSTUDENT a
    left outer join TB_HSE_COMMON a2
    on a.SUID=a2.SUID and a2.CODE_ID=a.ETHNICITY and a2.TB_ID='ETHNICITY'


    left outer join TB_HSE_COMMON a3
    on a.SUID=a3.SUID and a3.CODE_ID=a.HOMELANGUAGE and a3.TB_ID='HOMELANG'

    left outer join TB_HSE_COMMON a4
    on a.SUID=a4.SUID and a4.CODE_ID=a.Nationality and a4.TB_ID='NATION'


    left outer join TB_HSE_COMMON a7
    on a7.CODE_ID=a.POB and a7.TB_ID='BIRCTY' and a7.SUID=a.SUID

    left outer join TB_HSE_COMMON a8
    on a8.CODE_ID=a. DISTRICTCOUNCIL and a8.TB_ID='HOMEDB' and a8.SUID=a.SUID

    left outer join TB_HSE_COMMON a9
    on a.SUID = a9.SUID and a9.CODE_ID = a.SCHHOUSE and a9.TB_ID = 'SCHHUS'

    left outer join TB_HSE_COMMON a10
    on a10.CODE_ID = a.RELIGION and a10.TB_ID = 'RELIG' and a10.SUID = a.SUID


    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 t3
    on a.SUID=t3.SUID and t3.CODE_ID=t.ADMTYPE and t3.TB_ID='ADMTP'

    left outer join TB_HSE_COMMON t4
    on a.SUID=t4.SUID and t4.CODE_ID=t.ADMTYPE and t4.TB_ID='FMTYP'



    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 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'

    left outer join TB_STU_DEPARTURE x
    on a.SUID=x.SUID and a.STUID=x.STUID

    left outer join TB_HSE_COMMON x1
    on a.SUID=x1.SUID and x1.CODE_ID=x.DEPTERM and x1.TB_ID='LEVRES'

    left outer join TB_STU_STUSCHREC t1
    on a.SUID=t1.SUID and a.STUID=t1.STUID and t1.SCHYEAR=(SELECT MAX(SCHYEAR)FROM TB_STU_STUSCHREC v WHERE t1.SUID=v.SUID AND t1.STUID=v.STUID)
    left outer join TB_HSE_COMMON t2
    on a.SUID=t2.SUID and t2.CODE_ID=t1.STATUS and t2.TB_ID='STUDST'



    where a.SCHYEAR=?
    order by a.CLASSCODE, a.CLASSNO, a.SCHLVL, a.SCHSESS, a.REGNO, a.CLASSLVL
     
    #1 0004, 2021-02-08
    Last edited: 2021-02-08
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    178
    讚:
    0
    你好,

    1, 離校原因及最後出席日期的資料, 可以參考以下SQL
    select
    a.schyear,
    a.classlvl,
    a.classcode,
    a.classno,
    a.chname,
    a.sex,
    dateformat(b.LASTATTEND,'DD/MM/YYYY')as
    'Last Attendence Date',
    c.ch_des as 'Reason'
    from VW_STU_LATESTSTUDENT a
    join TB_STU_DEPARTURE b on a.suid=b.suid and a.stuid=b.stuid
    left outer join TB_HSE_COMMON c on a.suid=c.suid and c.code_id=b.DEPREASON and c.TB_ID='LEVRES'
    where a.schyear=?
    order by a.classlvl,a.classcode,a.classno

    2, 另外抽取

    "大前年班別 大前年學號 前年班別 前年學號 去年班別 去年學號"

    可以用
    left outer join TB_STU_STUSCHREC , 把schyear-1 , schyear-2 和 schyear-3 去取的上年、前年、大前年的學號和班別資料。

    3, 如果字數太長, websams 是不支援的。
     
    #2 edb-catherinewschan, 2021-02-10
  3. 55094568

    0004
    Expand Collapse

    文章:
    37
    讚:
    0

    你好能否將錯誤的SQL 幫忙更改為我上面的SQL
    不想開一新一條SQL 謝謝

    1是想更改 及2是想加在上面SQL 可以做到嗎.?????
    因不知道如何將你的SQL 加入我那條SQL
     
  4. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    178
    讚:
    0
    試試以下的SQL

    select

    a.SCHLVL 'SCHLV',
    a.SCHSESS 'SESSION',
    a.CLASSLVL 'CLASSLVL',

    a.schyear 'SCHYR',
    a.REGNO 'REGNO',
    a.classcode 'CLASS',
    a.classno 'NO',

    a.SCHHOUSE 'SCH',
    a9.CH_DES '學社',

    a.ENNAME 'ENNAME',
    a.CHNAME '中文姓名',

    a.SEX 'SEX',

    SUBSTR(a.ENNAME,1,LOCATE(a.ENNAME,' ')) '英文姓',
    SUBSTR(a.ENNAME,LOCATE(a.ENNAME,' ')+1) '英文名',
    SUBSTR(a.CHNAME,1,1) '中文姓',
    SUBSTR(a.CHNAME,2) '中文名',


    a.HKID 'HKID',
    a.STRN 'STRN',

    dateformat(a.DOB, 'DD/MM/YYYY') as 'DOB',
    (datediff(day, a.dob, now())/365) 'AGE',
    a7.CH_DES '出生地方',
    a.POB 'POB',
    dateformat(a.DATEFROMMAINLAND, 'DD/MM/YYYY') '內地來港日期',
    (case when a.NONCHSPEAKING=0 then 'N'
    else 'Y' end) '非華語學生示標',

    a1.ch_des '身份證明文件類型',
    a.DOCNO '身份證明文件號碼',
    a.PLACEOFORIGIN '籍貫',
    a.BIRTHCERT '出生證明書',
    a4.ch_des '國籍',
    a.Nationality 'Nationality',

    a2.CH_DES '種族',
    a.ETHNICITY 'ETHNICITY',

    a3.CH_DES '家中使用語言',
    a.HOMELANGUAGE 'HOMELANG',

    a10.CH_DES'宗教',
    a.RELIGION 'RELIGION',


    dateformat(t.ADMDATE, 'DD/MM/YYYY') '取錄日期',
    t3.CH_DES '取錄類別',

    t4.CH_DES '其他',

    dateformat(d.FIRSTATTDATE, 'DD/MM/YYYY') as '入學日期',
    t.classcode '入學級別',

    t2.ch_des '在校狀態',
    dateformat(x.LASTATTEND, 'DD/MM/YYYY') '最後出席日期',
    x1.CH_DES '離校原因',

    pre1.classcode '去年班別',
    pre1.classno '去年學號',
    pre2.classcode '前年班別',
    pre2.classno '前年學號',
    pre3.classcode '大前年班別',
    pre3.classno '大前年學號',

    a.HOMETEL 'HOMETEL',
    a.MOBILENO '學生電話',
    a.email 'EMAIL',

    a.SCHFROM '以前就讀學校',
    a.LASTCLASSLVL '以前就讀級別',

    guard.CHNAME '監護人姓名',
    guard.enNAME '監護人英文姓名',
    guard.HKID '監護人香港身份證號碼',
    guard.PHONE '監護人聯絡電話',
    guard.EMERGENCYPHONE '監護人緊急電話',
    guard.occupation '監護人職業',
    guard1.ch_des '監護人關係',

    f.chname '父親姓名',
    f.enname '父親英文姓名',
    f.HKID '父親香港身份證號碼',
    f.PHONE '父親聯絡電話',
    f.EMERGENCYPHONE '父親緊急聯絡電話',
    f.OCCUPATION '父親職業',

    m.chname '母親姓名',
    m.enname '母親英文姓名',
    m.HKID '母親香港身份證號碼',
    m.PHONE '母親聯絡電話',
    m.EMERGENCYPHONE '母親緊急聯絡電話',
    m.OCCUPATION '母親職業',


    (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) '兄弟姊妹',

    a8.CH_DES '區議會分區',
    a.DISTRICTCOUNCIL'HOMEDB',

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

    (case when trim(a.enflatno)<>'' then 'Room ' + 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) '英文地址'



    from VW_STU_LATESTSTUDENT a
    left outer join TB_HSE_COMMON a2
    on a.SUID=a2.SUID and a2.CODE_ID=a.ETHNICITY and a2.TB_ID='ETHNICITY'


    left outer join TB_HSE_COMMON a3
    on a.SUID=a3.SUID and a3.CODE_ID=a.HOMELANGUAGE and a3.TB_ID='HOMELANG'

    left outer join TB_HSE_COMMON a4
    on a.SUID=a4.SUID and a4.CODE_ID=a.Nationality and a4.TB_ID='NATION'


    left outer join TB_HSE_COMMON a7
    on a7.CODE_ID=a.POB and a7.TB_ID='BIRCTY' and a7.SUID=a.SUID

    left outer join TB_HSE_COMMON a8
    on a8.CODE_ID=a. DISTRICTCOUNCIL and a8.TB_ID='HOMEDB' and a8.SUID=a.SUID

    left outer join TB_HSE_COMMON a9
    on a.SUID = a9.SUID and a9.CODE_ID = a.SCHHOUSE and a9.TB_ID = 'SCHHUS'

    left outer join TB_HSE_COMMON a10
    on a10.CODE_ID = a.RELIGION and a10.TB_ID = 'RELIG' and a10.SUID = a.SUID


    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 t3
    on a.SUID=t3.SUID and t3.CODE_ID=t.ADMTYPE and t3.TB_ID='ADMTP'

    left outer join TB_HSE_COMMON t4
    on a.SUID=t4.SUID and t4.CODE_ID=t.ADMTYPE and t4.TB_ID='FMTYP'



    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 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'

    left outer join TB_STU_DEPARTURE x
    on a.SUID=x.SUID and a.STUID=x.STUID

    left outer join TB_HSE_COMMON x1
    on a.SUID=x1.SUID and x1.CODE_ID=x.DEPREASON and x1.TB_ID='LEVRES'

    left outer join TB_STU_STUSCHREC pre1
    on a.suid=pre1.suid and a.stuid=pre1.stuid and pre1.schyear=a.schyear-1

    left outer join TB_STU_STUSCHREC pre2
    on a.suid=pre2.suid and a.stuid=pre2.stuid and pre2.schyear=a.schyear-2

    left outer join TB_STU_STUSCHREC pre3
    on a.suid=pre3.suid and a.stuid=pre3.stuid and pre3.schyear=a.schyear-3

    left outer join TB_STU_STUSCHREC t1
    on a.SUID=t1.SUID and a.STUID=t1.STUID and t1.SCHYEAR=(SELECT MAX(SCHYEAR)FROM TB_STU_STUSCHREC v WHERE t1.SUID=v.SUID AND t1.STUID=v.STUID)
    left outer join TB_HSE_COMMON t2
    on a.SUID=t2.SUID and t2.CODE_ID=t1.STATUS and t2.TB_ID='STUDST'



    where a.SCHYEAR=?
    order by a.CLASSLVL, a.CLASSCODE, a.CLASSNO, a.SCHLVL, a.SCHSESS, a.REGNO
     
    #4 edb-catherinewschan, 2021-02-11
  5. 55094568

    0004
    Expand Collapse

    文章:
    37
    讚:
    0
    謝謝
    很好用的SQL