[笔记]流行数据库SQL差异分析之“限制结果集行数”
前言：近期所参与的项目共涉及了三种数据库，SQLServer、Oracle、MySQL。所以就找来这些资料，并记录为笔记供以后参考。
限制结果集行数
在进行数据检索的时候需要只检索结果集中的部分行，比如说“检索成绩排前三名的学生”、“检索工资水平排在第3位到第7位的员工信息”，这种功能被称为“限制结果集行数”。虽然主流的数据库系统中都提供了限制结果集行数的方法，但是无论是语法还是使用方式都存在着很大的差异。即使是同一个数据库系统的不同版本(比如MSSQLServer2000和MSSQLServer2005)也存在着一定的差异。
MYSQL
MYSQL中提供了LIMIT关键字用来限制返回的结果集，LIMIT放在SELECT语句的最后位置，语法为“LIMIT首行行号，要返回的结果集的最大数目”，比如下面的SQL语句将返回按照工资降序排列的从第二行开始(行号从0开始)的最多五条记录：
SELECT * FROM T_Employee ORDER BYFSalary DESC LIMIT 2,5 //返回第二行到第七行
SELECT * FROM T_Employee ORDER BYFSalary DESC LIMIT 0,5 //返回前五条
SQLServer2000
SQLServer2000中提供了TOP关键字用来返回结果集中的前N条记录，其语法为"SELECT TOP限制结果集数目字段列表SELEC下语句其余部分”，比如下面的SQL语句用来检索工资水平排在前五位(按照工资从高到低)的员工信息：
select top 5 * from T_Employee orderby FSalary Desc
SQLServer2000没有直接提供返回提供“检索从第5行开始的10条数据”、“检索第五行至第十二行的数据”等这样的取区间范围的功能，不过可以采用其他方法来变通实现，最常使用的方法就是用子查询，比如要实现检索按照工资从高到低排序检索从第六名开始一共三个人的信息，那么就可以首先将前五名的主键取出来，在检索的时候检索排除了这五名员工的前三个人，SQL如下:
SELECT top 3 * FROM T_Employee
WHERE FNumber NOT IN
(SELECT TOP 5 FNumber FROM T_EmployeeORDER BY FSalary DESC)
ORDER BY FSalary DESC
SQLServer2005
SQLServer2005兼容几乎所有的SQLServer2000的语法，所以可以使用上面提到的方式来在SQLServer2005中实现限制结果集行数，不过SQLServer2005提供了新的特性来帮助更好的限制结果集行数的功能。这个新特性就是窗口函数ROW_NUMBER()，ROW_NUMBER()函数可以计算每一行数据在结果集中的行号(从1开始计数)，其使用语法如下:
ROW_NUMBEROVER(排序规则)
比如执行下而的SOL语句：
SELECTROW_NUMBER() OVER(ORDER BY FSalary),FNumber,FName,FSalary,FAge FROM T_Employee //返回排序过的行号
ROW_NUMBER()不能用在WHERE语句中，可以用子查询来返回第3行到第5行的数据：
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY FSalary DESC)AS rownum,
FNumber,FName,FSalary,FAge FROMT_Employee
) AS a
WHERE a.rownum>=3 AND a.rownum<=5
Oracle
Oracle中支持窗口函数ROW_NUMBER()，其用法和SQLServer2005中相同，比如执行下面的SQL语句：
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BYFSalary DESC) row_num,
FNumber,FName,FSalary,FAge FROMT_Employee
) a
WHERE a.row_num>=3 AND a.row_num<=5
注意：rownum在Oracle中为保留字，所以这里将SQLServer2005中用到的rownum替换为row_num，Oracle中定义表别名的时候不能使用AS关键字，所以这里也去掉了AS，Oracle支持标准的函数ROW_NUMBER()，不过Oracle中提供了更方便的特性用来计算行号，也就在Oracle中可以无需自行计算行号，Oracle为每个结果集都增加了一个默认的表示行号的列，这个列的名称为rownum，比如执行下面的SQL语句：
SELECT rownum,FNumber,FNas.e,FSalary,FAgeFROM T _Employee
执行完毕我们就能在输出结果中看到下面的执行结果：
使用rownum可以很轻松的取得结果集中前N条的数据行，比如执行下面的SQL语句可以得到按工资从高到底排序的前6名员工的信息：
SELECT * FROM _Employee WHERE rownum<=6 ORDER BY FSalary Desc
下面的SQL就可以非常容易的实现“按照工资从高到低的顺序取出第三个到第五个员工信息”的功能了：
SELECTrownum,FNumber,FName,FSalary,FAge FROM T_Employee
WHERE rownum BETWEEN 3 AND 5
ORDER BY FSalary DESC //执行结果为空，结论看下面。
回顾一下rownum的含义：rownum为结果集中每一行的行号(从1开始计数)。对于下面的SQL：
SELECT * FROM_Employee WHERE rownum<=6 ORDER BY FSalary Desc
当进行检索的时候，对于第一条数据，其rownum为1，因为符合“WHERErownum<=6"所以被放到了检索结果中，当检索到第二条数据的时候，其rownum为2，因为符合“WHERE
rownum<=6"所以被放到了检索结中。依次类推，直到第七行。所以这句SQL语句能够实现“按照工资从高到低的顺序取出第三个到第五个员工信息”的功能。
而对于这句SQL语句：
SELECTrownum,FNumber,FName,FSalary,FAge FROM T_Employee
WHERE rownum BETWEEN 3 AND 5
ORDER BY FSalary DESC
当进行检索的时候，对于第一条数据，其rownum为1，因为不符合“WHERErownum BETWEEN 3 AND 5"，所以没有被放到了位索结果中，当检索到第二条数据的时候，因为第一条数据没有放到结果集中，所以第二条数据的rownum仍然为1，而不是我们想像的2，所以因为不符合‘WHERErownum<=6"，没有被放到了植索结果中，当检索到第三条数据的时候，因为第一、二条数据没有放到结果集中，所以第三条数据的rownum仍然为1，而不是我们想像的3，所以因为不符合“WHERErownum<=6"，没有被放到了检索结果中。依此类推，这样所有的数据行都没有被放到结果集中。
因此如果要使用rownum来实现“按照工资从高到低的顺序取出第三个到第五个员工信息”的功能，就必须借助于窗口函数ROW_NUMBER()。
DB2
DB2中支持窗口函数ROW_NUMBER()，其用法和SQLServer2005以及Oracle中相同，比如执行下面的SQL语句：
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BYFSalary DESC) row_num,
FNumber,FName,FSalary,FAge FROMT_Employee
) a
WHERE a.row_num>=3 ANDa.row_num<=5
除此之外，DB2还提供了FETCH关键字用来提取结果集的前N行，其语法为“FETCH FIRST 条数 ROWS ONLY”，比如我们执行下面的SQL语句可以得到按工资从高到底排序的前6名员工的信息：
SELECT * FROM T_Employee ORDER BYFSalary Desc FETCH FIRST 6 ROWSONLY
注意：FETCH子句要放到ORDERBY语句的后面。
DB2没有直接提供返回提供“检索从第5行开始的10条数据”、“检索第五行至第十二行的数据”等这样的取区间范围的功能，不过可以采用其他方法来变通实现，最常使用的方法就是用子查询，比如要实现检索按照工资从高到低排序检索从第六名开始一共三个人的信息，那么就可以首先将前五名的主键取出来，在检索的时候检索排除了这五名员工的前三个人。
SELECT * FROM T_Employee
WHERE FNumber NOT IN
(
SELECT FNumber FROM T_Employee
ORDER BY FSalary DESC
FETCH FIRST 5 ROWS ONLY
)
ORDER BY FSalary DESC
FETCH FIRST 3 ROWS ONLY
数据库分页
实现数据库分页的核心技术就是“限制结果集行数“，假设每一页显示的数据条数为PageSize，当前页数(从0开始技术)为Currentlndex，那么我们只要查询从第PageSize*CurrentIndex开始的PageSize条数据得到的结果就是当前页中的数据，当用户点击【上一页】按钮的时候，将Currentlndex设置为Currentlndex-1，然后重新检索。当用户点击【下一页】按钮的时候，将Currentlndex设置为Currentlndex+1，然后重新检索。当用户点击【首页】按钮的时候，将Currentlndex设置为0，然后重新检索。当用户点击【首页】按钮的时候，将Currentlndex设置为“总条数/PageSize"，然后重新检索。
