oracle查询语句选择,ORACLE查询语句

SQL codecreate 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