我想使用计数功能将两个表A和B连接起来。
表A具有以下内容:
SQL> select a.book_id, count(a.book_id)
from
a
group by a.book_id ;
BOOK_ID COUNT(A.BOOK_ID)
--------- ----------------
1 2
2 2
3 2
4 2
5 2
6 3
表B具有以下内容:
SQL> select b.book_id, count(b.book_id)
from
b
group by b.book_id ;
BOOK_ID COUNT(B.BOOK_ID)
--------- ----------------
6 2
所以我想有一个查询,给我以下结果:
BOOK_ID COUNT(A.BOOK_ID) COUNT(B.BOOK_ID)
--------- ---------------- ----------------
1 2 0
2 2 0
3 2 0
4 2 0
5 2 0
6 3 2
我尝试了这个:
SQL> select b.book_id, count(b.book_id),a.book_id, count(a.book_id)
from
b , a
where
b.book_id(+) = a.book_id
group by b.book_id, a.book_id ;
但是结果是这样的:
BOOK_ID COUNT(B.BOOK_ID) BOOK_ID COUNT(A.BOOK_ID)
--------- ---------------- --------- ----------------
0 1 2
0 2 2
0 3 2
0 4 2
0 5 2
6 6 6 6
可能是这样的:
select a.book_id as id_a,
(select count(1) from a a2 where a2.book_id = a.book_id) as count_a,
b.book_id as id_b,
(select count(1) from b b2 where b2.book_id = a.book_id) as count_b
from a
left join b on b.book_id = a.book_id
group by a.book_id;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句