现在有两个表我需要取出两个表所有数据,理想的效果如下:
A.ID A.NAME B.ID B.NAME
001 LI 001 LI
001 WANG
001 ZHANG
但是我用全联后出现的效果是
A.ID A.NAME B.ID B.NAME
001 LI 001 LI
001 LI 001 WANG
001 LI 001 ZHANG
sql是
SELECT A.ID,A.NAME,B.ID,B.NAME FROM A FULL OUTER JOIN B ON A.ID = B.ID
哪位仁兄帮忙解决下,急!!!!!!!!!!!!
A.ID A.NAME B.ID B.NAME
001 LI 001 LI
001 WANG
001 ZHANG
但是我用全联后出现的效果是
A.ID A.NAME B.ID B.NAME
001 LI 001 LI
001 LI 001 WANG
001 LI 001 ZHANG
sql是
SELECT A.ID,A.NAME,B.ID,B.NAME FROM A FULL OUTER JOIN B ON A.ID = B.ID
哪位仁兄帮忙解决下,急!!!!!!!!!!!!
SELECT DECODE(R, 1, AID, ''), DECODE(R, 1, ANAME, ''), BID, BNAME
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY AID, ANAME) R,
AID,
ANAME,
BID,
BNAME
FROM (SELECT A.ID AID, A.NAME ANAME, B.ID BID, B.NAME BNAME
FROM A
FULL OUTER JOIN B ON A.ID = B.ID));
SQL> select * from tabcopy1; ID NAME
---------- --------
1 赵二SQL> select * from tabcopy4; ID NAME
---------- --------
1 张三
2 李四
3 王五SQL> select a.*,b.* from tabcopy1 a,tabcopy4 b; ID NAME ID NAME
---------- -------- ---------- --------
1 赵二 1 张三
1 赵二 2 李四
1 赵二 3 王五SQL>