How to perform dynamic numbers in SQL, plus trip, is a classic problem in the database query. I put the current method, share the skills code based on the Pubs model database in SQL, usually this Two methods. 1. Use the temporary table to create a temporary table using SELECT INTO, in the first column, add Identify (int, 1, 1) as a line number, in which the result set has a line number It is also the highest efficiency method. This method cannot be used for view 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. Use the self-connection without a temporary table, in the SQL statement, dynamically sort. This method used by this method is self-connected, the connection relationship is generally greater than, 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 results: 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 the self-join, so this method is not suitable for processing a large number of rows. It is suitable for handling hundreds of lines. For large tables, you must use an index to avoid a wide range of search, or use the first method. 2. Do not properly process the repetition value. When the repetition value is compared, the discontinuous row number will appear. If this phenomenon does not want to appear, you can hide the sequence of sequences in the spreadsheet, but use the spreadsheet number. Or use the first method advantages: 1. These queries can be used in the view and result format settings inserted in the result set, now you can cache the result collection, then use DataView, add filter criteria Rownum> PageIndex * Pagesize And rownum <= (PageIndex 1) * PageSize can achieve fast paging, and no matter what your page data binding control is (DataList, DataGrid, or REPEATE). If you are using DataGrid, it is recommended not to use this technology. Because DataGrid's paging efficiency is similar to it.
