學生資料 如何抽取學生家長資料?

本文由 Wongw 在 2018-11-06 發表於 "WebSAMS 討論區" 討論區

  1. 116898

    Wongw
    Expand Collapse

    文章:
    13
    讚:
    0
    如何抽取學生家長資料?包括父親、母親職業及其他有紀錄的家長及監護人職業。
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    20
    讚:
    0
    請參加以下的SQL

    select
    s.CLASSNAME '班別',
    a.CLASSNO '學號',
    a.ENNAME '英文姓名',
    a.CHNAME '中文姓名',
    a.REGNO '註冊編號',
    a.STRN '學生編號',
    a.HOMETEL '家庭電話',
    f.chname '父親中文姓名',
    f.enname '父親英文姓名',
    f.OCCUPATION '父親職業',
    m.chname '母親中文姓名',
    m.enname '母親英文姓名',
    m.OCCUPATION '母親職業',
    gp.chname '祖父中文姓名',
    gp.enname '祖父英文姓名',
    gp.OCCUPATION '祖父職業',
    gm.chname '祖母中文姓名',
    gm.enname '祖母英文姓名',
    gm.OCCUPATION '祖母職業',
    bro.chname '兄長中文姓名',
    bro.enname '兄長英文姓名',
    bro.OCCUPATION '兄長職業',
    sis.chname '姊姊中文姓名',
    sis.enname '姊姊英文姓名',
    sis.OCCUPATION '姊姊職業',
    relat.chname '親戚中文姓名',
    relat.enname '親戚英文姓名',
    relat.OCCUPATION '親戚職業',
    oth.chname '其他中文姓名',
    oth.enname '其他英文姓名',
    oth.OCCUPATION '其他親戚職業',
    stf.chname '繼父中文姓名',
    stf.enname '繼父英文姓名',
    stf.OCCUPATION '繼父職業'
    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 f on
    a.SUID = f.SUID and a.STUID = f.STUID and f.RELATION = '01'
    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 gp on
    a.SUID = gp.SUID and a.STUID = gp.STUID and gp.RELATION = '03'
    left outer join wsadmin.TB_STU_PARENT gm on
    a.SUID = gm.SUID and a.STUID = gm.STUID and gm.RELATION = '04'
    left outer join wsadmin.TB_STU_PARENT bro on
    a.SUID = bro.SUID and a.STUID = bro.STUID and bro.RELATION = '05'
    left outer join wsadmin.TB_STU_PARENT sis on
    a.SUID = sis.SUID and a.STUID = sis.STUID and sis.RELATION = '06'
    left outer join wsadmin.TB_STU_PARENT relat on
    a.SUID = relat.SUID and a.STUID = relat.STUID and relat.RELATION = '07'
    left outer join wsadmin.TB_STU_PARENT oth on
    a.SUID = oth.SUID and a.STUID = oth.STUID and oth.RELATION = '08'
    left outer join wsadmin.TB_STU_PARENT stf on
    a.SUID = stf.SUID and a.STUID = stf.STUID and stf.RELATION = '51'
    where a.SCHYEAR=? and a.CLASSLVL=?
    order by a.SUID, a.SCHLVL, a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO
     
    #2 edb-catherinewschan, 2018-11-09