select t.* from(
select ta.asset_name,ta.type_name,ta.name,ta.t_assets_sep_id,
(select ep.ACCESSORIES_PATH from t_assets_sep ep where ep.t_assets_sep_id=ta.t_assets_sep_id) as tp,
(select count(*) from t_assets t where t.t_assets_sep_id=ta.t_assets_sep_id and ((t.pastdue_type='1' AND ((assets_day_date-(sysdate-mader_date))<=0)) or (t.pastdue_type='2' AND ((instore_date+(USEFUL_LIFE*365))-sysdate<=0)))) as egq,
(select count(*) from t_assets t where t.t_assets_sep_id=ta.t_assets_sep_id and ((t.pastdue_type='1' AND ((assets_day_date-(sysdate-mader_date))>0 and (assets_day_date-(sysdate-mader_date))<=31)) or (t.pastdue_type='2' AND ( (instore_date+(USEFUL_LIFE*365))-sysdate>0 and (instore_date+(USEFUL_LIFE*365))-sysdate<=31)))) as gq
from( select a.asset_name,pe.type_name,s.name,s.t_assets_sep_id
from t_assets a,t_assets_type pe,t_assets_sep s where a.deparet_code
like '001_001%' and a.assets_type_id=pe.assets_type_id and s.t_assets_sep_id=a.t_assets_sep_id and a.state!='1'
group by pe.type_name,s.name,a.asset_name,s.t_assets_sep_id)ta )t where t.egq>0 or t.gq>0
帮忙优化下 谢谢
解决方案 »
- PL/SQL只有不选数据库才能进入
- 新手求教:关于oracle SQL PLUS 与数据库的问题
- 求助!急!oralce816升级到oracle817时要求输入oracle例程的“内部口令”,是什么?
- 请教存储过程中建表的问题,给高分哦。
- linux下tomcat的Oracle中文问题!
- 关于large pool 的两个问题
- 目录服务位于何处?
- 又是监听器问题!高手们进来呀!
- 我想备份数据库,出现如下错误,不知道如何解决啊?
- export/import 工具在oracle9i的那个地方,我怎么没找着
- oracle insert语句:序列(号)不存在
- 大家说一说-数据库命名规则-灌水的请绕绕!!
Create Table tp As select ep.ACCESSORIES_PATH from t_assets_sep ep where ep.t_assets_sep_id=ta.t_assets_sep_id;
Create Table egq As elect count(*)
from t_assets t
where t.t_assets_sep_id=ta.t_assets_sep_id
and ((t.pastdue_type='1' AND ((assets_day_date-(sysdate-mader_date))<=0)) or (t.pastdue_type='2' AND ((instore_date+(USEFUL_LIFE*365))-sysdate<=0)));
同理,将子查询先执行,将其结果放在一个临时表中,最后,直接从临时表中去查询,这样效率会搞很多的
如果是10g或是以上的话 可以用with
类似这种格式 形成多种临时表
with tb as(
select ep.ACCESSORIES_PATH from t_assets_sep ep where ep.t_assets_sep_id=ta.t_assets_sep_id),
tc as(...)
select a.asset_name,pe.type_name,s.name,s.t_assets_sep_id
from t_assets a,t_assets_type pe,t_assets_sep s where a.deparet_code
like '001_001%' and a.assets_type_id=pe.assets_type_id and s.t_assets_sep_id=a.t_assets_sep_id and a.state!='1'
group by pe.type_name,s.name,a.asset_name,s.t_assets_sep_id;
Create Table t1 As select ep.ACCESSORIES_PATH As tp,t_assets_sep_id from t_assets_sep ep where ep.t_assets_sep_id=ta.t_assets_sep_id;Create Table t2 As elect count(*) As egq,t.t_assets_sep_id
from t_assets t
where t.t_assets_sep_id=ta.t_assets_sep_id
and ((t.pastdue_type='1' AND ((assets_day_date-(sysdate-mader_date))<=0)) or (t.pastdue_type='2' AND ((instore_date+(USEFUL_LIFE*365))-sysdate<=0)));
Create Table t3 As
select count(*) As gq,t.t_assets_sep_id
from t_assets t
where t.t_assets_sep_id=ta.t_assets_sep_id
and ((t.pastdue_type='1'
AND ((assets_day_date-(sysdate-mader_date))>0
and (assets_day_date-(sysdate-mader_date))<=31)) or (t.pastdue_type='2' AND ( (instore_date+(USEFUL_LIFE*365))-sysdate>0 and (instore_date+(USEFUL_LIFE*365))-sysdate<=31)));
select t.*
from(
select ta.asset_name,ta.type_name,ta.name,ta.t_assets_sep_id,t1.tp,t2.egq,t3.gq
From ta ,t1,t2,t3 Where t1.t_assets_sep_id=ta.t_assets_sep_id And t2.t_assets_sep_id=ta.t_assets_sep_id And t3.t_assets_sep_id=ta.t_assets_sep_id ) t
where t.egq>0 or t.gq>0