创建表：
Table1
id 流水号码,primary key,以sequence产生
name 姓名,字元 30
address 位址,字元 120
Table2
id 以table1的 id 为外键
category 分类(A,B,C类), 字元 1
friend 朋友,字元 50
并建立视图统计各個姓名各类朋友有多少？
不要使用group by .
------解决方案--------------------
使用开窗函数可以吧？
- SQL code
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_numfrom table1 a,table2 bwhere a.id=b.idorder by a.name name category c_num ---------------------------------1 李四 A 22 李四 B 43 张三 A 14 张三 B 25 张三 C 3