有数据表book_1,内容如下
id address age[3个字段]1 aa 1
2 bb 2
3 cc 3
book_2内容如下
id name gender[3个字段]3 nn 1
4 kk 1
5 ll 2
要求2表相连最后形成id address age name gender
1 aa 1
2 bb 2
3 cc 3 nn 1
4 kk 1
5 ll 2
我用的是
select *
from book_1 t left join book_2 m on t.id=m.id
union
select *
from book_1 t right join book_2 m on t.id=m.id
但是结果不对,数据有重复而且还错位了,请指正错误……
id address age[3个字段]1 aa 1
2 bb 2
3 cc 3
book_2内容如下
id name gender[3个字段]3 nn 1
4 kk 1
5 ll 2
要求2表相连最后形成id address age name gender
1 aa 1
2 bb 2
3 cc 3 nn 1
4 kk 1
5 ll 2
我用的是
select *
from book_1 t left join book_2 m on t.id=m.id
union
select *
from book_1 t right join book_2 m on t.id=m.id
但是结果不对,数据有重复而且还错位了,请指正错误……
select t.*,m.*
from book_1 t full join book_2 m
on t.id=m.id
SELECT 1 id, 'aa' address, 1 age FROM dual UNION ALL
SELECT 2, 'bb', 2 FROM dual UNION ALL
SELECT 3, 'cc', 3 FROM dual
),
book_2 AS(
SELECT 3 id, 'nn' NAME, 1 gender FROM dual UNION ALL
SELECT 4, 'kk', 1 FROM dual UNION ALL
SELECT 5, 'll', 2 FROM dual
)
select Nvl(t.id,m.id) id,t.address,t.age,m.NAME,m.gender
from book_1 t full join book_2 m on t.id=m.id
ORDER BY 1--结果:
ID ADDRESS AGE NAME GENDER
--------------------------------
1 aa 1
2 bb 2
3 cc 3 nn 1
4 kk 1
5 ll 2
-------try it good luck!
SQL>
SQL> with book_1 as(
2 select 1 id, 'aa' address, 1 age from dual union all
3 select 2 id, 'bb' address, 2 age from dual union all
4 select 3 id, 'cc' address, 3 age from dual
5 ),book_2 as
6 (
7 select 3 id, 'nn' name, 1 gender from dual union all
8 select 4, 'kk', 1 gender from dual union all
9 select 5, 'll', 2 gender from dual
10 )
11 select a.*, b.name, b.gender
12 from book_1 a, book_2 b
13 where a.id = b.id(+)
14 union
15 select b.id, a.address, a.age, b.name, b.gender
16 from book_1 a, book_2 b
17 where a.id(+) = b.id
18 / ID ADDRESS AGE NAME GENDER
---------- ------- ---------- ---- ----------
1 aa 1
2 bb 2
3 cc 3 nn 1
4 kk 1
5 ll 2SQL>
---------- ----- ----------
1 aa 1
2 bb 2
3 cc 3SQL> select * from t2; ID NAME GENDER
---------- ---------------- ----------
3 nn 1
4 kk 1
5 ll 2SQL> SELECT nvl(t1.id,t2.ID) ID,t1.address,t1.age,t2.name,t2.gender FROM t1 FULL
JOIN t2 ON(t1.id=t2.id)
2 ORDER BY ID; ID ADDRE AGE NAME GENDER
---------- ----- ---------- ---------------- ----------
1 aa 1
2 bb 2
3 cc 3 nn 1
4 kk 1
5 ll 2SQL>
另外,这数据结构的设计,有点别扭。两表其实可以合并,就不用这样费劲了。
decode(t.address,null,null,m.name) name,decode(t.age,null,null,m.gender) gender
from book_1 t full outer join book_2 m
on t.id=m.id
2 select 1 id, 'aa' address, 1 age from dual union all
3 select 2 id, 'bb' address, 2 age from dual union all
4 select 3 id, 'cc' address, 3 age from dual
5 ),book_2 as
6 (
7 select 3 id, 'nn' name, 1 gender from dual union all
8 select 4, 'kk', 1 gender from dual union all
9 select 5, 'll', 2 gender from dual
10 )
11 select nvl(t.id,m.id) id,nvl(t.address,m.name) address,nvl(t.age,m.gender) age,
12 decode(t.address,null,null,m.name) name,decode(t.age,null,null,m.gender) gender
13 from book_1 t full outer join book_2 m
14 on t.id=m.id
15 order by nvl(t.id,m.id);
ID ADDRESS AGE NAME GENDER
---------- ------- ---------- ---- ----------------------------------------
1 aa 1
2 bb 2
3 cc 3 nn 1
4 kk 1
5 ll 2
SQL>