只是一般问题，有人可以帮助如何对联合数据执行左连接吗？我有一个查询，我正在根据一些条件进行数据联合(使用table 1和table 2)，现在我需要与第三个表(table 5)执行左外连接，所以我正在寻找如何执行的帮助.只需示例查询就可以了.
我在小提琴上创建了场景，其中我使用表1和表2.100根据一些条件进行了联合 现在我需要使用联合的输出并在第三个表(即表5)上执行左连接.因此，如果它匹配，我需要执行left join on field 6 of table 5 with output field 1 of union data，然后在表5中插入行，除了字段9之外，所有值都将保持相同，我们将从联合输出数据(字段3)中获得:
输出将如下所示:
'j','abc','def','xyz_inc'
'j','abc','def','5_abc'
'j','abc','def','6_abc'
translate()，而不是拨打两次replace().您还可以合并您的translate()个呼叫，而不是将它们嵌套.regexp_split_to_table()，而不是包装unnest(string_to_array()).with your_big_union as (
select field1,field2,string_agg(distinct fld,',') field3
from(
select *
from(
select field1,field2,fld
from table1 t1
left join (
select * from table2
cross join regexp_split_to_table(trim(translate(field4,'{}','')),',') fld
)t2
on concat(t1.field2,';') similar to concat('%',t2.field3,'[();]%')
)x
)b
where field2 like '%fn@_%' escape '@' and fld is not null
group by field1,field2
union
(with cte as (
select field1,field2
,''''
||translate( btrim(field2)
,E'\n"'''
,'' )
||'''' as edited_field2
from table1)
,cte2 as (
select *,t.spotted_table as spotted_target,s.spotted_table as spotted_source
from cte
left join regexp_matches(
edited_field2
,'(?:UPDATE|INTO)(?:\s+ONLY)?\s+([[:alpha:]_]+[\.\w]*|"[^"]+"(?:\."[^"]+")?)'
,'gi'
) with ordinality as target_matches(hits,n1) on true
left join unnest(target_matches.hits) with ordinality as t(spotted_table,n2) on true
left join regexp_matches(
edited_field2
,'(?:FROM|JOIN|USING|TABLE)(?:\s+ONLY)?\s+([[:alpha:]_]+[\.\w]*|"[^"]+"(?:\."[^"]+")?)'
,'gi'
) with ordinality as source_matches(hits,n1) on true
left join unnest(source_matches.hits) with ordinality as s(spotted_table,n2) on true)
select field1
,string_agg(distinct spotted_target,',') as spotted_targets
-- ,string_agg(distinct spotted_source,',') as spotted_sources
,edited_field2
from cte2 where edited_field2 not like '%fn@_%' escape '@' and spotted_target is not null
and coalesce(split_part(spotted_target,'.',1) !~* '_inc"?$',true)
and coalesce(split_part(spotted_source,'.',1) !~* '_inc"?$',true)
group by field1,edited_field2
order by field1))
select *
from table5 left join your_big_union
on lower(your_big_union.field1) = table5.field6;
table5.field6加入field1，您将不会得到匹配项，因为您将'J'升级为field，而field6则保留了一个预设.我不知道如果您的连接两侧只有一个'j'，为什么您会期望3个结果.