The question of how to dynamically number rows in SQL, plus line numbers, is a classic problem in database queries. I'll sort out the present method and share the skills. The code is based on the pubs boilerplate database. In SQL, these two methods are generally:
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 that 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
Code:
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 the connection.
Code:
<
Code>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 |
carson< /td> |
cheryl |
4 | TD style= "BORDER:0;PADDING:0;" >defrance
michel |
5< /td> |
del Castillo |
innes |
dull | " TD style= "BORDER:0;PADDING:0;" >ann
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 result in the spreadsheet, using the spreadsheet number, or the first method
Advantages:
1. 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.