请问下面的例子用语句怎么实现啊？

Question

本帖最后由 tigar_chen 于 2011-06-15 17:23:47 编辑

Answer 1

应该是full outer join.
SQL> create table t_a
  2  (col1 varchar(10),
  3   col2 varchar(10));
 
Table created
 
SQL> 
SQL>  create table t_b
  2  (col1 varchar(10),
  3   col2 varchar(10));
 
Table created
 
SQL> select * from t_a;
 
COL1       COL2
---------- ----------
a          aa
a          ab
a          ac
a          ad
 
SQL> select * from t_b;
 
COL1       COL2
---------- ----------
a          aa
a          aa
a          bb
a          ac
a          cc
 
SQL> 
SQL> select decode(a.col1, null, b.col1, a.col1),
  2         decode(a.col2, null, b.col2, a.col2),
  3         count(b.col1)
  4    from t_a a
  5    full join t_b b on a.col1 = b.col1
  6                   and a.col2 = b.col2
  7   group by decode(a.col1, null, b.col1, a.col1),
  8            decode(a.col2, null, b.col2, a.col2);
 
DECODE(A.COL1,NULL,B.COL1,A.CO DECODE(A.COL2,NULL,B.COL2,A.CO COUNT(B.COL1)
------------------------------ ------------------------------ -------------
a                              ac                                         1
a                              ab                                         0
a                              ad                                         0
a                              cc                                         1
a                              aa                                         2
a                              bb                                         1
 
6 rows selected
 
SQL>