在进行数据检索时,有时候需要只检索结果集中部分行,比如说“检索排在前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()。