Oracle中rownum的用法总结

刺骨的言语ヽ痛彻心扉 2021-09-24 11:26 576阅读 0赞

数据库查询中，常用到"选取前X个"这样的问题，Oracle没有TOP关键字，这类问题都是通过rownum选取某几行来完成的。

# 先说结论 #

rownum不支持>, >=, =, !=, between...and...这几个运算符，只能用符号(<、<=)

# 例子1：选取列表的前三行 #

![format_png][]

# 例子2：选取列表第10行及以后的记录 #

第一次学Oracle，可能都会这么写：

（错误解法warning）

![format_png 1][]

![format_png 2][]

原因：ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类推。如果你用>,>=,=,between...and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除， 接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。

（总的来说，查询就是逐条筛选，这条不行下一条来上）

**（正确解法Tips）**

**先把rownum的列与table"拼接"作为一个子表来查询，这样rownum所在的列就是代表一个属性（对属性筛选当然是没问题的）**

![format_png 3][]

注意下图红框的部分。、

![format_png 4][]

# 例子3：选取列表中某个值前3的所有记录 #

例如：

*  选取薪水SAL前三的人
 *  选取成绩倒数前三的人

Key Points：

*  **前3不代表只是三个，因为有可能存在并列第一/第二**

**解决思路：先排序（根据实际决定升序还是降序），再选取前3行**

例如：选取SAL前3的SNAME

先选出SAL前3的三个数值：

（刚开始学可能会这么写，**错误warning：这种写法是先选列表的1，2，3行再排序**）

![format_png 5][]

（你可能会想：嵌套查询，把排序后的结果作为查询目标，错误warning：最值5000多个并列）

![20200622143042391.png][]

正确解法：

*  先排序去重
    
    select distinct sal from emp order by sal desc;
 *  再选出前3的sal
    
    ![format_png 6][]
 *  再从所有的记录中筛选sal在上述集合\{5000，3000，2975\}中的记录
    
    select \[col1, col2\] from emp where sal in \[set as above\]
    
    ![format_png 7][]

# 例子4：选取区间\[a,b\]的记录 #

上述解决了"前n个"即1<=rownum<=n这一类问题，但是并没有解决a<=rownum<=b这样更为通用的问题。但其实稍加改变就能够解决这个问题。

**回想一下例子2的操作：把行号rownum"拼接"到一个表，让rownum变为表的一个属性。**

**（截图不完全）**

![format_png 8][]

然后对R属性筛选

![format_png 9][]

现在来搞点更复杂的：**找出薪水排名在2到4的(R, ENAME, SAL)，注意考虑排名并列的问题**

*  sal排名（2-4：3000，2975，2850）
    
    ![format_png 10][]
 *  先添加几个重复排名的（红框就是我们的筛选目标）
    
    ![format_png 11][]

解决步骤：

*  先找出集合\{3000，2975，2850\}
    
    ![format_png 12][]
 *  再select \[col1,...\] from emp where sal in <set as above>
    
    ![format_png 13][]
    
    到这一步，筛选区间\[a,b\]的记录计算完成了。红框中即是修改\[a,b\]的地方。

[format_png]: /images/20210923/93eb5dd8e0e6486cacddb865ca257273.png
[format_png 1]: /images/20210923/4ec46e51b3bf417fbbdf468f578665ef.png
[format_png 2]: /images/20210923/28d310a208b9478a8c7594b38188385f.png
[format_png 3]: /images/20210923/e25d6ed68f8b4385ad10abf805bb8b9e.png
[format_png 4]: /images/20210923/b4f39af8bb304c578e2658aea8e7932f.png
[format_png 5]: /images/20210923/97e2aa5635d2481d8ec34ac21c39342e.png
[20200622143042391.png]: /images/20210923/821c3f2b91544831aa4e353ab21b8f68.png
[format_png 6]: /images/20210923/b90ff3941cb84ba68eba583570e44562.png
[format_png 7]: /images/20210923/486d5c7c55964746908f514d776533fc.png
[format_png 8]: /images/20210923/009be355eb5b46b8a6d871e16e6481d3.png
[format_png 9]: /images/20210923/ab135bb55b79475f95aa2baf25a2f718.png
[format_png 10]: /images/20210923/e9152ba1b03947f2b372a2297666f5b0.png
[format_png 11]: /images/20210923/12443b234af8438498799be7b7b8f68c.png
[format_png 12]: /images/20210923/fd47d71325c34468b4869bafb56145a5.png
[format_png 13]: /images/20210923/eee29a3889bb465890254eba440a9e70.png