sql - 如何改进 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;