oracle 分页

oracle分页小结
现在又要写分页的东西，之前就写过，倒是参照着别人写的那么改的，虽然是成功了，但是还是有些好奇，然后自己再写一遍。对这个排版我都无语了，哎将就吧

首先建立一个表比如说test
有如下字段
id  name  age  
数据如下
id        name             age
==================================================
1         xiaoming         12
2         xiaozhang         8
3         haha                 7
4         wo                    1
5         ni                    74
6         hao                 45
7         yun                 56
8         gua                 20  
9         bu                   32
10        ma                 28
11        ya                  95
12        xiaogang       62
13        luan               89
14        ok                  41
15        wan               92

执行：
select *
from test t
where 1=1
order by t.age

结果如下
id        name             age
==================================================
4         wo                  1
3         haha               7
2         xiaozhang       8
1         xiaoming         12
8         gua                 20
10        ma                 28
9         bu                   32
14        ok                  41
6         hao                 45
7         yun                 56
12        xiaogang        62
5         ni                     74
13        luan                89
15        wan                92
11        ya                   95


好我们都知道oracle分页有个关键字的rownum（sqlserver是top，mysql是limit）那怎么使用呢
我自作聪明来取前5条数据（最简单的分页了哈哈）

---sql@1--- 
select *
from test t
where 1=1
rownum<=5
order by t.age

结果如下
id        name             age
==================================================
4         wo                    1
3         haha                 7
2         xiaozhang         8
1         xiaoming         12
5         ni                    74


但是我想要的结果是
id        name             age
==================================================
4         wo                    1
3         haha                 7
2         xiaozhang         8
1         xiaoming         12
8         gua                 20


哦终于想起来了，sql的执行顺序是先from在where在order了，order永远是在最后了。我那个sql@1语句是先取出前5个在排序，但是没有达到我的要求。所以我改了下执行顺序，


修改为如下的：
---sql@2---
select *
from ( select *
      from test t
      where 1=1
      order by t.age ) T
where rownum<=5

结果如下
id        name             age
==================================================
4         wo                  1
3         haha               7
2         xiaozhang       8
1         xiaoming         12
8         gua                 20


刚好是我想要的结果。
ok我们接着做，因为一般分页都是取第N页到第M页之间的所有数据，翻译为数学就是 N <= X <= M ，但是oracle的rownum并不直接支持这种写法，所以需要拆分这个写法，首先是X <= M这个，rownum本身就是支持这种带范围的比较，那对于 rownum >=N呢，其实想想你如果直接rownum >=5你觉得会返回什么结果呢，这个是无范围的比较，在一个无限不确定的结果集中，所以oracle就返回0了，并不报错，那么如果是在一个有限的确定的结果集中呢，如果在一个有限的确定的结果集中，rownum的值是可以确定的，因此就可以使用>=操作了，rownum的查询就是在5和这个确定的有限结果集中了，好了，试下便知道了。接着刚才的sql我们来写个取第5到第10之间数据的sql，按照刚才的分析，我们必须要有个有限的确定的结果集，那么我们这个有限的结果集就选取前10条结果集，怎么取前10条呢，当然最简单的就是 rownum<=10

---sql@3---
select *
from ( select *
      from test t
      where 1=1
      order by t.age ) T
where rownum<=10

结果如下
id        name             age
==================================================
4         wo                    1
3         haha                 7
2         xiaozhang         8
1         xiaoming          12
8         gua                  20
10        ma                  28
9         bu                    32
14        ok                   41
6         hao                  45
7         yun                   56

ok这就是我们的有限的确定的结果集，你可以试着显示rownum

---sql@4---
select T.*,rownum rnm
from ( select *
      from test t
      where 1=1
      order by t.age ) T
where rownum<=10

结果如下
id        name             age      rnm
==================================================
4         wo                  1          1
3         haha               7          2
2         xiaozhang       8          3
1         xiaoming         12        4
8         gua                 20        5
10        ma                 28        6
9         bu                   32        7
14        ok                  41        8
6         hao                 45        9
7         yun                 56        10

看到没，这里就有一个确定的有限的rownum结果集（从1到10）其实我们用rownum>5就是在这个结果集（从1到10）中来比较了，确切说是和字段rnm来比较了。

ok我们要把这个结果看做一个新的结果集（有限的确定的），那我们怎么做呢，哈哈还是sql的语法了主要是from的用法了,然后在结合新的rownum比较rownum>=5

---sql@5---
select * 
from (select T.*,rownum rnm
      from ( select *
             from test t
             where 1=1
             order by t.age ) T
      where rownum<=10 )
where rnm>=5

结果如下
id        name             age      rnm
==================================================
8         gua              20          5
10        ma               28          6
9         bu               32           7
14        ok               41          8
6         hao              45          9
7         yun              56          10


哈哈终于成功了。分页完成。
有人问为什么你要用rownum rnm呢，怎么不直接

---sql@6---
select * 
from (select T.*
      from ( select *
             from test t
             where 1=1
             order by t.age ) T
      where rownum<=10 )
where rownum>=5

结果如下
id        name             age      rnm
==================================================

疑惑了吧，怎么没数据呢，rownum是当次select查询的结果集的一个属性，你在外部的rownum是新的一个结果集的属性，这样你又犯了那个错，在一个无限的不确定的结果集中执行rownum>=5.
分析完上面的过程我又想，其实我可以用另一种方式实现。既然我已经选取了前10条记录，那么其实我的目的是选取后5条数据，那我倒排序然后取前5条不就可以了吗，好先试下看吧。

---sql@7---
select * 
from (select T.*
      from ( select *
             from test t
             where 1=1
             order by t.age ) T
      where rownum<=10 
      order by t.age desc
    )
where rownum<=5

结果如下
id        name             age      
==================================================
7         yun              56      
6         hao              45        
14        ok               41        
9         bu               32        
10        ma               28        
8         gua              20       
结果是取到了，但是是倒序的，为了到达目的，所以我们在加个排序
---sql@8---
select * 
from (select T.*
      from ( select *
             from test t
             where 1=1
             order by t.age ) T
      where rownum<=10 
      order by t.age desc
    )
where rownum<=5
order by age

结果如下
id        name             age     
==================================================
8         gua              20        
10        ma               28       
9         bu               32       
14        ok               41       
6         hao              45       
7         yun              56        

 

ok总算完结，不管用哪种分页都是一种思路。