[자바] 오라클 페이징 paging 쿼리

오라클 페이징 쿼리 원리

select a.*

from(

select row_number() over(order by app asc) as r_num

,count(*) over () as totcount

,(row_number() over (order by app desc)) page0

,(row_number() over (order by app desc)-1) / 5 as page1

,floor( (row_number() over(order by app desc)-1) -1 / 5 +1 ) as page0

from table

)a

;

결과:

r_num totcount page0 page1 page

100 100 1 0 1

99 100 2 0.2 1

98 100 3 0.4 1

97 100 4 0.6 1

96 100 5 0.8 1

95 100 6 1 2

94 100 7 1.2 2

93 100 8 1.4 2

92 100 9 1.6 2

91 100 10 1.8 2

90 100 11 2 3

89 100 12 2.2 3

88 100 13 2.4 3

87 100 14 2.6 3

2페이지만 출력한다고 했을때는

select a.*

from(

select row_number() over(order by app asc) as r_num

,count(*) over () as totcount

,(row_number() over (order by app desc)) page0

,(row_number() over (order by app desc)-1) / 5 as page1

,floor( (row_number() over(order by app desc)-1) -1 / 5 +1 ) as page0

from table

)a

where page=2

;

결과:

r_num totcount page0 page1 page

95 100 6 1 2

94 100 7 1.2 2

93 100 8 1.4 2

92 100 9 1.6 2

91 100 10 1.8 2