Network Studio training materials for the third time after the summer vacation (Summary of Several SQL pages)

Address: http://www.cnblogs.com/qq731109249/archive/2012/10/23/2736278.html

In the actual development process, if the number of records is very large, it would be terrible to query and fill the data table with SQL statements directly. In addition, the website performance and server performance are greatly consumed.

Two common mistakes:

1) When querying real data, all the data that meets the conditions will be filled in the datatable, and then inProgramDisplays part of the data according to the conditions.

2) in statistical data, when obtaining the number of records that meet the condition, it also fills all the data that meets the condition into the datatable, and then uses the datatable instance rows. count attribute to obtain the number of records.

The result is extremely low efficiency. If the data size is too large, the data you need may not be displayed for a long time. Therefore, you should use paging query to display the data. Querying by PAGE means that only the required data is returned each time, instead of submitting all the data from the database every time. This reduces the data transfer between the program and the database, it can also improve the program performance.

In general, when the data volume is large, it needs to be displayed by page. In this way, two parameters are returned for the query result set: the currently displayed page number pageindex and the number of records per page.

The data table used. For example, the table ID is the primary key.

The following describes how to query the information of all users in the forward order of IDs. The SQL statement is as follows:

View code?

1	Select *From T_personOrder By IDASC;

Now we paging the data. The paging rule is that the page size is five pieces of data, so we can use the SQL statement to complete the data query on the 1st page:

View code?

1	Select Top 5 *From T_personOrder By IDASC;

In this way, we can retrieve the 5 data entries to be displayed on the 1st page. But how can we compile SQL statements to display data on pages 2, 3, 4?

If we use 1-5 records for the data retrieved on page 6, the data on page 6 should be 6-10 records. How should we do it? There are two methods: the first is to extract all the data and fill it in the able, and then start from 5 through I in the for loop, and the method I is less than 10 shows data. The disadvantages of this method have been described earlier. The second method is to filter the data in the database. In this case, the not in SQL statements can be used in a good way.

So how to use not in to display the data on the 2nd page? The SQL statement is as follows:

View code

  1   select   top   5   *   from  t_person  where  id  not   in   2  ( 3   select   top   5  id  from  t_person  order   by  id  ASC   4 )  order   by  id  ASC ; 

Here, a subquery is used to first display the data number on the 1st page, and then use not in to discharge records 1-5 from the data, showing 6-10 data records.

Because the data ID starts from 1, records with IDs 1-5 are displayed on page 1, records with IDs 6-10 are displayed on page 1, and records with IDs 11-15 are displayed on page 1st, the SQL statement for pushing data on page N is: n is the page on which data is displayed,

 1   Select   Top   5   *   From T_person Where ID Not   In   2 ( 3   Select   Top (N -  1 ) 5 ID From T_person Order   By ID ASC   4 ) Order   By ID ASC ;

In this way, the page data is displayed based on parameter n.

Another important knowledge point is how to calculate the total number of pages on the data page. If there are 20 data entries, if there are 5 data entries on each page, it is obviously divided into 4 pages. However, if the number of records is 21, it is obvious that there should be 5 pages. There is a formula. If there are m pieces of data in total and N pieces of data (both m and n are greater than 0) are displayed on each page, the page number of all records must be displayed: page = (M % n) = 0? (M/N) :( M/N + 1 );

The second paging method:

The row_number function in SQL is used to mark each record in the returned record set.

Because we need to delete the database, the IDs in the database table may be discontinuous. In the above method, we mainly use IDs for sorting and do not require too many operations. However, the following method requires a continuous id value to query data.

First look at an SQL statement:

1 Select * From 2(3 Select * FromT_person4)AsA5 WhereID>5 AndID<=10
 

In this way, we can still retrieve the data of the corresponding number of pages as required. The SQL statement is as follows: n is the page number of the displayed data.

  1   select   *   from   2  ( 3   select   *   from  t_person  4 )  as  A  5   where  id > (n -  1 )  *   5   and  id  <=  n  *   5  

At this time, we can select the data of the corresponding page number based on the parameter n, but we can quickly find that this page is highly dependent on the ID consecutively. Therefore, when we delete data, the IDs in the database may be discontinuous. Therefore, when we perform paging in the database, we need to number the data, in this case, we need to use the row_number function in SQL,

The SQL statement queried using the row_number function and the display result are as follows:

We can easily find that there is a RL column in the forward order of IDs.

Therefore, the complete SQL paging statement is: n is the page number of the pages we pass in. By default, each page displays 5 data bits.

  1   select   *   from   2  ( 3   select   *   from  t_person  4 )  as  A  5   where  id > (n -  1 )  *   5   and  id  <=  n  *   5  

In this way, we can create a paging storage process, paging the data in the database, and then call the program.

Finally, I will share a better paging stored procedure on the Internet:

1 Create Procedure Getpagedata 2 ( 3 @ Tablename Varchar ( 30 ), -- Table Name 4 @ Idname Varchar ( 20 ), -- Table primary key name 5 @ Pageindex Int , -- Current page number 6 @ Pagesize Int -- Size per page 7 ) 8 As 9 If @ Pageindex > 0 10 Begin 11 Set Nocount On 12 Declare @ Pagelowerbound Int , @ Startid Int ,@ SQL Nvarchar ( 225 ) 13 Set @ Pagelowerbound = @ Pagesize * ( @ Pageindex - 1 ) 14 If @ Pagelowerbound < 1 15 Set @ Pagelowerbound = 1 16 Set Rowcount @ Pagelowerbound 17 Set @ SQL = N ' Select @ startid = [ ' + @ Idname + ' ] From ' + @ Tablename + ' Order ' + @ Idname 18 Exec Sp_executesql @ SQL , N ' @ Startid int output ' , @ Startid Output 19 Set Rowcount 0 20 Set @ SQL = ' Select top ' + Str ( @ Pagesize ) + ' * From ' + @ Tablename + ' Where [ ' + @ Idname + ' ]> = ' + Str ( @ Startid ) + ' Order [ ' + @ Idname + ' ] ' 21 Exec ( @ SQL ) 22 Set Nocount Off 23 End