温馨提示:本文翻译自stackoverflow.com,查看原文请点击:postgresql - How to give select query a name in postgres
postgresql

postgresql - 如何在postgres中给选择查询一个名字

发布于 2020-03-27 11:40:59

我有以下查询:

select distinct encoder.encoder enc, src_ip, encoder.datacenter 
from logical_service, unnest(tags) enc, encoder, mpeg_out 
where enc like 'encoder:%' 
  and substring(enc,9) = encoder.encoder 
  and logical_service.lid=mpeg_out.lid 
  and encoder.datacenter=mpeg_out.datacenter 
order by encoder.encoder

我想给这个查询起一个名字,以便以后使用。我想这样做,但是最终失败了:

with encoder_alias as (
  select distinct encoder.encoder enc, src_ip, encoder.datacenter 
  from logical_service, unnest(tags) enc, encoder, mpeg_out 
  where enc like 'encoder:%' 
    and substring(enc,9) = encoder.encoder 
    and logical_service.lid=mpeg_out.lid 
    and encoder.datacenter=mpeg_out.datacenter 
    order by encoder.encoder
)

它没有给出任何有关为什么不起作用的输出。知道为什么我不能使用with作为名称吗?

查看更多

查看更多

提问者
Jay266
被浏览
167
a_horse_with_no_name 2019-07-03 23:03

一个公用表表达式必须接着是最终的SELECT语句:

with encoder_alias as (
  select distinct encoder.encoder enc, src_ip, encoder.datacenter 
  from logical_service
     cross join unnest(tags) enc
     join encoder on substring(enc,9) = encoder.encoder 
     join mpeg_out on logical_service.lid = mpeg_out.lid 
                  and encoder.datacenter = mpeg_out.datacenter 
  where enc like 'encoder:%' 
  order by encoder.encoder
)
select *             ---<<< here
from encoder_alias;

(请注意,我用“现代”(30岁)显式JOIN运算符替换了您古老,过时且脆弱的隐式连接