Oracle Advanced Query-subquery (pagination display case) ____oracle

Case: Paging Query displays employee information: Show employee number, name, salary

1. Display four records per page

2. Employees showing the second page

3. Sort by descending monthly salary

Analysis:

1. You can display the location of the query information by pseudo-column rownum.

Note: RowNum can only use <, <=, can not use >, >=. Line number (rownum) in Oracle will always start at 1

2. Check out employee information in descending order

Select Rownum,empno,ename,sal from emp order by Sal Desc;

3. The results of the above query as a collection, query out the previous 8 records

Select RowNum, Empno,ename,sal from (

Select Rownum,empno,ename,sal from emp desc to Sal E1

where RowNum <8;

4. The first 8 records that have been queried are used as a collection, and the rownum in this collection is not a line number, which can be used as the result of the query

The first column is displayed in the final query results.                   Query 5th, 6, 7, 8 records that is the second page of employee information. Select R,empno,ename,sal
From (select RowNum R, empno,ename,sal
From (select Rownum,empno,ename,sal from emp order BY sal Desc) E1
where RowNum <=8) E2

where r>4;

As shown in the figure: