學生資料 如何運用SQL抽取學生資料中的國藉?

本文由 tr-bryanchoy 在 2021-07-21 發表於 "WebSAMS 討論區" 討論區

  1. 55371223

    tr-bryanchoy
    Expand Collapse

    文章:
    1
    讚:
    0
    正準備用SQL抽取學生手冊內的學生資料,但請問如何運用SQL抽取學生資料中的國藉?
     
    #1 tr-bryanchoy, 2021-07-21
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    你好,可以參考以下的SQL

    select
    a.CHNAME '中文姓名',
    a.ENNAME '英文姓名',
    a.SEX '性別',
    a.REGNO '註冊編號',
    a.STRN '學生編號',
    dateformat(a.DOB, 'DD/MM/YYYY') '出生日期',
    a.BIRTHCERT '出生證明書',
    a.HKID '身份證',
    a.POB '出生地點',
    n.CH_DES '國藉',
    h.CH_DES '學社',
    dateformat(c.FIRSTATTDATE, 'DD/MM/YYYY') '入學日期',
    c.CLASSLVL '入學級別',
    c.CLASSCODE '入學班別',
    b.schyear '最後學年',
    b.CLASSLVL '最後級別',
    b.CLASSCODE '最後班別',
    b1.ch_des '狀況'
    from wsadmin.VW_STU_LATESTSTUDENT a
    join (select e.suid, e.stuid, e.firstattdate, e.CLASSCODE, e.CLASSLVL from wsadmin.tb_stu_stuschrec e
    where e.firstattdate = (select min(f.firstattdate) from wsadmin.tb_stu_stuschrec f where e.suid=f.suid and e.stuid=f.stuid)) c
    on a.suid=c.suid and a.stuid=c.stuid
    join (select e.suid, e.stuid, e.schyear, e.CLASSCODE, e.CLASSLVL, e.status from wsadmin.tb_stu_stuschrec e
    where e.firstattdate = (select max(f.firstattdate) from wsadmin.tb_stu_stuschrec f where e.suid=f.suid and e.stuid=f.stuid)) b
    on a.suid=b.suid and a.stuid=b.stuid
    left outer join wsadmin.tb_hse_common b1
    on b.suid=b1.suid and b.status=b1.code_id and b1.tb_id='STUDST'
    left outer join wsadmin.TB_HSE_COMMON n
    on a.SUID=n.SUID and n.CODE_ID=a.Nationality and n.TB_ID='NATION'
    left outer join wsadmin.TB_HSE_COMMON h
    on a.SUID=h.SUID and h.CODE_ID=a.SCHHOUSE and h.TB_ID='SCHHUS'
    where a.schyear=? and a.classlvl=?
    order by a.classlvl, a.classcode, a.classno
     
    #2 edb-catherinewschan, 2021-07-23