不同点:
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=’男’);