Oracle中 rownum 的实用案例

Oracle中 rownum 的实用案例

首先我们需要了解一些基本的概念.
在MySQL中分页有 limit 关键字, Limit 2 :从头开始查 查两条.
Limit 2,1 :从第二条开始查 查一条,也就是我们想要第四条的数据,以此类推 也可以Limit 2,6 等等
但是再Oracle中是没有 limit 的,那我们怎么办呢?Oracle公司当然不会犯这种错误,于是rownum油然而生.
rownum: Oracle中的伪列
详细rownum理论传送门: https://blog.****.net/qq_39196949/article/details/84379874

上案例
新建 Test 表 具体结构如下 结尾附 sql文件

建好表后再添加一些数据 因为Oracle是没有自增主键这一说的 我们需要自己新建一个自增主键
– 测试表 创建自增序列
create sequence zizeng
increment by 1
start with 1
minvalue 1
maxvalue 9999;

解释: 创建一个名为 zizeng 每次加1 从1开始 最小值为1 最大值为9999的 自增序列

新增数据
insert into test (id,name,money,deptid) values (zizeng.Nextval,‘哈哈’,3691,1)
insert into test (id,name,money,deptid) values (zizeng.Nextval,‘小明’,4691,1)
解释:这样我们新增主键就可以不用再管 可以高枕无忧了 只需 自增序列名+Nextval 关键字即可 只需要改一下名字 和工资 money就行了

新增数据完成 查询一下表的数据

首先是 rownum 分页
如果带有排序 rownum是排序前的所以分页就乱了
select a.* ,rownum from test a

带排序
select a.* ,rownum from test a order by money

为了解决排序后行号乱了问题,先排序后去行号
select a.* ,rownum from(select * from test order by money)a

第二个问题 取你想要的数据 再mysql中可以 select * from test order by money limit 2,1 就取到了 第三条数据 但是再Oracle中却没这么简单

第一步 我们要知道Oracle的rownum可以 < 但是不可以直接 > 只能用 >0
如果我这样写:
select a.* , rownum from(select * from test order by money)a
where rownum>1 and rownum<=4

是没结果的

为了解决这个问题,可以先获取行号后再进行对比,注意要取别名 要取别名哦~
步骤: select * from test order by money 这是第一层
select a.* ,rownum as rn from(第一层)a 这是第二层
select * from(第二层)b where b.rn >2 这是第三层 已经可以用 > 号了
成品:
select * from (
select a.* ,rownum as rn from(
select * from test order by money
)a
)b
where b.rn>2

这就可以了 可以 用 > < 号了,那么我们回到问题shan给 取到我们想要的某一条数据
既然可以 >< 了 那我们活学活用 直接定位我们所需要数据
select * from (
select a.*,rownum as rn from(
select * from test order by money
)a
)b
where b.rn>2 and b.rn<4

这样我们通过更改 >数字 and <数字 就可以获得我们想要的数据了 是不是很nice呢

留个课后作业: 查询出大于3个人工资的其他人员 先自己写写 写不出再看答案

– 查询大于三个人工资的其他员工
select test.* from
(select * from (
select a.*,rownum as rn from(
select * from test order by money
)a
)b
where b.rn>2 and b.rn<4)c,test
where test.money>c.money
order by test.money