[Note] SQL Difference Analysis in popular databases: "Limit the number of rows in the result set"

Preface:Recently, the project involved three types of databases: sqlserver, Oracle, and MySQL. So we will find these materials and record them as notes for future reference.

Limit the number of rows in the result set

Only some rows in the result set need to be retrieved for data retrieval, such as "top three students with search scores" and "employee information with search salary levels between 3rd and 7th ", this function is called "Limit the number of rows in the result set ". Although the mainstream database systems provide methods to limit the number of rows in the result set, there are great differences in both syntax and usage. Even different versions of the same database system (such as mssqlserver2000 and mssqlserver2005) are different.

 

MySQL

MySQL provides the limit keyword to limit the returned result set. limit is placed at the end of the SELECT statement. The syntax is "Limit first row number, maximum number of result sets to be returned ", for example, the following SQL statement returns a maximum of five records starting from the second row (the row number starts from 0) in descending order of wages:

Select * From t_employee order byfsalary DESC limit 2, 5 // The second row to the seventh row is returned.

Select * From t_employee order byfsalary DESC limit // return the first five

 

Sqlserver2000

Sqlserver2000 provides the top keyword to return the first N records in the result set. Its syntax is "select top limit result set quantity field list the remaining part of the statement under selec ", for example, the following SQL statement is used to retrieve the information of the top five employees (from high to low according to their salaries:

Select top 5 * From t_employee orderby fsalary DESC

 

Sqlserver2000 does not directly provide range-based functions such as "retrieving 10 pieces of data starting from 5th rows" and "retrieving data from the fifth row to 12th rows, however, you can use other methods to implement it. The most common method is to use subqueries, for example, if you want to retrieve the information of three people from the sixth place in descending order of wages, you can retrieve the first five primary keys, the first three employees of the five employees are excluded from the search. The SQL statement is as follows:

Select top 3 * From t_employee

Where fnumber not in

(Select top 5 fnumber from t_employeeorder by fsalary DESC)

Order by fsalary DESC

 

Sqlserver2005

Sqlserver2005 is compatible with almost all sqlserver2000 syntaxes, so you can use the method mentioned above to limit the number of rows in sqlserver2005, however, sqlserver2005 provides new features to better limit the number of rows in the result set. This new feature is the window function row_number (). The row_number () function can calculate the row number of each row in the result set (counting from 1). Its syntax is as follows:

Row_numberover (sorting rules)

 

For example, execute the following Sol statement:

Selectrow_number () over (order by fsalary), fnumber, fname, fsalary, Fage from t_employee // return the sorted row number

 

Row_number () cannot be used in the where statement. You can use a subquery to return data from 3rd rows to 5th rows:

Select * from

(

Select row_number () over (order by fsalary DESC) as rownum,

Fnumber, fname, fsalary, Fage fromt_employee

) As

Where a. rownum> = 3 and A. rownum <= 5

 

Oracle

Oracle supports Window Function row_number (), which is the same as that in sqlserver2005. For example, execute the following SQL statement:

Select * from

(

Select row_number () over (Order byfsalary DESC) row_num,

Fnumber, fname, fsalary, Fage fromt_employee

)

Where a. row_num> = 3 and A. row_num <= 5

 

Note: rownum is a reserved word in Oracle. Therefore, replace rownum in sqlserver2005 with row_num. The as keyword cannot be used when defining table aliases in Oracle, so as is also removed here, oracle supports the standard function row_number (). However, Oracle provides more convenient features to calculate row numbers, so you do not need to calculate row numbers in Oracle, oracle adds a default row number column for each result set. The column name is rownum. For example, execute the following SQL statement:

Select rownum, fnumber, FNAS. E, fsalary, fagefrom T _ employee

After the execution is completed, we can see the following execution results in the output results:

With rownum, you can easily obtain the first n data rows in the result set. For example, you can execute the following SQL statement to obtain the information of the first 6 employees sorted by salary in descending order:

Select * From _ employee where rownum <= 6 order by fsalary DESC

 

The following SQL statement can easily implement the "retrieve the third to fifth employee information in the order of salary from high to low" function:

Selectrownum, fnumber, fname, fsalary, Fage from t_employee

Where rownum between 3 and 5

Order by fsalary DESC // The execution result is empty. The conclusion is as follows.

 

Review the meaning of rownum: rownum is the row number of each row in the result set (counted from 1 ). For the following SQL:

Select * from_employee where rownum <= 6 order by fsalary DESC

When performing a search, the rownum of the first data is 1, which is put into the search result because it complies with "whererownum <= 6". When the second data is retrieved, its rownum is 2 because it complies with the "Where

Rownum <= 6 "is included in the search. And so on until the seventh line. Therefore, this SQL statement can be used to retrieve information about the third to fifth employees in the order of salary from high to low.

 

For this SQL statement:

Selectrownum, fnumber, fname, fsalary, Fage from t_employee

Where rownum between 3 and 5

Order by fsalary DESC

When performing a search, the rownum of the first data is 1, because it does not conform to "whererownum between 3 and 5", so it is not put into the bitwise result, when the second data is retrieved, the rownum of the second data is still 1 because the first data is not placed in the result set, therefore, because it does not conform to 'whererownum <= 6 ", it is not put into the result of the planting. When the third data is retrieved, because the first and second data are not put into the result set, therefore, the rownum of the third data is still 1, instead of 3, which is not in the search result because it does not conform to "whererownum <= 6. In this way, all data rows are not placed in the result set.

 

Therefore, to use rownum to retrieve the third to fifth employee information in the order of salary from high to low, you must use the window function row_number ().

 

DB2

DB2 supports Window Function row_number (), which is the same as that in sqlserver2005 and Oracle. For example, execute the following SQL statement:

Select * from

(

Select row_number () over (Order byfsalary DESC) row_num,

Fnumber, fname, fsalary, Fage fromt_employee

)

Where a. row_num> = 3 Anda. row_num <= 5

 

In addition, DB2 also provides the fetch keyword to extract the first n rows of the result set. Its syntax is "fetch first number rows only ", for example, we can execute the following SQL statement to obtain the information of the first six employees sorted by salary in descending order:

Select * From t_employee order byfsalary DESC fetch first 6 rowsonly

 

Note: The fetch clause must be placed after the orderby statement.

 

DB2 does not directly provide range-based functions such as "retrieving 10 pieces of data starting from 5th rows" and "retrieving data from the fifth row to 12th rows, however, you can use other methods to implement it. The most common method is to use subqueries, for example, if you want to retrieve the information of three people from the sixth place in descending order of wages, you can retrieve the first five primary keys, the first three employees were excluded from the search.

Select * From t_employee

Where fnumber not in

(

Select fnumber from t_employee

Order by fsalary DESC

Fetch first 5 rows only

)

Order by fsalary DESC

Fetch first 3 rows only

 

Database Paging

the core technology used to implement database paging is to "Limit the number of rows in the result set". Assume that the number of data entries displayed on each page is pagesize, and the current page number (starting from 0) is currentlndex, so we only need to query the pagesize data starting from the pagesize * currentindex to get the data in the current page, when the user clicks the previous page, set currentlndex to Currentlndex-1, then re-search. When the user clicks the next page, set currentlndex to currentlndex + 1, and then re-search. When you click the homepage button, set currentlndex to 0 and then retrieve it again. When you click the homepage, set currentlndex to "Total number of entries/pagesize", and then retrieve it again.