# 重复记录

--查询重复记录
---------------------------------------------------------------------
select rowid, col1, col2
from temp a
where rowid not in (select min (b.rowid)
from temp b
where a.col1 = b.col1 and a.col2 = b.col2);
---------------------------------------------------------------------
select rowid, col1, col2
from temp a
where rowid not in (  select min (b.rowid)
from temp b
group by col1, col2);
---------------------------------------------------------------------

--删除重复记录
---------------------------------------------------------------------
delete from temp a
where rowid not in (select min (b.rowid)
from temp b
where a.col1 = b.col1 and a.col2 = b.col2);
---------------------------------------------------------------------
delete from temp a
where rowid not in (  select min (b.rowid)
from temp b
group by col1, col2);
---------------------------------------------------------------------

--查询top(n .m)条记录
---------------------------------------------------------
select *
from (select a.*, rownum rn
from (  select *
from emp
order by empno) a
where rownum <= 10)
where rn >= 5;
---------------------------------------------------------
select *
from (  select emp.*, rownum rn
from emp
order by empno)
where rn >= 5 and rn <= 10;
---------------------------------------------------------

--查询选修了所有课程的学生
----------------------------------------------------------------------
select sname
from student
where not exists
(select *
from course
where not exists
(select *
from sc
where sno = student.sno and cno = course.cno));
----------------------------------------------------------------------
select sname
from student
where sno in (  select sno
from sc
group by sno
having count (*) = (select count (*) from course));
----------------------------------------------------------------------

先查询出student表的结果，然后将结果代入到student.sno然后再查询出c表中的结果，再一条一条的代入，有点像for的嵌套循环，第一轮外循环中，满足内部的not exists条件的course表中的结果集被保留，然后再判断外部的not exists，这次判断是根据内部Course表中被保留的结果集的情况，如果结果集为空，则输出当前这一条S表的结果集；然后再进行第二轮大的循环，即取出student表的第二条结果代入运算。

还可以这样理解，最内部的 select * from sc where cno = course.cno and sno=student.sno是查询出所有已经选择过课程的学生及相应课程，select * from course where   not exists 则是所有没有被选择的课程，在这个基础上的 select sname from student where  not exists 则是选取所有没有未选择课程的学生，即选择了所有课程的学员名称。

• 博文量
80
• 访问量
72873