ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 重复记录

重复记录

原创 Linux操作系统 作者:hjianping 时间:2011-04-26 19:41:21 0 删除 编辑

--查询重复记录
---------------------------------------------------------------------
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 则是选取所有没有未选择课程的学生,即选择了所有课程的学员名称。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17012874/viewspace-693795/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 分析函数
下一篇: 异常处理
请登录后发表评论 登录
全部评论

注册时间:2011-04-24

  • 博文量
    80
  • 访问量
    72873