请帮我做下优化 我不是做DBA的 对SQL不太明白
下面语句查询的时候造成了 01652 错误 又没办法修改临时表空间 请帮下忙 急
select a.formset_inst_id, a.ext1 as 编号, a.ext11 as 申请单位, a.ext7 as 申请人,
to_char(a.ext27,'yyyy-mm-dd') as 申请日期, b.name as 姓名, b.username as 用户名, b.type as 类别, b.io as 入退 from mv_formset_inst a
inner join
(
select a.formset_inst_id,a.name,b.username,c.type,d.io from
(
select a.formset_inst_id,1 as aa, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='姓名1'
union select a.formset_inst_id,2 as aa, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='姓名2'
union select a.formset_inst_id,3 as aa, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='姓名3'
union select a.formset_inst_id,4 as aa, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='姓名4'
union select a.formset_inst_id,5 as aa, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='姓名5'
) a
inner join
(
select a.formset_inst_id,1 as bb, b.value as username from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='用户名1'
union select a.formset_inst_id,2 as bb, b.value as username from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='用户名2'
union select a.formset_inst_id,3 as bb, b.value as username from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='用户名3'
union select a.formset_inst_id,4 as bb, b.value as username from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='用户名4'
union select a.formset_inst_id,5 as bb, b.value as username from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='用户名5'
) b on a.formset_inst_id=b.formset_inst_id and a.aa=b.bb
inner join
(
select a.formset_inst_id,1 as cc, b.value as type from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='类别1'
union select a.formset_inst_id,2 as cc, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='类别2'
union select a.formset_inst_id,3 as cc, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='类别3'
union select a.formset_inst_id,4 as cc, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='类别4'
union select a.formset_inst_id,5 as cc, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='类别5'
) c on a.formset_inst_id=c.formset_inst_id and a.aa=c.cc
inner join
(
select a.formset_inst_id,1 as dd, b.value as io from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='入退1'
union select a.formset_inst_id,2 as dd, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='入退2'
union select a.formset_inst_id,3 as dd, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='入退3'
union select a.formset_inst_id,4 as dd, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='入退4'
union select a.formset_inst_id,5 as dd, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='入退5'
) d on a.formset_inst_id=d.formset_inst_id and a.aa=d.dd
) b on a.formset_inst_id=b.formset_inst_id
where a.formset_id=261 and b.name is not null
下面语句查询的时候造成了 01652 错误 又没办法修改临时表空间 请帮下忙 急
select a.formset_inst_id, a.ext1 as 编号, a.ext11 as 申请单位, a.ext7 as 申请人,
to_char(a.ext27,'yyyy-mm-dd') as 申请日期, b.name as 姓名, b.username as 用户名, b.type as 类别, b.io as 入退 from mv_formset_inst a
inner join
(
select a.formset_inst_id,a.name,b.username,c.type,d.io from
(
select a.formset_inst_id,1 as aa, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='姓名1'
union select a.formset_inst_id,2 as aa, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='姓名2'
union select a.formset_inst_id,3 as aa, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='姓名3'
union select a.formset_inst_id,4 as aa, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='姓名4'
union select a.formset_inst_id,5 as aa, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='姓名5'
) a
inner join
(
select a.formset_inst_id,1 as bb, b.value as username from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='用户名1'
union select a.formset_inst_id,2 as bb, b.value as username from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='用户名2'
union select a.formset_inst_id,3 as bb, b.value as username from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='用户名3'
union select a.formset_inst_id,4 as bb, b.value as username from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='用户名4'
union select a.formset_inst_id,5 as bb, b.value as username from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='用户名5'
) b on a.formset_inst_id=b.formset_inst_id and a.aa=b.bb
inner join
(
select a.formset_inst_id,1 as cc, b.value as type from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='类别1'
union select a.formset_inst_id,2 as cc, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='类别2'
union select a.formset_inst_id,3 as cc, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='类别3'
union select a.formset_inst_id,4 as cc, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='类别4'
union select a.formset_inst_id,5 as cc, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='类别5'
) c on a.formset_inst_id=c.formset_inst_id and a.aa=c.cc
inner join
(
select a.formset_inst_id,1 as dd, b.value as io from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='入退1'
union select a.formset_inst_id,2 as dd, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='入退2'
union select a.formset_inst_id,3 as dd, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='入退3'
union select a.formset_inst_id,4 as dd, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='入退4'
union select a.formset_inst_id,5 as dd, b.value as name from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id and b.name='入退5'
) d on a.formset_inst_id=d.formset_inst_id and a.aa=d.dd
) b on a.formset_inst_id=b.formset_inst_id
where a.formset_id=261 and b.name is not null
解决方案 »
- oracle中查询的小问题,在线等
- oracle sql 定义变量赋值后再select的问题(sqlserver转oracle)
- 急,存储过程出现ORA-06550错误,请高手帮忙看看是什么问题?先谢了
- 求兼职Oracle DBA
- 在oracle数据库中,如何建立两帐数据表的主外键关系?
- Oracle8i如何在dos下倒数据
- 如何将SQL SERVER中的一个表的内容插入到ORACLE的表中(用SQL 语句)?特急,请高手帮帮忙。
- 对于一个在相当大数量级的记录,如何优化设计表的物理结构?
- oracle 通过dblink访问远程存储过程,返回游标到本地
- 求助,linux下成功安装了 oracle 18c 有谁教我怎么使用呢?
- oracle 多版本
- 求一SQL的写法
to_char(a.ext27,'yyyy-mm-dd') as 申请日期, b.name as 姓名, b.username as 用户名, b.type as 类别, b.io as 入退
from mv_formset_inst a
inner join mv_form_data_inst b on a.formset_inst_id=b.formset_inst_id
where (b.name like '姓名%' or b.name like '用户%' or b.name like '类别%' or b.name like '入退%')
and right(b.name,1) between '1' and '5'