SQL 5 限制结果集行数（MySQL & Oracle）

在进行数据检索时，有时候需要只检索结果集中部分行，比如说“检索排在前3名的学生”、“检索工资水平排在第三位～第七位的员工信息”，这种功能被称为“限制结果集行数”。虽然在主流的数据库系统中都提供了限制结果集行数的方法，但是无论是语法还是使用方式都存在很大的差异。

 

1. MySQL

MySQL提供了LIMIT关键字用来限制返回的结果集，LIMIT放在SELECT语句的最后位置，语法为

“LIMIT首行行号，要返回的结果集的最大数目”。比如下面的SQL语句

SELECT * FROM T_Employee ORDER BY FSalary DESC LIMIT 2,5

 

+----------+--------+------+---------+-------------+---------------+
| FNumber  | FName  | FAge | FSalary | FSubCompany | FDepartment   |
+----------+--------+------+---------+-------------+---------------+
| IT001    | Smith  |   28 | 5500.00 | Beijing     | InfoTech      |
| SALES001 | Timmy  |   25 | 5300.00 | Beijing     | Sales         |
| HR001    | Jane   |   23 | 4300.00 | Beijing     | HumanResource |
| DEV003   | Potter | NULL | 3333.00 | Guangzhou   | Development   |
| HR002    | Tina   |   25 | 3300.00 | Beijing     | HumanResource |
+----------+--------+------+---------+-------------+---------------+

 

很显然，下面的SQL语句将返回按照工资降序排列的前5条记录：

SELECT * FROM T_Employee ORDER BY FSalary DESC LIMIT 0, 5

+----------+-------+------+---------+-------------+---------------+
| FNumber  | FName | FAge | FSalary | FSubCompany | FDepartment   |
+----------+-------+------+---------+-------------+---------------+
| DEV001   | Tom   |   25 | 8300.00 | Beijing     | Development   |
| SALES002 | Stone |   35 | 8300.00 | Beijing     | Sales         |
| IT001    | Smith |   28 | 5500.00 | Beijing     | InfoTech      |
| SALES001 | Timmy |   25 | 5300.00 | Beijing     | Sales         |
| HR001    | Jane  |   23 | 4300.00 | Beijing     | HumanResource |
+----------+-------+------+---------+-------------+---------------+

 

 

2. Oracle

Oracle中支持窗口函数ROW_NUMBER()。

 

SELECT * FROM (

SELECT ROW_NUMBER() OVER(ORDER BY FSalary DESC) row_num,

FNumber, FName, FSalary, FAge, FROM T_Employee

)

WHERE a.row_num>=3 AND a.row_num<=5

 

Oracle支持标准的函数ROW_NUMBER(),不过Oracle中提供了更方便的特性用来计算行号，也就是在Oracle中可以无须自行计算行号。

Oracle为每个结果集增加了一个默认的表示行号的列，这个列的名称为rownum。比如我们执行下面的SQL语句。

SELECT rownum, FNumber, FName, FSalary, FAge, FROM T_Employee

结果待定~

 

使用rownum我们可以很轻松地取得结果集中前N跳的数据行，比如我们执行下面的SQL语句可以得到按工资从高到低排序的前6名员工的信息：

SELECT * FROM T_Employee WHERE rownum<= ORDER BY FSalary DESC

结果待定～

 

看到这里，你可能认为下面的SQL语句就可以非常容易实现“按照工资从高到低的顺序取出第三个～第五个员工信息”的功能了：

SELECT  rownum FNumber, FName, FSalary, FAge, FROM T_Employee WHERE rownum BETWEEN 3 AND 5 ORDER BY FSalary DESC

但检索的结果为空！！！

这是因为进行检索的时候，对于第一条数据，其rownum为1，因为不符合“WHERE rownum BWTWEEN 3 AND 5 ”所以没有被放到检索结果中；当检索到第二跳的时候，因为第一条数据没有放到结果集中，所以第二条数据的rownum仍然为1， 而不是我们所想的2；由此看来，可依次类推。

 

因此如果要使用rownum来实现“按照工资从高到低的顺序去胡第三个～第五个员工的信息”的功能，就必须借助于窗口函数ROW_NUMBER()。