英文:
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 |
英文:
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 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论