如何改善postgresql查询？

Question

有人可以告诉我如何改进下面的查询，并使其成为一个陈述而不是两个陈述？由于

CREATE LOCAL TEMP TABLE tmpdetail2 
WITH (OIDS) 
ON COMMIT DROP as 
    select d2.detailid, d2.objid, d2.p 
    from _detail d2       
    where d2.detailid in (19, 106); 

    select distinct d.detailid, d.p, d.pval, d.objid 
    from _detail d 
    left join tmpdetail2 d2 
    on d.objid = d2.objid 
    where d2.objid is null 
    and d.p not in(select p from tmpdetail2) 
    order by p asc, d.detailid asc; 

Answer 1

使用common table expression：

with tmpdetail2 as (
    select d2.detailid, d2.objid, d2.p 
    from _detail d2       
    where d2.detailid in (19, 106) 
) 
select distinct d.detailid, d.p, d.pval, d.objid 
from _detail d 
left join tmpdetail2 d2 
    on d.objid = d2.objid 
where d2.objid is null 
    and d.p not in(select p from tmpdetail2) 
order by p asc, d.detailid asc;