SQL 求助（高手进来看看）

[woaixueyu]

表A
1,null, 3
2,null,4

表B

1,3,null
3,4,null

2个表 列名相同 ，问如何合成如何下 数据


1,3,3
2,null,4
3,4, null

[fxianxian]

select a.id,nvl(a.id1,b.id1) id1, nvl(a.id2,b.id2) id2
from a full join b
on (a.id=b.id)

SQL> with a as (select 1 id,'' id1,3 id2 from dual
  2             union
  3             select 2 id,'' id1,4 id2 from dual
  4             ),
  5       b as (select 1 id,3 id1,'' id2 from dual
  6             union
  7             select 3 id,4 id1,'' id2 from dual
  8             )
  9  select nvl(a.id,b.id) id,nvl(a.id1,b.id1) id1, nvl(a.id2,b.id2) id2
 10  from a full join b
 11  on (a.id=b.id)
 12  /
 
        ID ID1                                             ID2
---------- ---------------------------------------- ----------
         1 3                                                 3
         2                                                   4
         3 4                                        
 
SQL> 

[fxianxian]

主要用full join就可以实现了

[心中的彩虹]

--full 跟union
SQL> edi
已写入 file afiedt.buf

  1    select nvl(a.id1,b.id1),nvl(a.id2,b.id2) id2,nvl(a.id3,b.id3) id3
  2*   from a full join b on(a.id1=b.id1)
SQL> /

NVL(A.ID1,B.ID1)        ID2        ID3
---------------- ---------- ----------
               1          3          3
               2                     4
               3          4

SQL>   select a.id1,nvl(a.id2,b.id2) id2,nvl(a.id3,b.id3) id3
  2    from a left join b on(a.id1=b.id1)
  3    union 
  4    select b.id1,nvl(b.id2,a.id2) id2,nvl(b.id3,a.id3) id3
  5    from b left join a on(a.id1=b.id1)
  6  /

       ID1        ID2        ID3
---------- ---------- ----------
         1          3          3
         2                     4
         3          4

[gelyon]

select nvl(a.id1,b.id1),nvl(a.id2,b.id2) id2,nvl(a.id3,b.id3) id3
from a full join b on(a.id1=b.id1)

[vber1010]

select a.col1,b.col2,a.col3 from a,b where a.col1=b.col1(+)
union 
select b.col1,b.col2,a.col3 from a,b where a.col1(+)=b.col1

[xdy3008]

with a as 
(
  select 1 id,'' id1,3 id2 from dual
  union
  select 2 id,'' id1,4 id2 from dual
),
b as (select 1 id,3 id1,'' id2 from dual
  union
  select 3 id,4 id1,'' id2 from dual
)
  
select a.id as id, nvl(a.id1, b.id1) as id1, nvl(a.id2, b.id2) as id2
  from a
  full join b on (a.id = b.id)

还有啊， 你有没有这种可能啊，就是A B表中同一记录，ID1值或者ID2值都不为空， 哪你想取谁的为准呢 ？
表A
1,null, 3
2,null,4
3,7,4

表B

1,3,null
3,4,null

[lonenan]

[Quote=引用 1 楼 fxianxian 的回复:]
select a.id,nvl(a.id1,b.id1) id1, nvl(a.id2,b.id2) id2
from a full join b
on (a.id=b.id)


SQL code
SQL> with a as (select 1 id,'' id1,3 id2 from dual
2 union
3 sel……
[/Quote]顶

[47522341]

with a as (select 1 id,'' id1,3 id2 from dual
union
select 2 id,'' id1,4 id2 from dual
),
b as (select 1 id,3 id1,'' id2 from dual
union
select 3 id,4 id1,'' id2 from dual
)
select id,max(id1),max(id2)
from (
select id,id1,id2 from a
union
select id,id1,id2 from b
)
group by id