相当于Postgresql中的FOUND_ROWS（）函数 [英] Equivalent of FOUND_ROWS() function in Postgresql

问题描述

我在我的应用程序中做一些分页，每次使用PostgreSQL的标准OFFSET和LIMIT关键字从数据库中返回20行。例如，要获取第1页页面：

I am doing some paging in my application, returning 20 rows from the database at a time using PostgreSQL's standard OFFSET and LIMIT keywords. For instance, to get page 1 page:

SELECT stuff FROM table WHERE condition ORDER BY stuff OFFSET 0 LIMIT 20

这是应用程序的要求，我们还向用户显示总记录数。所以，显然，我可以通过发出一个单独的查询获得总数：

It is a requirement of the application that we also show to the user the total number of records. So, obviously, I can get the total by issuing a separate query:

SELECT COUNT(*) FROM table WHERE condition

但是如果有大量的行，那么这不是一个最佳的解决方案。我注意到MySQL有一个非常有用的函数FOUND_ROWS（），它正是我正在寻找：

But if there are a large number of rows then this is not an optimal solution. I notice that MySQL has a very useful function called FOUND_ROWS() that does exactly what I am looking for:

http://dev.mysql.com/doc/refman/5.0/en/information-functions。 html＃function％5Ffound-rows

PostgreSQL中是否有等效项？

Is there an equivalent in PostgreSQL?

推荐答案

PostgreSQL已经窗口函数一段时间了，可用于执行许多操作，包括在应用LIMIT之前对行进行计数。

PostgreSQL has had window functions for a while now which can be used to do many things including counting rows before LIMIT is applied.

基于上述示例：

SELECT stuff,
       count(*) OVER() AS total_count
FROM table
WHERE condition
ORDER BY stuff OFFSET 40 LIMIT 20

这篇关于相当于Postgresql中的FOUND_ROWS（）函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！