select c.name as company_name,c.company_id
from t_area_configure c left join t_bpm_proc b on b.company_id=c.company_id
and ( b.flow_id in(select a.flow_id from t_bns_gcb__main a,
t_bns_gcb__task b,t_bpm_proc c
where a.flow_id=b.flow_id and a.flow_id=c.flow_id and c.state=3
having (sum(case when b.ht_name='qdjr' then 1 else 0 end)<=1 or
sum(case when b.ht_name='xckc' then 1 else 0 end)<=1)
group by a.flow_id) )
Group By c.name,c.company_id;数据有几十W数据括号里的子查询执行起来太慢,有什么好的方法优化,望高手指教
from t_area_configure c left join t_bpm_proc b on b.company_id=c.company_id
and ( b.flow_id in(select a.flow_id from t_bns_gcb__main a,
t_bns_gcb__task b,t_bpm_proc c
where a.flow_id=b.flow_id and a.flow_id=c.flow_id and c.state=3
having (sum(case when b.ht_name='qdjr' then 1 else 0 end)<=1 or
sum(case when b.ht_name='xckc' then 1 else 0 end)<=1)
group by a.flow_id) )
Group By c.name,c.company_id;数据有几十W数据括号里的子查询执行起来太慢,有什么好的方法优化,望高手指教
sum(case when b.ht_name='xckc' then 1 else 0 end) <=1)
group by a.flow_id)
这里的问题了
是不是b.ht_name='qdjr' 或者 b.ht_name='xckc' 有且只能有一条啊?就是说要么qdjr有一条要么xckc有一条或者都没有?
那么not in改not exists,则自个儿疼自个儿,一疼一个准儿。另外,注意关联字段双表索引。
in 的效率不一定就比exists低啊!
当子查询返回较多数据或者子查询不走索引时,又或者索引效率很低时,in的效率都比exists高啊!
select * from a,c where a.id=c.id(+) and c.id is not null
您先运行一下这句先吧
select a.flow_id
from t_bns_gcb__main a,
t_bns_gcb__task b,
t_bpm_proc c
where a.flow_id = b.flow_id
and a.flow_id = c.flow_id
and c.state = 3
having(sum(case when b.ht_name = 'qdjr' then 1 else 0 end) <= 1
or sum(case when b.ht_name = 'xckc' then 1 else 0 end) <= 1)
group by a.flow_id看看会不会很慢,以及得到的结果集数据量是多少?
您这句真让人模糊,也写的很烂having(sum(case when b.ht_name = 'qdjr' then 1 else 0 end) <= 1
or sum(case when b.ht_name = 'xckc' then 1 else 0 end) <= 1)
可能原因在于次此