SQL 想修改有關查學生升級狀況的SQL

本文由 Savio 在 2019-08-29 發表於 "WebSAMS 討論區" 討論區

  1. 55395767

    Savio
    Expand Collapse

    文章:
    22
    讚:
    0
    我校想查全校學生的升班情況,但同時想知他們的「學校註冊編號」,我要怎樣將 a.REGNO '註冊編號', 加去下面的SQL?

    select
    a.SCHYEAR '學年',
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    c.CHNAME '姓名',
    c.SEX '性別',
    d.CH_DES '狀況',
    b.SCHYEAR '下學年',
    b.CLASSCODE '下學年班別',
    b.CLASSNO '下學年班號'
    from wsadmin.TB_STU_STUSCHREC a
    left outer join wsadmin.TB_STU_STUSCHREC b
    on a.SUID = b.SUID and a.SCHSESS = b.SCHSESS and a.SCHYEAR + 1 = b.SCHYEAR and a.STUID = b.STUID
    left outer join wsadmin.TB_STU_STUDENT c
    on a.SUID = c.SUID and a.STUID = c.STUID
    left outer join wsadmin.TB_HSE_COMMON d
    on a.SUID = d.SUID and a.STATUS = d.CODE_ID and d.TB_ID = 'STUDST'
    where a.SCHYEAR = ?
    order by a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    74
    讚:
    0
    你好, 老師

    因為TB_STU_STUDENT的TABLE才有regno這個FIELD, 所以要改為 c.regno

    請參考以下的SQL :-

    select
    a.SCHYEAR '學年',
    c.regno '註冊編號',
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    c.CHNAME '姓名',
    c.SEX '性別',
    d.CH_DES '狀況',
    b.SCHYEAR '下學年',
    b.CLASSCODE '下學年班別',
    b.CLASSNO '下學年班號'
    from wsadmin.TB_STU_STUSCHREC a
    left outer join wsadmin.TB_STU_STUSCHREC b
    on a.SUID = b.SUID and a.SCHSESS = b.SCHSESS and a.SCHYEAR + 1 = b.SCHYEAR and a.STUID = b.STUID
    left outer join wsadmin.TB_STU_STUDENT c
    on a.SUID = c.SUID and a.STUID = c.STUID
    left outer join wsadmin.TB_HSE_COMMON d
    on a.SUID = d.SUID and a.STATUS = d.CODE_ID and d.TB_ID = 'STUDST'
    where a.SCHYEAR = ?
    order by a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO
     
    #2 edb-catherinewschan, 2019-09-04