PostgreSQL 限定结果数量

查询语句的结果可能包含成百上千行数据，但是前端显示时也许只需要其中的小部分，例如TOP-N排行榜；或者为了便于查看，每次只显示一定数量的结果，例如分页功能。为了处理这类应用，SQL提供了标准的FETCH和OFFSET子句。另外，PostgreSQL还实现了扩展的LIMIT语法。

Top-N查询

这类查询通常是为了找出排名中的前N个记录，例如以下语句查询薪水最高的前10名员工，使用FETCH语法：

SELECT first_name,last_name,salary FROM employees order by salary desc  fetch first 10 rows only 

其中，FIRST也可以写成NEXT，ROWS也可以写成ROW。结果返回了排序之后的前10条记录。使用LIMIT语法也可以实现相同的功能：

SELECT first_name,last_name,salary FROM employees order by salary desc limit 10;

分页查询

许多应用都支持分页显示的功能，即每页显示一定数量的记录（例如10行、20行等），同时提供类似上一页和下一页的导航。使用SQL实现这种功能需要引入另一个子句：OFFSET。假设我们的应用提供了分页显示，每页显示10条记录。现在用户点击了下一页，需要显示第11到第20条记录。使用标准SQL语法实现如下：

SELECT first_name,last_name,salary FROM employees order by salary desc OFFSET 10 ROWS fetch first 10 rows only ;

OFFSET表示先忽略掉多少行数据，然后再返回后面的结果。ROWS也可以写成ROW。对于应用程序而言，只需要传入不同的OFFSET偏移量和FETCH数量，就可以在结果中任意导航。使用LIMIT加上OFFSET同样可以实现分页效果：

SELECT first_name,last_name,salary FROM employees order by salary desc limit 10 offset 10;

注意事项

先看一下完整的FETCH和LIMIT语法：

SELECT column1, column2, ...FROM table[WHERE conditions][ORDER BY column1 ASC, column2 DESC, ...][OFFSET m {ROW | ROWS}][FETCH { FIRST | NEXT } [ num_rows] { ROW | ROWS }  ONLY];
SELECT column1, column2, ...FROM table[WHERE conditions][ORDER BY column1 ASC, column2 DESC, ...][LIMIT { num_rows| ALL } ][OFFSET m {ROW | ROWS}];

在使用以上功能时需要注意以下问题

• FETCH是标准SQL语法，LIMIT是PostgreSQL扩展语法。
• 如果没有指定ORDER BY，限定数量之前并没有进行排序，是一个随意的结果。
• OFFSET偏移量必须为0或者正整数。默认为0，NULL等价于0。
• FETCH限定的数量必须为0或者正整数。默认为1，NULL等价于不限定数量。
• LIMIT限定的数量必须为0或者正整数，没有默认值。ALL或者NULL表示不限定数量。
• 随着OFFSET的增加，查询的性能会越来越差。因为服务器需要计算更多的偏移量，即使这些数据不需要被返回前端。