select count(*) as countValue from(
select ta.asset_name,ta.type_name,ta.name,ta.t_assets_sep_id,
(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.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>0where t.egq>0 or t.gq>0 加了这句速度慢了N被 ....求优化
select count(*) as countValue from(
select ta.asset_name,ta.type_name,ta.name,ta.t_assets_sep_id,
(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.assets_type_id=pe.assets_type_id and s.t_assets_sep_id=a.t_assets_sep_id and a.state!='1'
and ( ((a.pastdue_type='1' AND ((a.assets_day_date-(sysdate-a.mader_date))<=0)) or (a.pastdue_type='2' AND ((a.instore_date+(a.USEFUL_LIFE*365))-sysdate<=0))) or ((a.pastdue_type='1' AND ((a.assets_day_date-(sysdate-a.mader_date))>0 and (a.assets_day_date-(sysdate-a.mader_date))<=31)) or (a.pastdue_type='2' AND ( (a.instore_date+(a.USEFUL_LIFE*365))-sysdate>0 and (a.instore_date+(a.USEFUL_LIFE*365))-sysdate<=31))))
group by pe.type_name,s.name,a.asset_name,s.t_assets_sep_id)ta )t
这样快了好多先前要20多秒现在3秒以内了
运算的时候 用 count(id) 不要用 count(*)
where t.egq>0
用 union 连接
where t.gq>0