Mysql oracle and sqlserver query instance resolution by PAGE, oraclesqlserver

Mysql oracle and sqlserver query instance resolution by PAGE, oraclesqlserver

Recently, I have made a simple research on the paging query of oracle, mysql, and sqlserver2005 data, and posted their query statements for you to learn .....

(1) mysql paging Query

The paging query of mysql is the simplest. You can use the limit keyword to perform queries. The query statements are generic:

selecto.*from(sql)o limit firstIndex,pageSize

As shown in the following figure, each page displays 20 records:

Query (1-20) the 20 records

Query (21-40) the 20 records

Mysql paging query is so simple ......

(2) Paging query of sqlserver2005

Before sqlserver2005, top keywords were used for paging query, but the efficiency was low. In sqlserver2005 and later versions, row_number () was used to complete paging query, the efficiency has been greatly improved, but the SQL statements are complicated. The following describes the generic paging query method:

selecttoppageSizeo.*from(selectrow_number()over(orderbyorderColumn)asrownumber,*from(sql)asowhererownumber>firstIndex;

The following shows the number of 20 records per page:

Query (1-20) the 20 records

Query (21-40) the 20 records

Knowing the row_number function in sqlserver, paging is simple .....

(3) oracle paging Query

Next we will focus on oracle's paging query. The paging query method of oracle is relatively multiple points, ROWNUM and row_number (). Today we will mainly use two paging query statements with better efficiency.

① ROWNUM query paging generic:

select*from(selecta.*,ROWNUMrn from(sql)a whereROWNUM<=(firstIndex+pageSize))wherern>firstIndex

The following is a query statement in this way:

Query (1-21) These 20 records ***** (no records with ID = 6 exist, so the maximum queried ID is 21)

Query (22-41) These 20 records ***** (there are no records with ID = 6, so the queried ID is 22 and the maximum ID is 41)

② Row_number () Resolution function paging query method:

select*from(select*from(selectt.*,row_number()over(orderbyorderColumn)asrownumberfrom(sql)t) p wherep.rownumber>firstIndex)whererownum<=pageSize

The following shows the query results by page using row_number:

Query (1-21) These 20 records ***** (no records with ID = 6 exist, so the maximum queried ID is 21)

Query (22-41) These 20 records ***** (there are no records with ID = 6, so the queried ID is 22 and the maximum ID is 41)

In the ROWNUM query method, there is"where ROWNUM<(firstIndex+pageSize)order by idascQuery records (firstIndex + pageSize) from the data table. Therefore, if the value is small, the efficiency is very good. If the value of this value is for a form with a large amount of data, for example:select* from wyuse where rownum<(5000) order by idascIn this way, 5000 records will be selected at the beginning, and the efficiency will naturally be much slower ....

Summary

The above section describes mysql oracle and SQL Server paging queries. I hope this will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!