首先来说一下rownum与rowid含义:
顾名思义rownum就是行数/行号,而rowid就是编码/编号/唯一识别号,所以他是类似“AAAR8gAAEAAAAErAAK”的编号,注意他是没有先后顺序的,也就是说他和数据入库时间没有任何关系,打个比方:他就像磁盘、内存存储数据用的是16进制的地址一样。
他们都是伪列,可以理解成表中的一个列只是他们并不是你创建的。同样是伪列区别是什么呢?
rowid是你录入数据时有数据库自动为这条记录添加的唯一的18位编号是一个物理编号用于找到这条记录(顺便说一句这也是为什么数据优调的时候强调尽量使用rowid的原因),他是不会随着查询而改变的 除非在表发生移动(比如表空间变化,数据导入/导出以后),才会发生变化。
rownum是根据sql查询后得到的结果自动加上去的,但是他却不受到sql中order by排序的影响,因为他和rowid的顺序一样是系统按照记录插入时的顺序给记录排的号(顺序的、无跳跃)。 但是如果你想让rownum和order by一样的顺序 那么可以使用子查询,形如:select rownum,t.* from (select * from 表空间名 order by 字段名) t 这样的话rownum就是根据该字段进行排序的编号了,为什么会这样呢,本人理解:rownum是根据表记录输出的行号,与筛选语句、排序语句都无关所以当用子查询时等于生成了一个表于是就按照这张表从1开始排序了。 同样,也可以用下面要提得到的分析函数中的row_number() over(order by 需要排序的字段名)。
下面说说分析函数row_number()、rank()、dense_rank()
ROW_NUMBER():
Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。 row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开时排序),因为row_number()是分析函数而rownum是伪列所以row_number()一定要over而rownum不能over。
RANK():
Rank函数返回一个唯一的值,除非遇到相同的数据,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)。
DENSE_RANK():
Dense_rank函数返回一个唯一的值,除非当碰到相同数据,此时所有相同数据的排名都是一样的。
dense_rank()是连续排序,有两个第二名时仍然跟着第三名。他和row_number的区别在于row_number是没有重复值的。
下面举个例子:
【1】测试环境:
SQL> desc user_order;
Name Null? Type
----------------------------------------- -------- ----------------------------
REGION_ID NUMBER(2)
CUSTOMER_ID NUMBER(2)
CUSTOMER_SALES NUMBER
【2】测试数据:
SQL> select * from user_order order by customer_sales;
REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ----------- --------------
5 1 151162
10 29 903383
6 7 971585
10 28 986964
9 21 1020541
9 22 1036146
8 16 1068467
6 8 1141638
5 3 1161286
5 5 1169926
8 19 1174421
7 12 1182275
7 11 1190421
6 10 1196748
6 9 1208959
10 30 1216858
5 2 1224992
9 24 1224992
9 23 1224992
8 18 1253840
7 15 1255591
7 13 1310434
10 27 1322747
8 20 1413722
6 6 1788836
10 26 1808949
5 4 1878275
7 14 1929774
8 17 1944281
9 25 2232703
30 rows selected.
【3】row_number()、rank()、dense_rank()这三个分析函数的区别实例
SQL> select region_id, customer_id, sum(customer_sales) total,
2 rank() over(
order by sum(customer_sales) desc) rank,
3 dense_rank() over(
order by sum(customer_sales) desc) dense_rank,
4 row_number() over(
order by sum(customer_sales) desc) row_number
5 from user_order
6 group by region_id, customer_id;
REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
8 18 1253840 11 11 11
5 2 1224992 12 12 12
9 23 1224992 12 12
13
9 24 1224992 12 12
14
10 30 1216858
15 13 15
30 rows selected.
请注意上面的绿色高亮部分,这里生动的演示了3种不同的排名策略:
①对于第一条相同的记录,3种函数的排名都是一样的:12
②当出现第二条相同的记录时,Rank和Dense_rank依然给出同样的排名12;而row_number则顺延递增为13,依次类推至第三条相同的记录
③当排名进行到下一条不同的记录时,可以看到Rank函数在12和15之间空出了13,14的排名,因为这2个排名实际上已经被第二、三条相同的记录占了。而Dense_rank则顺序递增。row_number函数也是顺序递增
比较上面3种不同的策略,我们在选择的时候就要根据客户的需求来定夺了:
①假如客户就只需要指定数目的记录,那么采用row_number是最简单的,但有漏掉的记录的危险
②假如客户需要所有达到排名水平的记录,那么采用rank或dense_rank是不错的选择。至于选择哪一种则看客户的需要,选择dense_rank或得到最大的记录。
本文转自:http://blog.csdn.net/tomatofly/article/details/5939045
分享到:
相关推荐
主要介绍了oracle中rownum和row_number()的使用方法以及区别和联系,十分的详细,有需要的小伙伴可以参考下。
在Oracle中,有一个很有趣的东西,那就是rownum。当你从某个表中查询数据的时候,返回的结果集中都会带有rownum这个字段,而且有时候也可以使用rownum进行一些条件查询
[sql] with query as (select ROW_NUMBER() over(order by (select 0)) AS ROWNUM, * FROM Product) select * from query where ROWNUM BETWEEN 5 AND 10 –2.ROW_NUMBER必须指写over (order by **),有时我根本就...
row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据... rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内) dense_rank()也是连续排序,有两个第二名时仍然跟
对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数,而且rownum不能以任何表的名称作为前缀。
oracle_SQL中 rowid 和rownum的使用
索引 经常要查询的语句,则给它建一个索引 表连接 select T_Oders as o join T_Customers as C on o.CustomerId = c.Id 子查询 单值子查询 单列多值子查询 ROW_NUMBER()函数 ROW_NUMBER() OVER(ORDER BY Fsalary ...
Oracle中ROWNUM的使用技巧 ROWNUM是一种伪列,它会根据返回记录生成一个序列化的数字。利用ROWNUM,我们可以生产一些原先难以实现的结果输出
主要介绍了Oracle中使用Rownum分页详细例子,本文将分别展示使用rownum伪列和row_number()分析函数来完成Oracle数据分页操作的具体使用方法,需要的朋友可以参考下
举例介绍在oracle中rownum和rowId的不同以及使用方法
Oracle分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法,需要的朋友可以参考下。
oracle_SQL中rowid与rownum的使用
Oracle学习笔记(rownum和rowid),有具体的代码案例讲解rownum和rowid
在前面一篇《Oracle开发之分析函数简介Over》,我们认识了分析函数的基本应用,现在我们再来考虑下面几个问题: ①对所有客户按订单总额进行排名 ②按区域和客户订单总额进行排名 ③找出订单总额排名前13位的客户 ④...
ORACLE 中ROWNUM用法总结 整理版本,详细明了
oracle_SQL中ROWID与ROWNUM的使用 很有用哦
Oracle中rownum的使用