SQL 舊有的SQL不能用

本文由 chautc 在 2020-03-23 發表於 "WebSAMS 討論區" 討論區

  1. 10004315

    chautc
    Expand Collapse

    文章:
    58
    讚:
    0
    select

    b.classname '班別',

    a.CLASSLVL '級別',

    a.classno '學號',

    a.chname '中文姓名',

    a.enname '英文姓名',

    a.regno '學生註冊編號',

    a.hkid '身份證號碼',

    a.pob '出生地點',

    dateformat(a.dob, 'DD/MM/YYYY') as dob,

    a.sex '性別',

    a.MOBILENO '學生手提電話',

    a.email '學生電郵',

    d.chname as 監護人姓名 ,

    d.OCCUPATION '監護人職業',

    a.hometel '住宅電話',

    d.EMERGENCYPHONE '緊急聯絡電話',

    (case when e.ch_des is not null then e.ch_des else '' end) +

    (case when trim(a.chdistrict)<>'' then trim(a.chdistrict) else '' end)+

    (case when trim(a.chstreet)<>'' then trim(a.chstreet) else '' end)+

    (case when trim(a.chvillageestate)<>'' then trim(a.chvillageestate) else '' end)+

    (case when trim(a.chbuilding)<>'' then trim(a.chbuilding) else '' end)+

    (case when trim(a.chblkno)<>'' then trim(a.chblkno)+'座' else '' end)+

    (case when trim(a.chfloorno)<>'' then trim(a.chfloorno)+'層' else '' end)+

    (case when trim(a.chflatno)<>'' then trim(a.chflatno)+'室' else '' end) as address

    from vw_stu_lateststudent a

    left outer join tb_sch_schclass b

    on b.classcode=a.classcode and

    b.schyear=a.schyear and

    b.suid = a.suid

    left outer join tb_hse_common c

    on c.code_id=a.schhouse and

    c.tb_id='SCHHUS' and

    c.suid = a.suid

    left outer join tb_stu_parent d

    on d.stuid = a.stuid and

    d.guardianind='1' and

    d.suid = a.suid

    left outer join tb_hse_common e

    on e.code_id=a.areacode and

    e.tb_id='AREACD' and

    e.suid = a.suid

    where a.schyear=?

    order by a.classcode, a.classno
     
  2. 57873296

    edb-rn
    Expand Collapse

    文章:
    231
    讚:
    0
    老師,請參考以下,

    只需要把table 的alias a. 改為 z.
    就可以成功抽取資料。

    select
    b.classname '班別',
    z.CLASSLVL '級別',
    z.classno '學號',
    z.chname '中文姓名',
    z.enname '英文姓名',
    z.regno '學生註冊編號',
    z.hkid '身份證號碼',
    z.pob '出生地點',
    dateformat(z.dob, 'DD/MM/YYYY') as dob,
    z.sex '性別',
    z.MOBILENO '學生手提電話',
    z.email '學生電郵',
    d.chname as 監護人姓名 ,
    d.OCCUPATION '監護人職業',
    z.hometel '住宅電話',
    d.EMERGENCYPHONE '緊急聯絡電話',
    (case when e.ch_des is not null then e.ch_des else '' end) +
    (case when trim(z.chdistrict)<>'' then trim(z.chdistrict) else '' end)+
    (case when trim(z.chstreet)<>'' then trim(z.chstreet) else '' end)+
    (case when trim(z.chvillageestate)<>'' then trim(z.chvillageestate) else '' end)+
    (case when trim(z.chbuilding)<>'' then trim(z.chbuilding) else '' end)+
    (case when trim(z.chblkno)<>'' then trim(z.chblkno)+'座' else '' end)+
    (case when trim(z.chfloorno)<>'' then trim(z.chfloorno)+'層' else '' end)+
    (case when trim(z.chflatno)<>'' then trim(z.chflatno)+'室' else '' end) as address
    from vw_stu_lateststudent z
    left outer join tb_sch_schclass b
    on b.classcode=z.classcode and
    b.schyear=z.schyear and
    b.suid = z.suid
    left outer join tb_hse_common c
    on c.code_id=z.schhouse and
    c.tb_id='SCHHUS' and
    c.suid = z.suid
    left outer join tb_stu_parent d
    on d.stuid = z.stuid and
    d.guardianind='1' and
    d.suid = z.suid
    left outer join tb_hse_common e
    on e.code_id=z.areacode and
    e.tb_id='AREACD' and
    e.suid = z.suid
    where z.schyear=?
    order by z.classcode, z.classno