oracle - 三个 select 语句上的 sql 完全外连接

Question

我想从三个表中选择值。每个表都有一个 buyer_entity_id 列。

我已经想出了在两个表上进行外连接的语法，但如何添加第三个表却让我困惑。

以下是两个表联接的语句，它完全按照我希望的方式工作：

select * from (select  b.buyer_entity_id, count(distinct(a.row_id)) as imps 
from imps a, anl_line b
where b.line_item_id=a.buyer_line_id 
and a.entity_id=3
group by b.buyer_entity_id
order by b.buyer_entity_id) tab1
full outer join (select b.buyer_entity_id, count(distinct(a.row_id)) as clicks 
from clicks a, anl_line b 
where a.buyer_line_id=b.line_item_id 
and a.entity_id=3
group by b.buyer_entity_id 
order by b.buyer_entity_id) tab2
on tab1.buyer_entity_id = tab2.buyer_entity_id;

第三个表将具有相同的 select 语句，并且也将联接到 Buyer_entity_id 值。但是，当我添加第三个选择语句时，我收到“缺少关键字”错误。下面是我的三路完整外连接语句：

select * from ((select  b.buyer_entity_id, count(distinct(a.row_id)) as imps 
from imps_table a, line_table b
where b.line_item_id=a.buyer_line_id 
and a.entity_id=3
group by b.buyer_entity_id
order by b.buyer_entity_id) tab1
full outer join (select b.buyer_entity_id, count(distinct(a.row_id)) as clicks 
from clicks_table a, line_table b 
where a.buyer_line_id=b.line_item_id 
and a.entity_id=3
group by b.buyer_entity_id 
order by b.buyer_entity_id) tab2)
outer join (select  b.buyer_entity_id, count(distinct(a.row_id)) as vers 
from vers_table a, line_table b
where b.line_item_id=a.buyer_line_id 
and a.entity_id=3
group by b.buyer_entity_id
order by  b.buyer_entity_id) tab3
on tab1.buyer_entity_id = tab2.buyer_entity_id and tab2.buyer_entity_id=tab3.buyer_entity_id;

Answer 1

错误如下：order by b.buyer_entity_id) tab2)。您需要一个 on 子句来指定 tab1 和 tab1 之间的连接条件。选项卡2。 order by b.buyer_entity) tab1 on

修复后。您需要将 full 添加到下一行，以便 outer join (select b.buyer_entity_id, count(distinct(a.row_id)) as vers 变为 full外连接 (select b.buyer_entity_id, count(distinct(a.row_id)) as vers。

这涵盖了语法问题，但不包括语义问题。

连接是成对的，表 1 是在某些条件下连接到表 2，然后结果将成为下一个连接的左侧，如果 tab1 和 tab3 匹配，但 tab2 不匹配，您希望发生什么？ tab3 数据和 tab2 空值。

select * 
from (select  b.buyer_entity_id, count(distinct(a.row_id)) as imps 
        from imps_table a, line_table b
        where b.line_item_id=a.buyer_line_id 
        and a.entity_id=3
        group by b.buyer_entity_id) tab1
full outer join (select b.buyer_entity_id, count(distinct(a.row_id)) as clicks 
        from clicks_table a, line_table b 
        where a.buyer_line_id=b.line_item_id 
        and a.entity_id=3
        group by b.buyer_entity_id) tab2
    on tab1.buyer_entity_id = tab2.buyer_entity_id
full outer join (select  b.buyer_entity_id, count(distinct(a.row_id)) as vers 
        from vers_table a, line_table b
        where b.line_item_id=a.buyer_line_id 
        and a.entity_id=3
        group by b.buyer_entity_id) tab3
    on tab3.buyer_entity_id in (tab1.buyer_entity_id, tab2.buyer_entity_id)
order by coalesce(tab1.buyer_entity_id
    , tab2.buyer_entity_id
    , tab3.buyer_entity_id);

另外，请丢失子查询中的 order by 它们不会为您做任何事情，因为不能保证顺序在连接中存在。

Answer 2

您可以使用WITH子句来简化一点：

使用first_part as
( ( 选择 .... ) 外连接 ( 选择 .... ));
从first_part 外连接中选择* ( select .... );

不过，您可能想重新审视整体逻辑，看看是否有更好的方法来完成您想要做的事情。多个外连接通常不是最有效的解决方案。

Answer 3

更快的方法如下所述
https://forums.oracle.com/thread/2388229

SELECT       COALESCE (a.id, b.id, c.id)     AS common_id
,       NVL2 (a.id, 1, NULL)           AS table_a_flag
,       NVL2 (b.id, 1, NULL)           AS table_b_flag
,       NVL2 (c.id, 1, NULL)           AS table_c_flag
FROM              table_a  a
FULL OUTER JOIN  table_b  b  ON  b.id  =           a.id
FULL OUTER JOIN      table_c  c  ON  c.id  = COALESCE (a.id, b.id)
ORDER BY  common_id;