将两个甲骨文表合并为一个

Question

我有两张甲骨文桌

第一

itmnum  qty1
1       10
2       5
3       7
5       9

second 
itmnum  qty2
2       3
3       9
4       12

需要这样的输出

itmnum qty1  qty2
1      10
2      5     3
3      7     9
4            12
5      5

谢谢

Answer 1

您想要的是一个完全的外部连接：

with
  first_table (itmnum, qty1) as
    ( select 1, 10 from dual union all
      select 2, 5  from dual union all
      select 3, 7  from dual union all
      select 5, 9  from dual) 
, second_table (itmnum, qty2) as
    ( select 2, 3  from dual union all
      select 3, 9  from dual union all
      select 4, 12 from dual )
-- start here
select coalesce(f.itmnum,s.itmnum) as itmnum
     , f.qty1, s.qty2
from   first_table f
       full outer join second_table s
            on  s.itmnum = f.itmnum
order by 1;

    ITMNUM       QTY1       QTY2
---------- ---------- ----------
         1         10 
         2          5          3
         3          7          9
         4                    12
         5          9 

(我在这里违反了自己的规则，包括了可选的outer关键字，我通常跳过这个关键字，用于内部和外部联接，但是完全联接是非常罕见的，因此我认为如果没有它，它可能会更加混乱。)

Answer 2

还有一种选择：

SQL> create table test1 (
2  itmnum number(10),
3  qty1 number(10));
4  create table test2 (
5  itmnum number(10),
6  qty2 number(10));
7  insert into test1 (itmnum, qty1) values (1,10);
8  insert into test1 (itmnum, qty1) values (2,5);
9  insert into test1 (itmnum, qty1) values (3,7);
10 insert into test1 (itmnum, qty1) values (5,9);
11 insert into test2 (itmnum, qty2) values (2,3);
12 insert into test2 (itmnum, qty2) values (3,9);
13 insert into test2 (itmnum, qty2) values (4,12);

SQL>select t1.itmnum, t1.qty1, t2.qty2
1  from test1 t1, test2 t2
2  where t1.itmnum = t2.itmnum(+)
3  union all
4  select t2.itmnum, t1.qty1, t2.qty2
5  from test1 t1, test2 t2
6  where t1.itmnum(+) = t2.itmnum
7  and   t1.itmnum is null
8  order by itmnum

    ITMNUM       QTY1       QTY2
---------- ---------- ----------
         1         10
         2          5          3
         3          7          9
         4                    12
         5          9
SQL>         

Answer 3

这里有一个选择：

• first和second是当前的表
• all_nums从两个表中选择所有不同的itmnums。
• 使用first和second与all_nums的外部连接，您将得到结果。

。

SQL> with
  2    first (itmnum, qty1) as
  3      (select 1, 10 from dual union all
  4       select 2, 5 from dual union all
  5       select 3, 7 from dual union all
  6       select 5, 9 from dual),
  7    second (itmnum, qty2) as
  8      (select 2, 3 from dual union all
  9       select 3, 9 from dual union all
 10       select 4, 12 from dual),
 11  -- start here
 12    all_nums as
 13      (select itmnum from first union
 14       select itmnum from second
 15      )
 16  select a.itmnum, f.qty1, s.qty2
 17  from all_nums a left join first f on f.itmnum = a.itmnum
 18                  left join second s on s.itmnum = a.itmnum
 19  order by a.itmnum;

    ITMNUM       QTY1       QTY2
---------- ---------- ----------
         1         10
         2          5          3
         3          7          9
         4                    12
         5          9

SQL>