SQL使用ROWNUM进行分页查询的总结果数量。

huangapple go评论69阅读模式
英文:

SQL select the total number of results in a paginated query with ROWNUM

问题

I would like to figure out if I can select in a single query the total number of results of the query if it wasn't paginated with the ROWNUM values.
My Table is very simple, just these 3 columns (ID, DESCRIPTION, NAME)

select  ID, DESCRIPTION, NAME
from ( select  ID, DESCRIPTION, NAME, ROWNUM as rnum 
    from TESTDATAITEM where DESCRIPTION = 'test' and NAME = 'foo' 
    order by ID )
a where rnum between 0 AND 9 order by rnum

This is the query that my java backend runs with myBatis to return the first page (10 results) to the React UI, while instead, the total results number if not limited with "rnum between 0 AND 9" would be hundreds..

Is there a way to obtain the information of the total number of results in the same query or the best way is to run another query without limiting the results between 0 and 9?

I've tried to add a count() for TOTALRESULTS and a group by but it's not working the way I thought

 select TOTALRESULTS, ID, DESCRIPTION, NAME
 from ( select count(*) as TOTALRESULTS, ID, DESCRIPTION, NAME, ROWNUM as rnum 
     from TESTDATAITEM where DESCRIPTION = 'TEST' and NAME = 'TEST_NAME' 
     group by ID, NAME, ROWNUM
     order by ID )
 a where rnum between 0 AND 9 order by rnum

Ps I need to use ROWNUM although it's ugly (instead of LIMIT /OFFSET) cause there is an old version of oracle

thanks

英文:

I would like to figure out if I can select in a single query the total number of results of the query if it wasn't paginated with the ROWNUM values.
My Table is very simple, just these 3 columns (ID, DESCRIPTION, NAME)

  select  ID, DESCRIPTION, NAME
  from ( select  ID, DESCRIPTION, NAME, ROWNUM as rnum 
      from TESTDATAITEM where DESCRIPTION = 'test' and NAME = 'foo' 
      order by ID )
  a where rnum between 0 AND 9 order by rnum

This is the query that my java backend runs with myBatis to return the first page (10 results) to the React UI, while instead, the total results number if not limited with "rnum between 0 AND 9" would be hundreds..

Is there a way to obtain the information of the total number of results in the same query or the best way is to run another query without limiting the results between 0 and 9?

I've tried to add a count() for TOTALRESULTS and a group by but it's not working the way I thought

 select TOTALRESULTS, ID, DESCRIPTION, NAME
 from ( select count(*) as TOTALRESULTS, ID, DESCRIPTION, NAME, ROWNUM as rnum 
     from TESTDATAITEM where DESCRIPTION = 'TEST' and NAME = 'TEST_NAME' 
     group by ID, NAME, ROWNUM
     order by ID )
 a where rnum between 0 AND 9 order by rnum

Ps I need to use ROWNUM although it's ugly (instead of LIMIT /OFFSET) cause there is an old version of oracle

thanks

答案1

得分: 3

你可以使用分析版本的计数函数,对整个结果集执行:

select count(*) over () as TOTALRESULTS

这将给你(带有一些虚拟示例数据):

select TOTALRESULTS, ID, DESCRIPTION, NAME
  from ( select count(*) over () as TOTALRESULTS, ID, DESCRIPTION, NAME, ROWNUM as rnum 
      from TESTDATAITEM where DESCRIPTION = 'test' and NAME = 'foo'
      order by ID )
  a where rnum between 0 AND 9 order by rnum
TOTALRESULTS ID DESCRIPTION NAME
99 1 test foo
99 2 test foo
99 3 test foo
99 4 test foo
99 5 test foo
99 6 test foo
99 7 test foo
99 8 test foo
99 9 test foo

fiddle

英文:

You can use the analytic version of count, over the entire result set:

select count(*) over () as TOTALRESULTS

giving you (with some dummy sample data):

select TOTALRESULTS, ID, DESCRIPTION, NAME
  from ( select count(*) over () as TOTALRESULTS, ID, DESCRIPTION, NAME, ROWNUM as rnum 
      from TESTDATAITEM where DESCRIPTION = 'test' and NAME = 'foo'
      order by ID )
  a where rnum between 0 AND 9 order by rnum
TOTALRESULTS ID DESCRIPTION NAME
99 1 test foo
99 2 test foo
99 3 test foo
99 4 test foo
99 5 test foo
99 6 test foo
99 7 test foo
99 8 test foo
99 9 test foo

fiddle

huangapple
  • 本文由 发表于 2023年5月17日 17:06:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/76270360.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定