Oracle Paging (use of limit mode)

SELECT * FROM A_matrix_navigation_map

where rowID not in (select rowID from A_matrix_navigation_map where rownum<=0) and rownum<=10

The second type:

SELECT * FROM (

SELECT a.*, RowNum R from (

SELECT * from A_matrix_navigation_map

) A WHERE rownum <= 10

) B WHERE r > 0

Third Kind

SELECT * FROM table WHERE rownum<101;

Minus

SELECT * FROM table WHERE rownum<91;

Fourth type

You can use a bit of a workaround, I'll give you an example:

sql = "Select A_id,a_title,a_author,aid,a_time,a_readtime,sid from Article Order by a_id";

int A;

while (Rs.next ()) {

a++;

if (a=ipage+spage) continue;

...

}

The second of these is very mature paging

My instance ~ can implement a similar limit function

$bandanspager = "SELECT * FROM ABC T2,ABC1 t1 where t1.id=t2.id Group by t1.id";

$sql = "SELECT * FROM (

SELECT a.*, RowNum R from

(". $bandanspager.") A WHERE rownum <= 10

) B WHERE r > 0

Oracle does not support a limit similar to MySQL. But you can still rownum to limit the number of rows returned by the result set.

If you only want to return the first 10 rows of records, you can write:

SELECT * FROM table WHERE rownum<10;

However, the following statement is not correct:

SELECT * FROM table WHERE rownum>90 and rownum<100;

This is because Oracle believes that this condition is not true and therefore does not return.

You should write this:

SELECT * FROM table WHERE rownum<101;

Minus

SELECT * FROM table WHERE rownum<91;

Other database simulations to implement the limit syntax for MySQL

If I had a user table, I would like to query the 10 users starting with the 50th user that meet certain criteria, and can't use the ID number between and that query, how should I write the SQL statement?

In the MySQL database, there are limit,offset statements can be easily implemented, then in SQL Server? Does SQL Server support limit and offset statements?

Select Top Ten * from

(select top * from [user] ORDER by userid) AA

ORDER BY userid Desc

Oracle, if you want to remove from the table the first m to N records sorted by a field

The following Oracle statements are the best:

Sql> Select ID from (

Select ID, rownum as con from (

Select ID from Testsort ORDER by ID

) where rownum <= 3/*n value */

) where Con >= 2; /*m Value */

Id

----------

2

3

1. Implementing select TOP N in Oracle
Because Oracle does not support the SELECT top statement, the query for select top n is often implemented in Oracle with the combination of order by and RowNum.
To put it simply, the implementation method is as follows:
SELECT Column Name 1 ... Column name n from
(SELECT column Name 1 ... Column name n from table name ORDER by column name 1 ... Column name N)
WHERE ROWNUM <= N (number of extracted records)
ORDER by ROWNUM ASC
Here is an example to illustrate briefly.
Customer table Customer (Id,name) has the following data:
ID NAME
First
Second
Third
Forth, Geneva
Fifth
Sixth
Seventh
Eighth
Ninth
Ten Tenth
Last
The SQL statement for the first three customers is drawn according to the letter of name as follows:
SELECT * FROM
(SELECT * from CUSTOMER ORDER by NAME)
WHERE ROWNUM <= 3
ORDER by ROWNUM ASC
The output is:
ID NAME
Eighth
Fifth
First
2. Extract the first m (M <= N) record in the top N record
After obtaining the top n data, we can consider starting with rownum in order to extract the records of the nth record. We know that RowNum is a hidden sub-segment of the data number in the record table, so you can extract the recorded rownum at the same time as the top n record, and then extract the record numbered m from the N record, which is the result we want to get.
From the above analysis it is easy to get the following SQL statement.
SELECT Column Name 1 ... Column name n from
(
SELECT ROWNUM RECNO, column name 1 ... Column name Nfrom
(SELECT column Name 1 ... Column name n from table name ORDER by column name 1 ... Column name N)
WHERE ROWNUM <= N (number of extracted records)
ORDER by ROWNUM ASC
)
WHERE RECNO = m (M <= N)
Based on the same data from the table above, the SQL statement that gets the information of the second customer sorted alphabetically by name should read:
SELECT ID, NAME from
(
SELECT ROWNUM RECNO, ID, NAME from
(SELECT * from CUSTOMER ORDER by NAME)
WHERE ROWNUM <= 3
ORDER by ROWNUM ASC)
WHERE RECNO = 2
The result is:
ID NAME
Fifth
3. Extract the nth record in a recordset sorted in a certain way
In the 2 description, when m = n is the result of our Heading 3. In fact, 2 of the practice in the n>m part of the data is basically not used, we just to illustrate the convenience of the use.
As described above, the SQL statement should be:
SELECT Column Name 1 ... Column name n from
(
SELECT ROWNUM RECNO, column name 1 ... Column name Nfrom
(SELECT column Name 1 ... Column name n from table name ORDER by column name 1 ... Column name N)
WHERE ROWNUM <= N (number of extracted records)
ORDER by ROWNUM ASC
)
WHERE RECNO = N
So, the SQL statement for the example in 2 is:
SELECT ID, NAME from
(
SELECT ROWNUM RECNO, ID, NAME from
(SELECT * from CUSTOMER ORDER by NAME)
WHERE ROWNUM <= 2
ORDER by ROWNUM ASC
)
WHERE RECNO = 2
The result is:
ID NAME
Fifth
4. Extract the X record from the beginning of the record in the recordset that is sorted in a certain way
3 is only to extract a record of the situation, when we need to extract multiple records, at this time, the value of N in 2 should be in the range of n >= (M + X-1), of course, the most economical value is the time to take the equal sign. Of course the final extraction condition is not RECNO = n, it should be recno between M and (M + X-1), so the resulting SQL statement is:
SELECT Column Name 1 ... Column name n from
(
SELECT ROWNUM RECNO, column name 1 ... Column name Nfrom
(
SELECT Column Name 1 ... Column name n from table name ORDER by column name 1 ... Column name N)
WHERE ROWNUM <= N (n >= (M + X-1))
ORDER by ROWNUM ASC
)
WHERE RECNO between M and (M + X-1)
Also take the above data as an example, the 2nd record that extracts the letter of name starts with the SQL statement of 3 records:
SELECT ID, NAME from
(
SELECT ROWNUM RECNO, ID, NAME from
(SELECT * from CUSTOMER ORDER by NAME)
WHERE ROWNUM <= (2 + 3-1)
ORDER by ROWNUM ASC
)
WHERE RECNO between 2 and (2 + 3-1)
The results are as follows:
ID NAME
Fifth
First
Forth, Geneva

Oracle Paging (use of limit mode)