创建表:
Table1
id 流水号码,primary key,以sequence产生
name 姓名,字元 30
address 位址,字元 120 Table2
id 以table1的 id 为外键
category 分类(A,B,C类), 字元 1
friend 朋友,字元 50
并建立视图统计各個姓名各类朋友有多少?
不要使用group by .
Table1
id 流水号码,primary key,以sequence产生
name 姓名,字元 30
address 位址,字元 120 Table2
id 以table1的 id 为外键
category 分类(A,B,C类), 字元 1
friend 朋友,字元 50
并建立视图统计各個姓名各类朋友有多少?
不要使用group by .
那就需要写函数了
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,'C','王五六');
insert into table2 values (2,'A','朱毛');
insert into table2 values (2,'B','朱毛A');
create or replace function mycount(v_id in int,v_category in varchar2) return int is
Result int;
begin
if v_category is null then
select count(*) into result from table2 where id=v_id;
else
select count(*) into result from table2 where id=v_id and CATEGORY=v_category;
end if;
return (result);
end;
select id,mycount(id,null),mycount(id,'A') from table1;结果:
ID MYCOUNT(ID,NULL) MYCOUNT(ID,'A')
1 1 3 1
2 2 2 1
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
select t1.name,t2.category,count(friend) over(partition by t1.name,t2.category)
from Table1 t1,Table2 t2
where t1.id = t2.id