Oracle uses rownum to implement Paging

// Rownum is a pseudo column. It is the number automatically assigned to each row in the query result by the Oracle system. The first row is 1, the second row is 2, and so on ....
// An oracle page, which must contain at least three layers (unless order by is not used, two layers can be used for the time being). The template is
Select temp2. * from (
Select rownum num, temp1. * from (
SQL query
) Temp1 where rownum <= n1
) Temp2 where temp2.num> n2
-
// For example, the following SQL statement returns 11th to 20 10 records of the query results:
Select temp2 .*
From (
Select rownum num, temp1 .*
From (
Select tt. title_id, tt. name
From t_title tt
Where tt. name like '%'
Order by tt. sort_seqs asc, tt. title_Id desc) temp1
Where rownum <= 20
) Temp2
Where temp2.num> 10
// Analysis:
// 1. First, a normal query statement (including order)
Select tt. title_id, tt. name
From t_title tt
Where tt. name like '%'
Order by tt. sort_seqs asc, tt. title_Id desc
// This is no different from normal SQL statements.
--
// 2. Add the rownum field to display the number of columns (Note: rownum must be <n1, nested before> n2)
Select rownum num, temp1 .*
From (
Select tt. title_id, tt. name
From t_title tt
Where tt. name like '%'
Order by tt. sort_seqs asc, tt. title_Id desc) temp1
Where rownum <= 20
// We have added the rownum display field. At this time, the number of The number of rows will be added to each row, and only 20 rows of data (including 20 rows) will be returned)
--
// 3. Capture 10th to 20 pieces of data, and the SQL statement is the most complete one.
// Use the final solution:
// 1. Use rownum:
// The following two statements:
Select rownum, id, name from student where rownum> 2;
Select rownum, id, name from student where rownum <= 10;
--
// The execution result of the first statement is null, And the execution result of the second statement is the first 10 records;
// Why? We know that rownum is a pseudo column and is a pseudo column automatically added to the query result by oracle. The first row is 1,
// If where rownum> 2, find the first row and find that its rownum is 1. If the condition is not met, discard it,
// Assign the rownum value of the second statement to 1, and then judge whether the second record meets the conditions ....
// When an infinite loop is returned, null is returned;
// This is strange. Why do we need to reset the value to 1 when rownum = 1 in the first record does not meet the condition?
// Very simple. You directly add the condition rownum> 2 after the where clause. It is a condition,
// If the first record does not meet the condition, it is discarded. At this time, the result set is empty and the value of rownum = 1 will always be assigned;
// Solution: first query and assign rownum to each record, then nested Query
Select t. * from (select rownum num, id, name from student) t where t. num> 2
// The second statement can be executed normally. According to the above explanation, this can be understood!
--
// 2. Simultaneous rownum and order by problems
// When rownum is determined after where and order by exists, rownum has a higher priority!
// Oracle will first judge rownum and then order by from the result. It is obviously a wrong result!
// It is like the top 10 students whose scores are the best in the school. As a result, 10 students are taken out and sorted according to their scores!
// This is completely different from SQL Server's TOP. If TOP encounters order by, it first executes order by, on the page;
// The solution is to execute order by first and then execute rownum nested
// To put it bluntly, use () to change the priority of the function!