获取分页查询中的总行数 [英] Get total count of rows in pagination query

问题描述

我对记录分页有以下查询

I have the following query for record pagination

SELECT *
  FROM (SELECT e.*, 
               ROWNUM row_num
          FROM (SELECT emp_no,
                       emp_name,
                       dob 
                  from emp) outr
          WHERE ROWNUM < ( (pagenum * row_size) + 1))
 WHERE  row_num >= ( ( (pagenum  - 1) * row_size) + 1)

我也想在同一查询中获取行数，为此，我尝试了 使用

I would like to get the count of rows as well in same query and for this I have tried using

COUNT(*) OVER ()

但是，当我对下一组页面和行进行分页时，没有得到准确的结果.

however I am not getting accurate results when I am paginating to next set of page and rows.

如何有效使用COUNT(*) OVER ()?

推荐答案

典型的分页查询的总行数为:

A typical pagination query with the total number of rows would be:

SELECT *
  FROM (SELECT outr.*,
               ROWNUM row_num
          FROM (SELECT emp_no,
                       emp_name,
                       dob,
                       count(*) over () total_nb
                  FROM emp
                 ORDER BY ...) outr
         WHERE ROWNUM < ((pagenum * row_size) + 1))
 WHERE row_num >= (((pagenum - 1) * row_size) + 1)

别忘了ORDER BY.

这篇关于获取分页查询中的总行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！