# SQL SQL 抽取問題

1. ### 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.omclass 'T1班名次',
g.omclasslvl 'T1級名次',
d.syspercscore 'T2A1 總平均分',
e.syspercscore 'T2A2 總平均分',
h.syspercscore 'T2總平均分',
h.omclass 'T2班名次',
h.omclasslvl 'T2級名次',
f.syspercscore '全年總平均分',
f.omclass '全年班名次',
f.omclasslvl '全年級名次',

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.

之前都用到依個SQL 能否幫忙修改 ,謝謝

#1 55094568, 2020-03-16
2. ### 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.omclass 'T1班名次',
g.omclasslvl 'T1級名次',
d.syspercscore 'T2A1 總平均分',
e.syspercscore 'T2A2 總平均分',
h.syspercscore 'T2總平均分',
h.omclass 'T2班名次',
h.omclasslvl 'T2級名次',
f.syspercscore '全年總平均分',
f.omclass '全年班名次',
f.omclasslvl '全年級名次',
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 Expand Collapse

文章:
32
讚:
0
謝謝~~~~

#3 55094568, 2020-05-21