日期:2014-05-17  浏览次数:20817 次

关于外连接的简单问题
表A 表B
ID ANUM ID BNUM
1 10 1 10
2 10 2 10
3 10 4 10
希望得出结果
ID ANUM BNUM
1 10 10
2 10 10
3 10
4 10

用语句 SELECT a.id, anum, bnum from a full outer join b on a.id= b.id
得出的结果是 
1 10 10
2 10 10
3 10
  10

这个问题应该怎么修正? 急 谢谢指教

------解决方案--------------------
try it ..

id(a)->aid, id(b)->bid

SQL code


SQL:
select tt.sid,
       max(tt.anum) as anum,
       max(tt.bnum) as bnum
  from ( 
       select a.aid as sid,
              a.anum,
              null as bnum
         from A
        union 
       select b.bid as sid,
              null as anum,
              b.bnum
         from B
        )tt
 group by tt.sid;


RESULT:

       SID       ANUM       BNUM
---------- ---------- ----------
         1         10         10
         2         10         10
         3         10 
         4                    10