我有以下查询:
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作为名称吗?
一个公用表表达式必须接着是最终的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
运算符替换了您古老,过时且脆弱的隐式连接)