Oracle中rownum的用法总结
数据库查询中,常用到"选取前X个"这样的问题,Oracle没有TOP关键字,这类问题都是通过rownum选取某几行来完成的。
先说结论
rownum不支持>, >=, =, !=, between...and...这几个运算符,只能用符号(<、<=)
例子1:选取列表的前三行
例子2:选取列表第10行及以后的记录
第一次学Oracle,可能都会这么写:
(错误解法warning)
原因:ROWNUM是一个序列,是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1,第二条为2,依次类推。如果你用>,>=,=,between...and这些条件,因为从缓冲区或数据文件中得到的第一条记录的rownum为1,则被删除, 接着取下条,可是它的rownum还是1,又被删除,依次类推,便没有了数据。
(总的来说,查询就是逐条筛选,这条不行下一条来上)
(正确解法Tips)
先把rownum的列与table"拼接"作为一个子表来查询,这样rownum所在的列就是代表一个属性(对属性筛选当然是没问题的)
注意下图红框的部分。、
例子3:选取列表中某个值前3的所有记录
例如:
- 选取薪水SAL前三的人
- 选取成绩倒数前三的人
Key Points:
- 前3不代表只是三个,因为有可能存在并列第一/第二
解决思路:先排序(根据实际决定升序还是降序),再选取前3行
例如:选取SAL前3的SNAME
先选出SAL前3的三个数值:
(刚开始学可能会这么写,错误warning:这种写法是先选列表的1,2,3行再排序)
(你可能会想:嵌套查询,把排序后的结果作为查询目标,错误warning:最值5000多个并列)
正确解法:
-
先排序去重
select distinct sal from emp order by sal desc;
-
再选出前3的sal
-
再从所有的记录中筛选sal在上述集合{5000,3000,2975}中的记录
select [col1, col2] from emp where sal in [set as above]
例子4:选取区间[a,b]的记录
上述解决了"前n个"即1<=rownum<=n这一类问题,但是并没有解决a<=rownum<=b这样更为通用的问题。但其实稍加改变就能够解决这个问题。
回想一下例子2的操作:把行号rownum"拼接"到一个表,让rownum变为表的一个属性。
(截图不完全)
然后对R属性筛选
现在来搞点更复杂的:找出薪水排名在2到4的(R, ENAME, SAL),注意考虑排名并列的问题
-
sal排名(2-4:3000,2975,2850)
-
先添加几个重复排名的(红框就是我们的筛选目标)
解决步骤:
-
先找出集合{3000,2975,2850}
-
再select [col1,...] from emp where sal in <set as above>
到这一步,筛选区间[a,b]的记录计算完成了。红框中即是修改[a,b]的地方。