请问下面的例子用语句怎么实现啊？
A B C
a aa a aa a aa 2
a ab a aa a ab 0
a ac a bb ==> a ac 1
a ad a ac a ad 0
a cc a bb 1
a cc 1
在A和B都有的累加一次
只在B里面有的计算一次
A有B没有的计零次
[解决办法]
左连接后group by
[解决办法]
应该是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 aaa aba aca ad SQL> select * from t_b; COL1 COL2---------- ----------a aaa aaa bba aca 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 1a ab 0a ad 0a cc 1a aa 2a bb 1 6 rows selected SQL>
