SQL WebSAMS兄弟姐妹資料名單

本文由 koock 在 2019-09-10 發表於 "WebSAMS 討論區" 討論區

標籤:
  1. 169066

    koock
    Expand Collapse

    文章:
    14
    讚:
    0
    請問怎樣使用SQL製作這名單:兄弟姐妹資料名單(依級別) (R-STU048-C) ?WebSAMS 匯出excel 格式很難處理,謝謝!
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

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

    如果多過一位兄弟姊妹資料, 用" || " 來分隔。

    select
    a.CLASScode 'Class',
    a.CLASSNO 'Class No.',
    a.ENNAME 'English Name',
    a.CHNAME 'Chinese Name',
    a.SEX,
    (select list(string('STRN: ',SIB.STRN, ' , Name: ', SIB.enNAME, ' , Class: ', SIB.CLASScode), ' || ')
    from wsadmin.VW_STU_LATESTSTUDENT SIB
    left outer join wsadmin.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
    ) '兄弟姊妹'

    from wsadmin.VW_STU_LATESTSTUDENT a
    where a.SCHYEAR=? and a.CLASSLVL=?
    order by a.SUID, a.SCHLVL, a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO
     
    #2 edb-catherinewschan, 2019-09-13
  3. 169066

    koock
    Expand Collapse

    文章:
    14
    讚:
    0
    謝謝,可否只顯示在學學生
     
  4. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    73
    讚:
    0
    你好,

    把離校學生的資料剔除就可以。
    請試以下的SQL

    select
    a.CLASScode 'Class',
    a.CLASSNO 'Class No.',
    a.ENNAME 'English Name',
    a.CHNAME 'Chinese Name',
    a.SEX,
    (select list(string('STRN: ',SIB.STRN, ' , Name: ', SIB.enNAME, ' , Class: ', SIB.CLASScode), ' || ')
    from wsadmin.VW_STU_LATESTSTUDENT SIB
    left outer join wsadmin.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
    ) '兄弟姊妹'
    from wsadmin.VW_STU_LATESTSTUDENT a
    left outer join wsadmin.TB_STU_DEPARTURE b on a.suid=b.suid and a.stuid=b.stuid
    where b.DEPREASON is null and a.SCHYEAR=? and a.CLASSLVL=?
    order by a.SUID, a.SCHLVL, a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO
     
    #4 edb-catherinewschan, 2019-09-24 , 3:10 下午