sqlserver分页查询和oracle分页查询介绍

1 前言

因为平时工作接触到的都是sqlserver数据库或者oracle数据库，所以这里目前不含有mysql数据库的分页查询。

1.1 数据准备

1-1 student表
ID	code	name	age	sex
1	001	张三	24	男
2	002	李四	26	男
3	003	王五	21	女
4	004	赵六	18	男
5	005	钱七	28	女

2 sqlserver数据库

sqlserver数据库分页的方式有两种：

2.1 TOP关键字

top关键字在sql中就是取前几条的意思，后面接数字，数值就代表多少条。推荐使用这种方法，因为不需要考虑sqlserver版本。

select TOP 3 * from student where age>20 order by id asc

sql表达的意思：先升序（ID）获取年龄大于20的学生，并取前三条，很明显查到的数据分别是张三、李四、王五。

当然分页不会这么简单，一般都是x页到y页的形式来分页。

select TOP 2 * from (
    select TOP 3 * from student where age>20 order by id asc
) first order by id desc

sql表达的意思：先执行里面的子查询，升序（ID）获取年龄大于20的学生，并取前三条，很明显查到的数据分别是张三、李四、王五。再降序（ID）变成王五、李四、张三，最后获取前两条，查到的数据分别是王五、李四，不懂的请看下图分析，不要脸的贴了一张图。

2-1 逻辑简图

当然，如果想要数据升序排列，还需要再进行一次排序。

2.2 offset /fetch next（2012版本及以上才有）

offset A rows ，将前A条记录舍去，fetch next B rows only ，向后在读取B条数据。

select * from student order by id offset 1 rows fetch next 2 rows only;

结构：select查表 - where条件 - order排序 - offset舍弃 - fetch拿出

sql表达的意思：舍弃一条数据，拿出两条数据，那么查到的两条数据分别是李四和王五。这里舍弃和拿出都是按顺序的，也就是排完序后的数据，从第一条开始。

3 oracle 数据库

oracle数据库有一个rownum关键字，它可以生成当前数据所在的行，我们分页就是判断rownum是否符合条件。

3.1 minus关键字

使用minus，原理就是查询出前X行的数据，减去查询出前Y行的数据，X必须大于Y。

select * from student where rownum<=5 
minus 
select * from student where rownum<=3

这样就得到两行数据，分别是赵六和钱七。

3.2 效率高的写法

不排序分页：

SELECT * FROM (
    SELECT ROWNUM AS rowno, stu.* FROM 
    student stu WHERE ROWNUM <= 4
) stuTable WHERE stuTable.rowno >= 2;

排序分页：

SELECT * FROM (
    SELECT stu.*, ROWNUM AS rowno FROM (  
        SELECT * FROM student ORDER BY ID DESC
    ) stu WHERE ROWNUM <= 4
) stuTable WHERE stuTable.rowno >= 2;

可以看出不排序分页的效率比排序分页的效率高，因为只嵌套一层，速度最快。对取到的数据顺序有要求的，则用排序分页的sql，对数据顺序无要求的，建议用不排序分页。

注意：where条件执行优先于order排序，所以最里层sql只进行排序，不能同时进行rownum的大小判断

3.3效率低的写法

为什么还要存在效率低的写法呢，因为写起来实在是方便，而且好理解。这就是廉价代码，当然我们不能因为项目不值钱就做没技术的事情，好好对待每一个写代码的机会，慢慢提升技术品味。

不排序分页：

SELECT * FROM (
    SELECT ROWNUM AS rowno, stu.* FROM student stu 
) stuTable WHERE stuTable.rowno <= 4 AND stuTable.rowno >= 2;

排序分页：

SELECT * FROM (
    SELECT stu.*, ROWNUM AS rowno FROM (  
        SELECT * FROM student ORDER BY ID
    ) stu
) stuTable WHERE stuTable.rowno BETWEEN 10 AND 20;

4 结束语

就先写这么多了，内容以后有时间了或者工作上遇到了更高级一点的写法再慢慢补充。