Sorting and paging of SQL row numbers (an alternative implementation of inserting custom page numbers in SQL queries)

(1) display and sort row numbers

1. SQL Server row number

A. SQL 2000 uses identity (int,) and temporary tables to display row numbers.
SELECT
Identity (int, 1, 1) as rownum,
[DataID]
INTO #1
FROM DATAS
Order by DataID;
SELECT * FROM #1
B. SQL 2005 provides a very useful function row_number (),
Can be directly used to display the row number, of course, you can also use SQL 2000 identity
SELECT
Row_number () over (order by DataID) as rownum,
[DataID]
From datas;
If the sorting function is added, sort the data before adding the row number.

2. ORACLE row number display

Use ROWNUM
SELECT
ROWNUM,
[DataID]
FROM DATAS
Order by DataID
Note: add the row number before sorting. If you want to sort it and add the row number, use the subquery.

3. Obtain the first n data entries
A. SQL
Select top n [DataID] from DATAS
B. ORACLE
SELECT
[DataID]
From datas where ROWNUM <= n
Where n> = 1
The ROWNUM of ORACLE cannot be greater than or equal to ROWNUM = 1, or a natural number <= greater than 1

(2) SQL paging Methods
Take 10 data records on each page as an example to query the data on the third page, that is, the records from to 30.
1. Paging solution 1: (use Not In and select top pages)
Statement format:
Copy codeThe Code is as follows:
Select top 10 *
FROM DATAS
WHERE DataID NOT IN
(Select top 20 DataID
FROM DATAS
Order by DataID)
Order by DataID

2. Paging solution 2: (use the ID greater than the number of pages and select top pages)
Statement format:
Copy codeThe Code is as follows:
Select top 10 *
FROM DATAS
Where id>
(Select max (DataID)
FROM (select top 20 DataID
FROM DATAS
Order by DataID) as t)
Order by DataID

3. Paging solution 3
Copy codeThe Code is as follows:
Select top 10 DataID from
(SELECT top 30
[DataID]
FROM DATAS
Order by dataid desc)
Order by DataID

4. Paging solution 4: (using SQL cursor Stored Procedure paging)
Copy codeThe Code is as follows:
Create procedure SqlPager
@ SQL nvarchar (8000), -- query string
@ Curpage int, -- page N
@ Pagesize int -- number of lines per page
As
Set nocount on
Declare @ P int, -- P is the cursor id
@ Rowcount int
Exec sp_cursoropen @ P output, @ SQL, @ scroalopt = 1, @ ccopt = 1, @ rowcount = @ rowcount output
Select ceiling (1.0 * @ rowcount/@ pagesize) as total number of pages, @ rowcount as total number of lines, @ curpage as current page
Set @ curpage = (@ curpage-1) * @ pagesize + 1
Exec sp_cursorfetch @ P, 16, @ curpage, @ pagesize
Exec sp_cursorclose @ P
Set nocount off


The methods are as follows:
Code is based on the pubs model database
In SQL, these two methods are generally used.
1. Use a temporary table
You can use select into to create a temporary table. In the first column, add Identify (int,) as the row number,
In this way, the result set has a row number in the generated temporary table, which is also the most efficient method currently.
This method cannot be used for views.
Copy codeThe Code is as follows:
Set nocount on
Select IDentify (int, 1, 1) 'roworder', au_lname, au_fname into # tmp from authors
Select * frm # tmp
Drop table # tmp

2. Use self-connection
Temporary tables are not used. In SQL statements, the data is dynamically sorted. The connection used in this method is self-join, and the connection relationship is generally
Greater,
Copy codeThe Code is as follows:
Select rank = count (*), a1.au _ lname, a1.au _ fname
From authors a1 inner join authors a2 on a1.au _ lname + a1.au _ fname> = a2.au _ lname + a2.au _ fname
Group by a1.au _ lname, a1.au _ fname
Order by count (*)

Running result:
Rank au_lname au_fname
-----------------------------------------------------------------------
1 Bennet Abraham
2 Blotchet-Hils Reginald
3 Carson Cheryl
4 DeFrance micel
5 del Castillo Innes
6 Dull Ann
7 Greene Morningstar
.......
Disadvantages:
1. Use self-join, so this method is not applicable to processing a large number of rows. It is applicable to processing hundreds of rows.
For large tables, you must use indexes to avoid large-scale searches, or use the first method.
2. duplicate values cannot be processed normally. When comparing duplicate values, discontinuous row numbers appear.
If you do not want this to happen, you can hide the sorting column when inserting results in the workbook. Instead, you can use the workbook number.
Or use the first method.
Advantages:
These queries can be used in view and result format settings.
The row number is inserted in the result set. Now you can cache the result set, and then use DataView to add filtering conditions.
RowNum> PageIndex * PageSize And RowNum <= (PageIndex + 1) * PageSize
You can achieve fast paging, and no matter what the page data binding control is (DataList, DataGrid, or Repeate ).
If you are using a DataGrid, we recommend that you do not use this technology. Because the paging efficiency of the DataGrid is similar to that of it.