SQL database paging sorting records

If you want to reprint it, Please retain your copyright:
/*
* Description: SQL database data paging technology
* Auther: chongchong2008-innocent blue
* MSN: chongchong2008@msn.com
* QQ: 154674958
* Blog: chongchong2008.cnblogs.com
* Dates: 2010-01-13
* Copyright: chongchong2008 Yichang Hubei China
*/

The SQL database is sorted in descending order as follows:

 

Method 1: Select top min (max) Select top Method

Problem: there was a bug on the first page.
Problem: you cannot sort multiple fields. The primary key must be required. That is, the unique sorting field cannot be repeated. The primary key type must be Int. If multiple fields are sorted, the primary key must be continuous, cannot be broken.

Basic SQL statement for reading data from the second pageCode, 10 data entries per page
For example, descending order:

Select top 10 * From table_test where table_id <(
Select min (table_id) from (
Select top 20 table_id from table_test order by table_id DESC
) T
) Order by table_id DESC

 

Method 2: select in select top Method

The last page contains a bug.
The problem is that a primary key is required, that is, a unique field that cannot be repeated. The primary key type can be any. Multiple fields can be sorted. Multiple fields can be sorted in their own way.

The basic SQL code for reading the second page of data, with 10 data records per page

For example, descending order:

Select * From table_test where table_id in (
Select top 10 table_id from (
Select top 10 table_id, table_orderid from table_test order by table_orderid DESC, table_id DESC
) T order by table_orderid ASC, table_id ASC
) Order by table_orderid DESC, table_id DESC

Sort in ascending order as follows:

Select * From table_test where table_id in (
Select top 10 table_id from (
Select top 10 table_id, table_orderid from table_test order by table_orderid ASC, table_id ASC
) T order by table_orderid DESC, table_id DESC
) Order by table_orderid ASC, table_id ASC

 

The first method is actually very good. If you do not require other non-primary key sorting, it is definitely your first choice.

However, when sorting non-primary key fields is required, the second method must be used, and multi-field sorting can also be used. This sorting method is mainly described below.
This sorting method requires that the sorting method of the sorting fields be reversed and used when you write an SQL statement. To support automatic reversal of Multiple Fields, I have written a function to facilitate the call.

As follows:

-- ===================================================== ======
-- Description: reverse the sorting method of the sorting field.
-- Author: chongchong2008-innocent blue
-- QQ: 154674958.
-- Email: yc_mingzi@tom.com
-- MSN: chongchong2008@msn.com
-- Create Date: 2009-11-13
-- ===================================================== ======
Alter function [DBO]. [fn_chongchong_reversorder]
(
@ Orderfields nvarchar (255)
)
Returns nvarchar (256)
As
Begin

Declare @ sourcestring nvarchar (255)
Declare @ resultstring nvarchar (255)
Declare @ tempstring nvarchar (32)
Declare @ index int
Declare @ Len int
Declare @ len2 int

Set @ sourcestring = @ orderfields + ',';

-- Calculate the length
Set @ Len = Len (@ sourcestring)
Set @ Index = charindex (',', @ sourcestring)
Set @ resultstring =''

-- Loop
While (@ index! = 0)
Begin
Set @ tempstring = substring (@ sourcestring, 1, @ index-1)

If (charindex ('desc', @ tempstring )! = 0)
Set @ tempstring = Replace (@ tempstring, 'desc', 'asc ')
Else
Set @ tempstring = Replace (@ tempstring, 'asc ', 'desc ')

Set @ resultstring = @ resultstring + @ tempstring + ',';

Set @ sourcestring = substring (@ sourcestring, @ index + 1, @ len-@ index)
Set @ Index = charindex (',', @ sourcestring)
End

Set @ len2 = Len (@ resultstring)
Set @ resultstring = substring (@ resultstring, 1, @ len2-1)

Return @ resultstring

End

This SQL function provides the following functions: (used to reverse the sorting of any number of sorting fields)

Declare @ strsource nvarchar (255)
Set @ strsource = DBO. fn_chongchong_reversorder ('test _ orderid ASC, test_otherid DESC, test_date DESC ')
Print @ strsource

Returns the output test_orderid DESC, test_otherid ASC, test_date ASC.

 

Use the second method to write the following code:

Set @ orderkey = @ orderkey + case @ ordertype when 0 then 'asc 'else' DESC 'end;
Set @ orderkeyrevers = DBO. fn_chongchong_reversorder (@ orderkey); -- reverse
Set @ orderfields = Replace (replace (@ orderkey, 'asc ', ''), 'desc',''); -- remove the sorting token.

Set @ strsql = 'select' + @ fieldnamelist + 'from' + @ tablename
+ @ Where1 + @ primarykey + 'in ('
+ 'Select top' + Cast (@ pagesize as varchar) + @ primarykey + 'from ('
+ 'Select top' + Cast (@ pagesize * (@ pageindex) as varchar) + @ orderfields + 'from' + @ tablename
+ @ Where2 + 'ORDER BY' + @ orderkey
+ ') T order by' + @ orderkeyrevers
+ ') Order by' + @ orderkey;

Note that this method has a bug when you flip to the last page. The following section is used to fix this bug.

If (@ pageindex >=@ totalpage) -- modify the next page
Set @ strsql = 'select' + @ fieldnamelist + 'from' + @ tablename + @ where1 + @ primarykey + 'in ('
+ 'Select top' + Cast (@ lastrecords as varchar) + @ primarykey + 'from' + @ tablename + 'ORDER BY' + @ orderkey + + case @ ordertype when 0 then 'desc' else 'asc 'end
+ ') Order by' + @ orderkey + case @ ordertype when 0 then 'asc 'else' DESC 'end;

 

OK, that's it...