【Oracle】【SQL】rownum, row_number() 和 rank()的区别

已知表结构如下：

SC表

sno	cno	score
s001	c001	80
s001	c002	90
s002	c001	79
s002	c002	85
s003	c001	79
s003	c002	80

 

1. ROWNUM

1.1 rownum 是一个序列，是oracle数据库从数据文件或缓存中读取数据的顺序， 如：

select * from sc where rownum = 1

结果返回SC表的第一行数据

sno	cno	score
s001	c001	80

 

1.2 select rownum是在查询结果中插入“伪列" 用于显示查询结果的序列，如：

select rownum, cno, score from sc where sno = 's001'

返回结果为：

ronum	cno	score
1	c001	80
2	c002	90

 

1.3 当rownum和order by 在同一个SQL出现时，数据库将先对select结果插入rownum伪列在进行排序，如：

select rownum, cno, score from sc where sno = 's001' order by score DESC

返回结果为：

rownum	cno	score
2	c002	90
1	c001	80

 

1.4 在where语句中使用rownum时，不能使用> 或 between，如：

select * from sc where rownum > 2;

select * from sc where rownum between 1 and 1;

以上语句均返回空值

 

 

2. ROW_NUMBER()

2.1 跟rownum 类似，row_number() 也是在查询结果中插入一个伪列，用以显示每一行的序列，但是row_number()不可单独使用，一般结合over(<window specification>)一起使用

如：

检索SC表中每科成绩前2名的同学：

select * from

(select sno, cno, score, row_number()over(partition by cno order by score DESC) rn

from sc)

where rn between 1 and 2

返回结果为：

sno	cno	score	RN
s001	c001	80	1
s002	c001	79	2
s001	c002	90	1
s002	c002	85	2

在上述语句中 row_number()over(partition by cno order by score DESC) 的含义是：

partition by cno: 以column cno 进行分组

order by score DESC: 在分组完成的基础上，将每一组中的数据以column score 进行降序排列

row_number(): 在分组和排序完成的基础上，在结果表中插入一行伪劣，用以显示每一组的行序列。

 

 

3. RANK()

3.1 rank()跟row_number()的用法类似，通常跟over(partition by column A order by column B) 一起使用，区别是，row_number()返回结果不考虑列值相同的情况，而rank()返回列值相同的所有数据

如：

select * from

(select sno, cno, score, rank()over(partition by cno order by score DESC) rn

from sc)

where rn between 1 and 2

返回结果为：

sno	cno	score	RN
s001	c001	80	1
s002	c001	79	2
s003	c001	79	2
s001	c002	90	1
s002	c002	85	2

使用row_number()时RN的值按1,2,3,4 顺序升序排列，不会重复

使用rank()时，对于值相同的行RN的值也相同，比如四行数据的第二行和第三行相同，那么RN的值是：1,2,2,4

 

<完>

<欢迎纠错/补充>