SQL查询之限制结果集行数（分页）

以前遇到需要查询前几行，或者需要分页查询时，经常到处找，乱、散、耗费时间，特此做一下汇总。

本文只讲SQL，不设计ORM框架。

首先，我们用到的数据库种类很多，每种数据库提供的语法还是有很大不同的，这里只列主流的几种。

MySql：

提供了LIMIT关键字用来限制返回的的结果集，LIMIT放在SELECT语句的最后位置，LIMIT有2个参数，第一个是返回行数的起始位置，第二个是返回的最大行数。例如：

SELECT * FROM T_USER ORDER BY ID LIMIT  2,5

上述查询语句的结果集：查询排名第3~7的年龄最小的用户。   如果需要查询前N行，只需改变LIMIT参数，例如： 前三行，即 LIMIT 0 , 3

MSSQLServer2000：

提供了 TOP 关键字用来返回结果集中的前N条。见例子：

SELECT TOP 5 * FROM T_USER ORDER BY AGE

上述查询语句的结果集：查询排名前5的年龄最小的用户。 

由于 SQLServer2000 未提供 “检索从M行到N行的数据” 这样的取区间范围的功能，故可用子查询

SELECT TOP 5 * FROM T_USER WHERE ID NOT IN ( SELECT TOP 10 ID FROM T_USER ORDER BY AGE ) ORDER BY AGE

上述查询语句的结果集：查询排名前11~15的年龄最小的用户。 

MSSQLServer2005：

SQLServer2005 兼容 几乎所有的 SQLServer2000的语法，所以可以是继续用 TOP 来限制结果集行数。另外，SQLServer2005提供了新特性就是窗口函数ROW_NUMBER()，可以计算每一行数据在结果集中的行号（从1开始计数），语法： ROW_NUMBER()  OVER(排序规则)  示例：

SELECT ROW_NUMBER()  OVER(ORDER BY AGE),* FROM T_USER

上述查询语句的结果集：查询按年龄升序排列的用户，并且第一列是每行的序号（1，2，3，···）。

如果我们需要查询： 查询排名前11~15的年龄最小的用户。 则需要用子查询来实现，例：

SELECT * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY AGE ) AS rownum, *  FROM T_USER ) AS a WHERE a.rownum>=11 and a.rownum<=15

Oracle：

a)、支持窗口函数ROW_NUMBER()，其用法和MSSQLServer2005中相同，例如：查询排名前11~15的年龄最小的用户

SELECT * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY AGE )   row_num, *  FROM T_USER )   a WHERE a.row_num>=11  and  a.row_num<=15    

这个地方需要注意：1、定义别名时，不能使用AS。2、rownum 是Oracle的关键字，不能做为别名，因此用 row_num代替。

b)、关键字：rownum。Oracle中无需自行计算行号，它会为每个结果集都增加一个默认的表示行号的列，这个列的名称就是rownum。

例如：查询排名前5的年龄最小的用户

SELECT * FROM T_USER  WHERE  rownum <=5 ORDER BY AGE

注意：关键字rownum只有结果集中有数据时，rownum才有意义。 如果在条件中存在 rownum between m and n ,m>1.则查询为空。

DB2：

a)、支持窗口函数ROW_NUMBER()，其用法和MSSQLServer2005以及Oracle中相同。例如：查询排名前11~15的年龄最小的用户

SELECT * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY AGE )   row_num, *  FROM T_USER )   a WHERE a.row_num>=11  and  a.row_num<=15   

b)、关键字FETCH，用来提取结果集的前N行。语法：FETCH FIRST m  ROWS ONLY  其中 m 为条数。例如：

SELECT * FROM T_USER ORDER BY AGE FETCH 6 ROWS ONLY

上述查询语句的结果集：查询排名前6的年龄最小的用户。 需要注意：FETCH子句要放在ORDER BY语句的后面。

DB2没有直接提供返回 “检索从第m行到第n行数据” 这样的取区间范围的功能，故可以采用其他方法来实现，常用的方法就是子查询。例：

SELECT  * FROM T_USER WHERE ID NOT IN ( SELECT  ID FROM T_USER ORDER BY AGE FETCH FIRST 10 ROWS ONLY ) ORDER BY AGE FETCH FIRST 5 ROWS ONLY

上述查询语句的结果集：查询排名前11~15的年龄最小的用户。

除了以上讲述的情况，还有两种方法  “检索从第m行到第n行数据”。差集 SQL MINUS或SQL EXCEPT，例如：

SELECT * FROM T_USER WHERE 条件1

MINUS

SELECT * FROM T_USER WHERE 条件2

或者

SELECT * FROM T_USER WHERE 条件1

EXCEPT

SELECT * FROM T_USER WHERE 条件2

上述查询语句的结果集： 根据条件1查出的结果集 减去 根据条件2查出的结果集。注意：MINUS和EXCEPT并不是每个数据库都支持，请测试后使用。

以Orcale中使用MINUS举例：查询排名前11~15的年龄最小的用户

SELECT * FROM T_USER  WHERE  rownum <=15 ORDER BY AGE

MINUS

SELECT * FROM T_USER  WHERE  rownum <=10 ORDER BY AGE

以上，就是我总结的关于 检索取区间数据的所有方法，希望对大家有点帮助。