当前位置 博文首页 > 执剑问道的博客：Oracle中求三门学科都一样的同学姓名。where...
有学科表，想求出三门学科都一样的学生姓名；
create table user2(name varchar2(10),course varchar2(12),score number );
insert into user2 values('xxx','语文','70');
insert into user2 values('yyy','数学','80');
insert into user2 values('xxx','数学','75');
insert into user2 values('yyy','语文','70');
insert into user2 values('xxx','英语','80');
insert into user2 values('yyy','英语','75');
insert into user2 values('ggg','数学','80');
insert into user2 values('ggg','英语','75');
结果如下所示；三人中，有两个符合条件，需要求出他们的名字
第一步：这里可以用到where…or…，但这样有个问题，就是会把只有两门的也查出来。
select name, course
from user2
where course = '数学'
or course = '语文'
or course = '英语'
group by name, course
结果如图：
第二部：我们用count（）over（）对学科course进行统计：
select s.name, s.course, count(course) over(partition by name) c
from (select name, course
from user2
where course = '数学'
or course = '语文'
or course = '英语'
group by name, course) s
结果如图所示：
第三步：最后得出结果：
select *
from (select s.name, s.course, count(course) over(partition by name) c
from (select name, course
from user2
where course = '数学'
or course = '语文'
or course = '英语'
group by name, course) s) s1
where s1.c = 3;
如图所示：
