SQL 抽不到一年級學生資料

本文由 貓巴士 在 2014-08-24 發表於 "WebSAMS 討論區" 討論區

  1. 55054620

    貓巴士
    Expand Collapse

    文章:
    7
    讚:
    0
    To: 石頭先生

    不知為何以下SQL抽不到一年級學生的資料,但其他年級則沒問題,為什麼呢?

    select
    a.CLASSCODE,
    a.CLASSNO,
    a.CHNAME as '學生中文姓名',
    a.ENNAME as '學生英文姓名',
    a.SEX as' 性別',
    dateformat(a.DOB, 'YYYY/MM/DD') as '出生日期',
    a.PLACEOFORIGIN as '籍貫',
    a.REGNO as'註冊編號',
    a.STRN '學生編號',
    d.chname '監護人姓名',
    d.OCCUPATION as '監護人職業',
    f.ch_des '監護人關係',

    (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.HOMETEL as'電話',
    d.EMERGENCYPHONE '監護人緊急聯絡電話',
    g.CH_DES'區議會分區',
    dateformat(h.FIRSTATTDATE, 'YYYY/MM/DD') as '入讀本校日期',
    (datediff(day, a.dob, '2014-09-01')/365) as '年齡'

    from VW_STU_LATESTSTUDENT a

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

    join tb_stu_parent d
    on d.stuid = a.stuid and d.SUID=a.SUID and d.GUARDIANIND='1'

    join tb_hse_common f
    on f.code_id=d.relation and f.tb_id='RELATE' and f.SUID = d.SUID

    left outer join
    (select STUID, min(FIRSTATTDATE) 'FIRSTATTDATE' from
    TB_STU_STUSCHREC group by STUID) h on h.STUID = a.STUID

    where a.SCHYEAR = ? and a.Classlvl=?

    order by a.CLASSCODE, a.CLASSNO
     
    #1 貓巴士, 2014-08-24
  2. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    以上SQL主要是抽取有監護人的學生,至於抽不到一年級學生的資料,老師可以檢查監護人的設定 或 改用以下SQL列出所有學生。

    gud.jpg

    select
    a.CLASSCODE,
    a.CLASSNO,
    a.CHNAME as '學生中文姓名',
    a.ENNAME as '學生英文姓名',
    a.SEX as' 性別',
    dateformat(a.DOB, 'YYYY/MM/DD') as '出生日期',
    a.PLACEOFORIGIN as '籍貫',
    a.REGNO as'註冊編號',
    a.STRN '學生編號',
    d.chname '監護人姓名',
    d.OCCUPATION as '監護人職業',
    f.ch_des '監護人關係',
    (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.HOMETEL as'電話',
    d.EMERGENCYPHONE '監護人緊急聯絡電話',
    g.CH_DES'區議會分區',
    dateformat(h.FIRSTATTDATE, 'YYYY/MM/DD') as '入讀本校日期',
    (datediff(day, a.dob, '2014-09-01')/365) as '年齡'
    from VW_STU_LATESTSTUDENT a
    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 d
    on d.stuid = a.stuid and d.SUID=a.SUID and d.GUARDIANIND='1'
    left outer join tb_hse_common f
    on f.code_id=d.relation and f.tb_id='RELATE' and f.SUID = d.SUID
    left outer join
    (select STUID, min(FIRSTATTDATE) 'FIRSTATTDATE' from
    TB_STU_STUSCHREC group by STUID) h on h.STUID = a.STUID
    where a.SCHYEAR = ? and a.Classlvl=?
    order by a.CLASSCODE, a.CLASSNO
     
    #2 edb-石頭, 2014-08-25