create table table1
  (id int primary key,
  name varchar2(30),
  address varchar2(120));
    
  create table table2
  (id int,
  category varchar2(1),
  friend varchar2(50)
  );
    
  insert into table1 values (1,'张三','北京');
  insert into table1 values (2,'李四','上海');
    
  insert into table2 values (1,'A','张三');
  insert into table2 values (1,'B','李四');
  insert into table2 values (1,'B','王五');    
  insert into table2 values (1,'C','安安');   
  insert into table2 values (1,'C','窝窝'); 
  insert into table2 values (1,'C','大大');  
  insert into table2 values (2,'A','豆豆');
  insert into table2 values (2,'A','丢丢');
  insert into table2 values (2,'B','天天');   
  insert into table2 values (2,'B','乖乖');   
  insert into table2 values (2,'B','刚刚');  
  insert into table2 values (2,'B','弟弟');  
    
select distinct a.name,b.category,count(category) over(partition by b.id,b.category) c_num
from table1 a,table2 b
where a.id=b.id
order by a.name


    name  category  c_num        
---------------------------------
1    李四    A    2
2    李四    B    4
3    张三    A    1
4    张三    B    2
5    张三    C    3