SQL inserts a line number in the query - another implementation of custom paging

xiaoxiao2021-04-10  388

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.

转载请注明原文地址:https://www.9cbs.com/read-133349.html

New Post(0)