(i) line number display and sorting
line number of 1.SQL server
A.sql 2000 uses identity (int,1,1) and temporary tables to display line 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 used directly to display line numbers, and of course you can use SQL 2000 's identity
SELECT
Row_number () over (Dataid) as RowNum,
[Dataid]
From DATAS;
Here, if you add a sort function, sort and add line numbers first.
2.ORACLE line number display
Using RowNum
SELECT
RowNum,
[Dataid]
From DATAS
ORDER BY Dataid
Note: First add line number and then sort, if you want to sort well and then add line number will use subqueries
3. Take the first n piece of data
A.sql Edition
Select top N [dataid] from DATAS
B.oracle Edition
SELECT
[Dataid]
From DATAS where rownum<=n
Among them, n>=1
Oracle's rownum cannot be applied to more than, only rownum= 1, or the natural number of <= greater than 1
(ii) Several ways to divide SQL pages
Take 10 data per page For example, query the third page of data, that is, 21-30 of these records
1. Paging Scheme one: (Use not in and select top pagination)
Statement form:
Copy Code code as follows:
SELECT Top 10 *
From DATAS
WHERE Dataid not in
(SELECT top Dataid
From DATAS
ORDER by Dataid)
ORDER BY Dataid
2. Paging Programme II: (with ID greater than number and select top pagination)
Statement form:
Copy Code code as follows:
SELECT Top 10 *
From DATAS
WHERE ID >
(SELECT MAX (dataid)
From (SELECT dataid
From DATAS
Order by Dataid) as T)
ORDER BY Dataid
3. Paging Programme III
Copy Code code as follows:
Select Top Dataid from
(SELECT Top 30
[Dataid]
From DATAS
ORDER BY dataid Desc) A
ORDER BY Dataid
4. Paging Scheme four: (using SQL Cursor stored procedure paging)
Copy Code code as follows:
CREATE PROCEDURE SqlPager
@sql nvarchar (8000),--query string
@curpage int,--nth page
@pagesize INT--Number of rows per page
As
SET NOCOUNT ON
declare @p int,--p is the ID of the cursor
@rowcount int
EXEC sp_cursoropen @p output, @sql, @scrollopt =1, @ccopt =1, @rowcount = @rowcount output
Select Ceiling (1.0* @rowcount/@pagesize) as total number of pages, @rowcount as rows, @curpage as current page
Set @curpage = (@curpage-1) * @pagesize +1
exec sp_cursorfetch @p,16, @curpage, @pagesize
EXEC sp_cursorclose @p
SET NOCOUNT OFF
Methods are summarized as follows:
Code based on the pubs template database
In SQL, this is generally the case for both methods.
1. Use of temporary tables
You can use SELECT into to create a temporary table, in the first column, add identify (int,1,1) as the line number,
So in the resulting temporary table, the result set has the line number. It is also the most efficient method at present.
This method cannot be used for views
Copy Code code 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. Using a self-connection
Do not use temporary tables, in SQL statements, dynamic sorting. The connection used for this method is a self connection, which is generally
Greater than
Copy Code code 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 (*)
Run Result:
Rank au_lname au_fname
----------- ---------------------------------------- --------------------
1 Bennet Abraham
2 Blotchet-halls Reginald
3 Carson Cheryl
4 DeFrance Michel
5 del Castillo Innes
6 Dull Ann
7 Greene Morningstar
... ....
Disadvantages:
1. Use a self-join, so this method does not work with a large number of rows. It works with hundreds of rows.
For large tables, be sure to use an index to avoid a wide range of searches, or use the first method.
2. Cannot handle duplicate values normally. When you compare duplicate values, discontinuous row numbers appear.
If you do not want this behavior, you can hide the row sequence when you insert the results in the spreadsheet, but instead use the spreadsheet number.
Or use the first method
Advantages:
These queries can be used in view and result formatting
Inserting the line number in the result set, you can now cache the result collection and then use DataView to add the filter condition
Rownum>pageindex*pagesize and rownum<= (pageindex+1) *pagesize
You can achieve fast paging, and no matter what your page data-bound controls are (Datalist,datagrid, or repeate).
If you are using a DataGrid, it is not recommended to use this technique. Because the DataGrid's paging efficiency is almost the same as it is.
