sql - 如何改善Postgresql查询？

有人能告诉我如何改进下面的查询，使其成为一个语句而不是两个语句吗？谢谢

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;

最佳答案

使用acommon 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;

关于sql - 如何改善Postgresql查询？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25686914/

D2

sql - 如何改善Postgresql查询？