SQL SQL 抽取問題

本文由 55094568 在 2020-03-16 發表於 "WebSAMS 討論區" 討論區

  1. 55094568

    55094568
    Expand Collapse

    文章:
    32
    讚:
    0
    select
    a.REGNO 'REGNO',
    a.CLASSLVL 'CLASSLVL',
    a.classcode '班別',
    a.classno '班號',
    a.enname '英文姓名',
    a.chname '中文姓名',
    a.sex '性別',
    a.SCHHOUSE 'SCHHUS',
    a.MOBILENO '學生電話',
    a.email 'EMAIL',



    (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) '家居地址',




    b.syspercscore 'T1A1 總平均分',
    c.syspercscore 'T1A2 總平均分',
    g.syspercscore 'T1總平均分',
    g.OVERCONDGRADECONVERSIONCOMPCODE 'T1操行',
    g.omclass 'T1班名次',
    g.omclasslvl 'T1級名次',
    d.syspercscore 'T2A1 總平均分',
    e.syspercscore 'T2A2 總平均分',
    h.syspercscore 'T2總平均分',
    h.OVERCONDGRADECONVERSIONCOMPCODE 'T2操行',
    h.omclass 'T2班名次',
    h.omclasslvl 'T2級名次',
    f.syspercscore '全年總平均分',
    f.omclass '全年班名次',
    f.omclasslvl '全年級名次',
    f.OVERCONDGRADECONVERSIONCOMPCODE '全年總操行'


    from vw_stu_lateststudent a
    left outer join tb_asr_studassessdata b
    on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear and b.timeseq=1101
    left outer join tb_asr_studassessdata c
    on a.suid=c.suid and a.stuid=c.stuid and a.schyear=c.schyear and c.timeseq=1102
    left outer join tb_asr_studassessdata d
    on a.suid=d.suid and a.stuid=d.stuid and a.schyear=d.schyear and d.timeseq=1201
    left outer join tb_asr_studassessdata e
    on a.suid=e.suid and a.stuid=e.stuid and a.schyear=e.schyear and e.timeseq=1202
    left outer join tb_asr_studassessdata f
    on a.suid=f.suid and a.stuid=f.stuid and a.schyear=f.schyear and f.timeseq=1000
    left outer join tb_asr_studassessdata g
    on a.suid=g.suid and a.stuid=g.stuid and a.schyear=g.schyear and g.timeseq=1100
    left outer join tb_asr_studassessdata h
    on a.suid=h.suid and a.stuid=h.stuid and a.schyear=h.schyear and h.timeseq=1200




    where a.schyear=?
    order by a.classlvl,a.classcode,a.classno





    抽取資料時 彈出以下句子

    The request may contains malicious code.
    Please contact the system administrator.


    之前都用到依個SQL 能否幫忙修改 ,謝謝
     
    #1 55094568, 2020-03-16
  2. 57873296

    edb-rn
    Expand Collapse

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

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

    select
    z.REGNO 'REGNO',
    z.CLASSLVL 'CLASSLVL',
    z.classcode '班別',
    z.classno '班號',
    z.enname '英文姓名',
    z.chname '中文姓名',
    z.sex '性別',
    z.SCHHOUSE 'SCHHUS',
    z.MOBILENO '學生電話',
    z.email 'EMAIL',
    (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) '家居地址',
    b.syspercscore 'T1A1 總平均分',
    c.syspercscore 'T1A2 總平均分',
    g.syspercscore 'T1總平均分',
    g.OVERCONDGRADECONVERSIONCOMPCODE 'T1操行',
    g.omclass 'T1班名次',
    g.omclasslvl 'T1級名次',
    d.syspercscore 'T2A1 總平均分',
    e.syspercscore 'T2A2 總平均分',
    h.syspercscore 'T2總平均分',
    h.OVERCONDGRADECONVERSIONCOMPCODE 'T2操行',
    h.omclass 'T2班名次',
    h.omclasslvl 'T2級名次',
    f.syspercscore '全年總平均分',
    f.omclass '全年班名次',
    f.omclasslvl '全年級名次',
    f.OVERCONDGRADECONVERSIONCOMPCODE '全年總操行'
    from vw_stu_lateststudent z
    left outer join tb_asr_studassessdata b
    on z.suid=b.suid and z.stuid=b.stuid and z.schyear=b.schyear and b.timeseq=1101
    left outer join tb_asr_studassessdata c
    on z.suid=c.suid and z.stuid=c.stuid and z.schyear=c.schyear and c.timeseq=1102
    left outer join tb_asr_studassessdata d
    on z.suid=d.suid and z.stuid=d.stuid and z.schyear=d.schyear and d.timeseq=1201
    left outer join tb_asr_studassessdata e
    on z.suid=e.suid and z.stuid=e.stuid and z.schyear=e.schyear and e.timeseq=1202
    left outer join tb_asr_studassessdata f
    on z.suid=f.suid and z.stuid=f.stuid and z.schyear=f.schyear and f.timeseq=1000
    left outer join tb_asr_studassessdata g
    on z.suid=g.suid and z.stuid=g.stuid and z.schyear=g.schyear and g.timeseq=1100
    left outer join tb_asr_studassessdata h
    on z.suid=h.suid and z.stuid=h.stuid and z.schyear=h.schyear and h.timeseq=1200
    where z.schyear=?
    order by z.classlvl,z.classcode,z.classno
     
  3. 55094568

    55094568
    Expand Collapse

    文章:
    32
    讚:
    0
    謝謝~~~~
     
    #3 55094568, 2020-05-21