SQL 請教如何修改SQL

本文由 tr-mussochan 在 2022-09-26 發表於 "WebSAMS 討論區" 討論區

  1. 57307132

    tr-mussochan
    Expand Collapse

    文章:
    5
    讚:
    0
    各位同工:

    以下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
     
    #1 tr-mussochan, 2022-09-26
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    255
    讚:
    0
    你好

    可以加入ENNAME 去顯示英文名

    select
    a.SCHYEAR '學年',
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    c.CHNAME '中文姓名',
    c.ENNAME '英文姓名',
    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 = 2022
    order by a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO
     
    #2 edb-catherinewschan, 2022-09-27