刚才发贴,把题目搞错了.现在重发.
有两 Table Table 1 :
A B
1 a
1 b
1 c
1 d Table 2 :
A C
1 e
1 f
1 g 得到结果
A B C
1 a e
1 b f
1 c g
1 d 空------------------------------------
或者:Table 1 :
A B
1 a
1 b
1 c
Table 2 :
A C
1 e
1 f
1 g
1 h得到结果
A B C
1 a e
1 b f
1 c g
1 空 h
有两 Table Table 1 :
A B
1 a
1 b
1 c
1 d Table 2 :
A C
1 e
1 f
1 g 得到结果
A B C
1 a e
1 b f
1 c g
1 d 空------------------------------------
或者:Table 1 :
A B
1 a
1 b
1 c
Table 2 :
A C
1 e
1 f
1 g
1 h得到结果
A B C
1 a e
1 b f
1 c g
1 空 h
from (select a,b,
row_number() over(partition by a order by b) rn
from tb1) t1 full outer join
(select a,c,
row_number() over(partition by a order by c) rn
from tb2) t2
on t1.a=t2.a and t1.rn=t2.rn
(select t.* , row_number() over(partition by a order by b) px from table1 t) m
full join
(select t.* , row_number() over(partition by a order by c) px from table2 t) n
on m.a = n.a and m.px = n.px
如果字段B,C有序,则可以排序后产生序号并根据序号连接:
SELECT nvl(t1.a, t2.a) a, t1.b, t2.c
FROM (SELECT a, b, rownum rn FROM (SELECT a, b FROM table1 ORDER BY b)) t1
FULL OUTER JOIN (SELECT a, c, rownum rn FROM (SELECT a, c FROM table2 ORDER BY c)) t2
ON (t1.a = t2.a AND t1.rn = t2.rn)
(
SELECT a,b,row_number()OVER(PARTITION BY a order BY a) ID
FROM table1 a) tmp1,
(SELECT a,c,row_number()OVER(PARTITION BY a order BY a) ID
FROM table2 b) tmp2
WHERE tmp1.ID =tmp2.ID(+)
SELECT nvl(t1.a, t2.a) a, t1.b, t2.c FROM
(SELECT a, b, rownum rn FROM (SELECT a, b FROM tab1 ORDER BY b)) t1
FULL OUTER JOIN
(SELECT a, c, rownum rn FROM (SELECT a, c FROM tab2 ORDER BY c)) t2
ON (t1.a = t2.a AND t1.rn = t2.rn)
--应该可以不对B和C排序
--直接这样:
SELECT nvl(t1.a, t2.a) a, t1.b, t2.c FROM
(SELECT a, b, rownum rn FROM tab1 ) t1
FULL OUTER JOIN
(SELECT a, c, rownum rn FROM tab2) t2
ON (t1.a = t2.a AND t1.rn = t2.rn)
2 FROM (SELECT a, b, rownum rn FROM (SELECT city_id a, city_name b FROM city ORDER BY b)) t1
3 FULL OUTER JOIN
4 (SELECT a, c, rownum rn FROM (SELECT city_id a, short_name c FROM city_short_name ORDER BY c)) t2
5 ON (t1.a = t2.a AND t1.rn = t2.rn)
6 /A B C
-------- ---------- --------
03 广州 c
04
01 北京
02 上海 SQL>
SQL> SELECT nvl(t1.a, t2.a) a, t1.b, t2.c FROM
2 (SELECT a, b, rownum rn FROM (SELECT city_id a, city_name b FROM city)) t1
3 FULL OUTER JOIN
4 (SELECT a, c, rownum rn FROM (SELECT city_id a, short_name c FROM city_short_name)) t2
5 ON (t1.a = t2.a AND t1.rn = t2.rn)
6 /A B C
-------- ---------- --------
03 广州
01 北京
04
02 上海
两条结果不一样,也和楼主的要求不符合啊,两个表的原始数据如下:SQL> select * from city;CITY_ID CITY_NAME YEAR
-------- ---------- -----
02 上海 1949
03 广州 1949
04 1949
01 北京 1949SQL> select * from city_short_name;CITY_ID SHORT_NAME
-------- ----------
01 a
02 b
03 c
下面的结果才是对的,SQL> SELECT nvl(t1.a, t2.a) a, t1.b, t2.c
2 FROM (SELECT a, b, rownum rn FROM (SELECT city_id a, city_name b FROM city ORDER BY a)) t1
3 FULL OUTER JOIN
4 (SELECT a, c, rownum rn FROM (SELECT city_id a, short_name c FROM city_short_name ORDER BY a)) t2
5 ON (t1.a = t2.a AND t1.rn = t2.rn)
6 /A B C
-------- ---------- --------
01 北京 a
02 上海 b
03 广州 c
04
分析了一下,应该是对需求的理解不同造成的差别,
楼主给的实例数据是:field C ——》 field B
1 a e
1 b f
1 c g
1 d 空而本人用的测试数据用的和六楼想的是一样的
field B——》 field A, field C——》 field A,不知道上面的意思有没有明白, 对需求的准确理解很重要。