SQL Cookbook:一、检索记录(9)限制返回的行数
问题
限制查询中返回的行数。此处不关心顺序,返回任意n行都可以。
解决方案
使用数据库提供的内置函数来控制返回的行数。
DB2
在DB2中,使用FETCH FIRST子句:
select * from emp fetch first 5 rows only
MySQL and PostgreSQL
在MySQL和PostgreSQL中,使用LIMIT:
select * from emp limit 5
Oracle
在Oracle中,在WHERE子句中通过使用ROWNUM来限制行数:
select * from emp where rownum <= 5
SQL Server
使用TOP关键字,来限制返回的行数:
select top 5 * from emp
讨论
许多数据库都提供一些子句例如FETCH FIRST和LIMIT,可以使用户指定从查询中返回的行数。Oracle的做法不同,必须使用ROWNUM函数来得到每行的行号(从1开始递增数值)。
在使用ROWNUM<=5返回前5行时会发生如下操作:
1. Oracle执行查询。
2. Oracle获取第一个符合条件的行叫做第1行。
3. 已经有5行了吗?如果没有,Oracle就再返回行,因为它要满足行号小于等于5的条件,如果有,那么Oracle就不再返回行。
4. Oracle获取下一行,并递增行号(从2,到3,再到4,等等)。
5. 转到第3步。
可以看到,Oracle的ROWNUM数值是在获取每行之后才被赋值的。这非常重要的一个关键点,比如说,许多Oracle开发人员想通过指定ROWNUM=5来返回第5行,这是错误的做法。下面说明ROWNUM=5时会发生什么:
1. Oracle执行查询。
2. Oracle获取第一个符合条件的行叫做第1行。
3. 已经有5行了吗?如果没有,Oracle就丢弃这些行,因为它不满足条件。如果到了第5行,那么Oracle就返回该行。但是,答案是永远不会有“到了第5行”的情况发生。
4. Oracle获取下1行,这是第1行。原因是从查询中返回的必须是行号为1的行。
5. 转到第3步。
仔细看这个过程,可以知道使用ROWNUM=5来返回第5行失败的原因。如果不返回第1行到第4行的话,就不会有第5行。
ROWNUM=1确实是返回第1行,这似乎与前面的说明矛盾了。原因是ROWNUM=1返回第1行,无论表中是否有记录行,Oracle都会尝试至少获取1行。请仔细看前面讲述的过程,把5换为1,就可以理解指定ROWNUM=1作为条件返回1行为什么是可行的了。