全连接处理合并三个关联表

全连接处理合并三个关联表

1、三个表

创建三个表，通过tid oid rid进行关联

create table ttest (tid varchar2(4),tname varchar2(8),tdate date,tnum number);
create table otest (oid varchar2(4),oname varchar2(8),odate date,onum number);
create table rtest (rid varchar2(4),rname varchar2(8),rdate date,rnum number);

以下简称t表、 r表、 o表，准备好数据之后：

SQL> select * from ttest t;

TID  TNAME    TDATE             TNUM
---- -------- ----------- ----------
BBBB bbbbbbbb 2022-6-20            5
AAAA aaaaaaaa 2022-6-13           20
AAAA aaaaaaaa 2022-6-13           14
BBBB bbbbbbbb 2022-6-19           11
BBBB bbbbbbbb 2022-6-18            3

Executed in 0.074 seconds


SQL> select * from otest o;

OID  ONAME    ODATE             ONUM
---- -------- ----------- ----------
BBBB bbbbbbbb 2022-6-20           16
AAAA aaaaaaaa 2022-6-18            7
BBBB bbbbbbbb 2022-6-19           10
AAAA aaaaaaaa 2022-6-19            6
BBBB bbbbbbbb 2022-6-17           15

Executed in 0.069 seconds


SQL> select * from rtest r;

RID  RNAME    RDATE             RNUM
---- -------- ----------- ----------
BBBB bbbbbbbb 2022-6-20            4
AAAA aaaaaaaa 2022-6-17           19
BBBB bbbbbbbb 2022-6-13           20
AAAA aaaaaaaa 2022-6-19            6
BBBB bbbbbbbb 2022-6-14            4

Executed in 0.07 seconds

2、目标

三个表按关联ID，将重复的ID，NAME和日期处理，简化表结构：

ID  NAME    DATE             TNUM       ONUM       RNUM

3、实现

（1）t表有数据，o表有数据

--t有，o有
select a.tid   as mid,
       a.tname as mname,
       a.tdate as mdate,
       a.tnum,
       a.onum,
       a.rnum
  from (select c.*, r.*
          from rtest r
          full join (select t.*, o.*
                      from ttest t
                      full join otest o
                        on t.tid = o.oid
                       and t.tdate = o.odate
                     order by t.tdate) c
            on r.rid = c.oid
           and r.rdate = c.odate) a
 where a.tid is not null;
   

（2）t表无数据，o表有数据

--t无，o有
select a.oid   as mid,
       a.oname as mname,
       a.odate as mdate,
       a.tnum,
       a.onum,
       a.rnum
  from (select c.*, r.*
          from rtest r
          full join (select t.*, o.*
                      from ttest t
                      full join otest o
                        on t.tid = o.oid
                       and t.tdate = o.odate
                     order by t.tdate) c
            on r.rid = c.oid
           and r.rdate = c.odate) a
 where a.tid is null
   and a.oid is not null;
   

（3）t表无数据，o表无数据，r表有数据

--t无，o无，r有
select a.rid   as mid,
       a.rname as mname,
       a.rdate as mdate,
       a.tnum,
       a.onum,
       a.rnum
  from (select c.*, r.*
          from rtest r
          full join (select t.*, o.*
                      from ttest t
                      full join otest o
                        on t.tid = o.oid
                       and t.tdate = o.odate
                     order by t.tdate) c
            on r.rid = c.oid
           and r.rdate = c.odate) a
 where a.tid is null
   and a.oid is null
   and a.rid is not null;

（4）合并三个查询

select a.tid   as mid,
       a.tname as mname,
       a.tdate as mdate,
       a.tnum,
       a.onum,
       a.rnum
  from (select c.*, r.*
          from rtest r
          full join (select t.*, o.*
                      from ttest t
                      full join otest o
                        on t.tid = o.oid
                       and t.tdate = o.odate
                     order by t.tdate) c
            on r.rid = c.oid
           and r.rdate = c.odate) a
 where a.tid is not null
union
select a.oid   as mid,
       a.oname as mname,
       a.odate as mdate,
       a.tnum,
       a.onum,
       a.rnum
  from (select c.*, r.*
          from rtest r
          full join (select t.*, o.*
                      from ttest t
                      full join otest o
                        on t.tid = o.oid
                       and t.tdate = o.odate
                     order by t.tdate) c
            on r.rid = c.oid
           and r.rdate = c.odate) a
 where a.tid is null
   and a.oid is not null
union
select a.rid   as mid,
       a.rname as mname,
       a.rdate as mdate,
       a.tnum,
       a.onum,
       a.rnum
  from (select c.*, r.*
          from rtest r
          full join (select t.*, o.*
                      from ttest t
                      full join otest o
                        on t.tid = o.oid
                       and t.tdate = o.odate
                     order by t.tdate) c
            on r.rid = c.oid
           and r.rdate = c.odate) a
 where a.tid is null
   and a.oid is null
   and a.rid is not null;

执行结果：

MID  MNAME    MDATE             TNUM       ONUM       RNUM
---- -------- ----------- ---------- ---------- ----------
AAAA aaaaaaaa 2022-6-13           14            
AAAA aaaaaaaa 2022-6-13           20            
AAAA aaaaaaaa 2022-6-17                                 19
AAAA aaaaaaaa 2022-6-18                       7 
AAAA aaaaaaaa 2022-6-19                       6          6
BBBB bbbbbbbb 2022-6-13                                 20
BBBB bbbbbbbb 2022-6-14                                  4
BBBB bbbbbbbb 2022-6-17                      15 
BBBB bbbbbbbb 2022-6-18            3            
BBBB bbbbbbbb 2022-6-19           11         10 
BBBB bbbbbbbb 2022-6-20            5         16          4

11 rows selected

（5）对比验证

--三表全连接   
select  c.*,r.*
  from rtest r
  full join (select t.*, o.*
               from ttest t
               full join otest o
                 on t.tid = o.oid
                and t.tdate = o.odate
              order by t.tdate) c
    on r.rid = c.oid
   and r.rdate = c.odate;

执行结果，与（4）核对，完全一致。

TID  TNAME    TDATE             TNUM OID  ONAME    ODATE             ONUM RID  RNAME    RDATE             RNUM
---- -------- ----------- ---------- ---- -------- ----------- ---------- ---- -------- ----------- ----------
AAAA aaaaaaaa 2022-6-13           14                                                                
AAAA aaaaaaaa 2022-6-13           20                                                                
BBBB bbbbbbbb 2022-6-18            3                                                                
BBBB bbbbbbbb 2022-6-19           11 BBBB bbbbbbbb 2022-6-19           10                           
BBBB bbbbbbbb 2022-6-20            5 BBBB bbbbbbbb 2022-6-20           16 BBBB bbbbbbbb 2022-6-20            4
                                     BBBB bbbbbbbb 2022-6-17           15                           
                                     AAAA aaaaaaaa 2022-6-18            7                           
                                     AAAA aaaaaaaa 2022-6-19            6 AAAA aaaaaaaa 2022-6-19            6
                                                                          BBBB bbbbbbbb 2022-6-14            4
                                                                          BBBB bbbbbbbb 2022-6-13           20
                                                                          AAAA aaaaaaaa 2022-6-17           19

11 rows selected