我如何优化此查询以缩短执行时间

Jaraisyn

这是我的查询

select case t.type
when 'N' then  (select count(*) from table10_other)
when 'L' then (select count(*) from table11_other)
when 'P' then (select count(*) from table12_other)
end as nlp, t.* 
from table t  
left outer join employee e on e.emp_id = t.emp_id
left outer join table2 t2 on t2.code= t.code and d.year = t.year 
order by e.name

包括ORDER BY部分会大大减慢我的查询速度。

迈克尔·皮安科夫（Michael Piankov）

我认为您很无聊，因为select count(*) from table总是会给您相同的答案。我的建议是您需要类似

select count(*) over (partition by t.type) as nlp
     , t.* 
from table t  
left outer join employee e on e.emp_id = t.emp_id
left outer join table2 t2 on t2.code= t.code and d.year = t.year 
order by e.name

或者如果您只想获得NLP的计数

select case when t.type IN ('N','L','P') THEN 
        count(*) over (partition by t.type)  
       END as nlp
     , t.* 
from table t  
left outer join employee e on e.emp_id = t.emp_id
left outer join table2 t2 on t2.code= t.code and d.year = t.year 
order by e.name

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。