Last school attended

本文由 P 在 2008-10-15 發表於 "WebSAMS 討論區" 討論區

  1. 55078230

    P
    Expand Collapse

    文章:
    1
    讚:
    0

    請問如何用SQL提取學生的基本資料(姓名,班別..)及Last school attended?

    請幫忙?

     
  2. 55169025

    EDB-Chong
    Expand Collapse

    文章:
    0
    讚:
    0

    P:

    select
      s.CLASSNAME 'Class',
      a.CLASSNO 'Class No',
      a.CHNAME 'Name (Chi)',
      a.ENNAME 'Name (Eng)',
      a.SEX,
      a.SCHFROM,
      a.REGNO,
      a.STRN,
      dateformat(a.DOB, 'DD/MM/YYYY') as DOB,
      a.BIRTHCERT,
      a.HKID,
      a.POB,
      a.HOMETEL,
      guard.CHNAME as 'Guardian (Chi)',
      guard.EMERGENCYPHONE,
      a.SCHFROM,
      (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
      ) 'Sibling'
     
    from VW_STU_LATESTSTUDENT a

    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'

    where a.SCHYEAR=? and a.CLASSLVL=?
    order by a.SUID, a.SCHLVL, a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO

    EDB-HMCHONG

     
    #2 EDB-Chong, 2008-10-15