Paging between SQL Server and Oracle

 

In normal project development, the paging stored procedure uses a large number of stored procedures. top is often used in the SQL Server paging stored procedure, and RowNum is often used in Oracle.

Now, there is a UserInfo table, where one field is UserId and the other is UserName, where UserId increases automatically and step size is 1. there are 30 data records in the table, and the UserId value is not necessarily continuous. The purpose is to retrieve 11th to 20th records. Let's take a look at several SQL Server practices:

 

Method 1:

Select top 10 *
From userinfo
Where userid in
(
Select top 20 userid
From userinfo
)
Order by userid DESC

 

 

Method 2:

Select top 10 * From userinfo where userid not in

(Select top 10 UserId from UserInfo)

 

 

Method 3:

Select top 10 * From userinfo where userid>
(Select max (userid) from

(Select top10 UserId from UserInfo order by UserId))

 

 

Method 4 (only available in Sqlserver 2005 ):

Select * from (select row_number () over

(Order by UserId) as RowId, * from UserInfo) U
Where U. RowId between 10 and 20

 

In SQL Server, there are several other writing methods, not one by one. In the four methods, the last two methods are more efficient than the first two methods, but the fourth method can only be written in SqlServer 2005.

 

Before reading the practice of getting 11th to 20th records in Oracle, let's look at some strange things that people encounter when using RowNum.

The table is also UserInfo, with 30 data records

Select t. * from userinfo t where rownum> 10

Query results:

 

In theory, we should have 20 pieces of data, right? Where is the problem?

 

Because ROWNUM is a pseudo column added to the result set, that is, a column added after the result set is found (the emphasis here is that a result set must be created first ). In short, rownum is the serial number that matches the condition result. So can we understand that rownum> 10 has no data:

ROWNUM is a sequence in which oracle databases read data from data files or buffers. If it obtains the first record, the rownum value is 1, the second record is 2, and so on. If you use>,> =, =,... and, because the rownum of the first record obtained from the buffer or data file is 1, it is deleted and then removed. However, its rownum is still 1 and deleted, and so on. The final query result is null.

 

Let's look at the following SQL statement:

Select t. * from userinfo t where rownum! = 10

 

Query results:

 

 

The results are not 21, but 9. It can be understood that the rownum of the record after rownum is 9 is 10, because the condition is! = 10, so remove it. Then add the record, and the rownum is 10. Keep doing this and there are only nine results.

If you change the following condition to where rownum> 1, no data is found. If it is where rownum> 0 or where rownum> = 1, all data can be queried. The reason is simple: rownum is added after the queried result set. It always starts from 1.

Between 1 and 20 or between 0 and 20 can find the result, but between 2 and 20 can not get the result, the same reason, because rownum always starts from 1.

Therefore, to retrieve the 11th to 20th records in the UserInfo table, write as follows:

Select * from
(Select rownum as rn, t. * from userinfo t where rownum> 0)
Where rn between 10 and 20

Query results:

Of course, you can also write it like this:

Select * from UserInfo where rownum <20
Minus
Select * from UserInfo where rownum <10

This writing method is not as efficient as the previous one.

 

But it cannot be written like this:

Select t. * from UserInfo t where rownum between 10 and 20
Select t. * from UserInfo t where rownum> 10 and rownum <= 20

None of the above two write methods can obtain data.

 

For SQL Server and Oracle, if there is a better way to write 11th to 20th records in the table, you can paste them out and learn.