id name subject
insert into teacher values(1,'张三','java');
insert into teacher values(2,'王五','C#');
insert into teacher values(3,'李四','java');
insert into teacher values(4,'张三','C#');
insert into teacher values(5,'李四','php');
insert into teacher values(6,'张三','php');
insert into teacher values(7,'李四','C#');
insert into teacher values(8,'张三','java');
insert into teacher values(9,'王五','java');
.............有约一万条数据,里面有很多重复的怎么查询所教课程三门以上的老师的姓名
create table teacher(id int,name varchar2(20),subject varchar2(20));
insert into teacher values(1,'张三','java');
insert into teacher values(2,'王五','C#');
insert into teacher values(3,'李四','java');
insert into teacher values(4,'张三','C#');
insert into teacher values(5,'李四','php');
insert into teacher values(6,'张三','php');
insert into teacher values(7,'李四','C#');
insert into teacher values(8,'张三','java');
insert into teacher values(9,'王五','java');
insert into teacher values(10,'李四','oracle');--根据姓名分组
select name from teacher group by name having count(distinct subject) > 3
NAME
--------------------
李四
select a.*,row_number(partition by name order by 1) rn from teacher a
) where rn >=3
select name from techer group by name having count(subject)>3
同意一楼
from teacher group by name) a where a.total > 3以L楼的测试数据,所查询出来的结果是
----------------------------------------------
李四
张三
----------------------------------------------
测试没有问题
from teacher group by name) a where a.total > 3
这个也是正确的。
-----------
李四
-----------
没看到前面说有重复。是掉了点东东.