怎么样才能只执行一条sql语句，就返回某一页的数据，同时返回总条数？

窗口函数 count(*) over()

​​https://stackoverflow.com/questions/156114/best-way-to-get-result-count-before-limit-was-applied/8242764#8242764​​

-------------------------------

When paging through data that comes from a DB, you need to know how many pages there will be to render the page jump controls.

Currently I do that by running the query twice, once wrapped in a ​​count()​​ to determine the total results, and a second time with a limit applied to get back just the results I need for the current page.

This seems inefficient. Is there a better way to determine how many results would have been returned before ​​LIMIT​​ was applied?

I am using PHP and Postgres.

 

Pure SQL

Things have changed since 2008. You can use a ​​window function​​ to get the full count and the limited result in one query. Introduced with ​​PostgreSQL 8.4 in 2009​​.

SELECT foo
     , count(*) OVER() AS full_count
FROM   bar
WHERE  <some condition>
ORDER  BY <some col>
LIMIT  <pagesize>
OFFSET <offset>;


Note that this can be considerably more expensive than without the total count. All rows have to be counted, and a possible shortcut taking just the top rows from a matching index may not be helpful any more.

Doesn't matter much with small tables or ​​full_count​​ <= ​​OFFSET​​ + ​​LIMIT​​. Matters for a substantially bigger ​​full_count​​.

Corner case: when ​​OFFSET​​ is at least as great as the number of rows from the base query, no row is returned. So you also get no ​​full_count​​. Possible alternative:

Sequence of events in a ​​SELECT​​ query

( 0. CTEs are evaluated and materialized separately. In Postgres 12 or later the planner may inline those like subqueries before going to work.) Not here.

  2. ​​WHERE​​ clause (and ​​JOIN​​ conditions, though none in your example) filter qualifying rows from the base table(s). The rest is based on the filtered subset.

( 2. ​​GROUP BY​​ and aggregate functions would go here.) Not here.

( 3. Other ​​SELECT​​ list expressions are evaluated, based on grouped / aggregated columns.) Not here.

  2. Window functions are applied depending on the OVER clause and the frame specification of the function. The simple count(*) OVER() is based on all qualifying rows.
  3. ORDER BY

( 6. ​​DISTINCT​​ or ​​DISTINCT ON​​ would go here.) Not here.

  2. ​​LIMIT​​ / ​​OFFSET​​ are applied based on the established order to select rows to return.

​​LIMIT​​ / ​​OFFSET​​ becomes increasingly inefficient with a growing number of rows in the table. Consider alternative approaches if you need better performance:

Alternatives to get final count

There are completely different approaches to get the count of affected rows (not the full count before ​​OFFSET​​ & ​​LIMIT​​ were applied). Postgres has internal bookkeeping how many rows where affected by the last SQL command. Some clients can access that information or count rows themselves (like psql).

For instance, you can retrieve the number of affected rows in plpgsql immediately after executing an SQL command with:

GET DIAGNOSTICS integer_var = ROW_COUNT;


​​Details in the manual.​​

Or you can use ​​pg_num_rows in PHP​​. Or similar functions in other clients.

Related: