具有联接和计数功能的Oracle SQL

ama989

我想使用计数功能将两个表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
贾尔·埃尔南德斯(Jair Hernandez)

可能是这样的:

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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章