日期:2014-05-17 浏览次数:20983 次
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>