rownum 和 rowid

不同点：

rownum和rowid都是伪列，但是两者的根本是不同的；

rownum是根据sql查询出的结果给每行分配一个逻辑编号，所以你的sql不同也就会导致最终rownum不同；

rowid是物理结构上的，在每条记录insert到数据库中时，都会有一个唯一的物理记录 ；

例如  AAAMgzAAEAAAAAgAAB 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
这里的AAAMgzAAEAAAAAgAAB物理位置对应了这条记录，这个记录是不会随着sql的改变而改变。

因此，这就导致了他们的使用场景不同了，通常在sql分页时或是查找某一范围内的记录时，我们会使用rownum。

rownm 例子：

分页查询

sql> select * from

  2  (

  3  select a.*, rownum as rn from css_bl_view a

  4  where capture_phone_num = '(1) 925-4604800'

  5  ) b

  6  where b.rn between 6 and 10;

6 rows selected.

sql> select * from css_bl_view a

  2  where capture_phone_num = '(1) 925-4604800'

  3  and rownum <= 10

  4  minus

  5  select * from css_bl_view a

  6  where capture_phone_num = '(1) 925-4604800'

  7  and rownum <= 5

  8  ;

 
sql> select * from
  2  (
  3  select a.*, rownum as rn from css_bl_view a
  4  where capture_phone_num = '(1) 925-4604800'
  5  and rownum <= 10
  6  ) b
  7  where b.rn > 5;

难点：

rownum 与排序

   select rownum,sal from emp order by sal;

从这条语句的运行结果你可以发现，rownum不是从1 一次增大，而是乱的，实际上这些rownum是指每条记录未排序之前所处的行数，当然这不是我们想要的结果，那么如何得到rownum也是顺序排列的查询结果呢？这就需要先对原纪录排序，然后从新的顺序中提取出rownum和想要的记录内容。如：

1.select rownum,sal from (select * from emp order by sal) ;

2.select rownum, sal from (select * from emp order by sal ) where rownum <5;

3.select r, sal from (select rownum r,sal from (select * from emp order by sal ))

where r >5 and r < 10;

分析：首先对结果集进行排序，在对结果集排序的时候不进行rownum编号，查询完毕在对结果集进行编号。

rowid去重复用法：

基本去重复思路：

举例：
--建表tbl
SQL> create table stu(no number,name varchar2(10),sex char(2));
--添加测试记录
SQL> insert into stu values(1, 'ab',’男’);
SQL> insert into stu values(1, 'bb',’女’);
SQL> insert into stu values(1, 'ab',’男’);
SQL> insert into stu values(1, 'ab',’男’);
       SQL>commit;
 
删除重复记录方法很多，列出两种。
⑴ 通过创建临时表
可以把数据先导入到一个临时表中，然后删除原表的数据，再把数据导回原表，SQL语句如下：
SQL>create table stu_tmp as select distinct* from stu;
SQL>truncate table sut;                                                   //清空表记录
SQL>insert into stu select * from stu_tmp;                        //将临时表中的数据添加回原表
 
这种方法可以实现需求，但是很明显，对于一个千万级记录的表，这种方法很慢，在生产系统中，这会给系统带来很大的开销，不可行。

⑵ 利用rowid结合max或min函数

使用rowid快速唯一确定重复行结合max或min函数来实现删除重复行。

SQL>delete from stu a where rowid not in (select max(b.rowid) from stu b where a.no=b.no and a.name = b.name and a.sex = b.sex);                                          //这里max使用min也可以

或者用下面的语句

SQL>delete from stu a where rowid < (select max(b.rowid) from stu b where a.no=b.no and a.name = b.name and a.sex = b.sex);       //这里如果把max换成min的话，前面的where子句中需要把"<"改为">"

 

跟上面的方法思路基本是一样的，不过使用了group by，减少了显性的比较条件，提高效率。

SQL>delete from stu where rowid not in (select max(rowid) from stu t group by t.no, t.name, t.sex );

 

思考：若在stu表中唯一确定任意一行数据（1, 'ab',’男’），把sex字段更新为”女”，怎么做？

       SQL>update stu set sex=’女’ where rowid=(select min(rowid) from stu where no=1 and name=’ab’ and sex=’男’);