Shulou(Shulou.com)05/31 Report--

SQL how to achieve line number sorting and paging, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

(1) Line number display and sorting

1.SQL Server's line number A.SQL 2000 uses identity (int,1,1) and a temporary table to display the line number SELECT identity (int,1,1) AS ROWNUM. [DataID] INTO # 1 FROM DATAS order by DataID; SELECT * FROM # 1 B.SQL 2005 provides a good function row_number (), which can be used to display line numbers directly. Of course, you can also use identity SELECT row_number () over (ORDER BY DataID) AS ROWNUM of SQL 2000, [DataID] FROM DATAS If the sorting function is added here, sort first and then add the line number

The line number of 2.ORACLE is displayed using ROWNUM SELECT ROWNUM. [DataID] FROM DATAS order by DataID Note: add the line number first and then sort it. If you want to sort it well, you have to use a subquery.

3. The ROWNUM of the first n pieces of data A.SQL select top n [DataID] from DATAS B.ORACLE SELECT [DataID] FROM DATAS where ROWNUM=1 ORACLE cannot be applied to greater than, only ROWNUM=1, or (SELECT MAX (DataID) FROM (SELECT TOP 20 DataID FROM DATAS ORDER BY DataID) AS T) ORDER BY DataID

3. The copy code for paging scheme 3 is as follows: select top 10 DataID from (SELECT top 30 [DataID] FROM DATAS order by dataid desc) An ORDER BY DataID

4. Paging scheme 4: (paging using SQL's cursor stored procedure) copy the code as follows: create procedure SqlPager @ sql nvarchar (8000),-- query string @ curpage int,-- page N @ pagesize int-- rows per page as set nocount on declare @ P int,-- P is the total number of pages of the cursor id @ rowcount int exec sp_cursoropen @ P output,@sql,@scrollopt=1,@ccopt=1, @ rowcount=@rowcount output select ceiling (1.0*@rowcount/@pagesize) as The total number of rows in @ rowcount as, @ curpage as current page set @ curpage= (@ curpage-1) * @ pagesize+1 exec sp_cursorfetch @ pagesize+1 exec sp_cursorfetch @ pageSize exec sp_cursorclose @ P set nocount off

The methods are as follows: the code is based on pubs template database in SQL, generally these two methods. 1. Using temporary tables, you can use select into to create temporary tables. In the first column, add Identify (int,1,1) as the row number, so that in the resulting temporary table, the result set has a row number. It is also the most efficient method at present. This method cannot be used to copy the code code for views 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

two。 Use self-join instead of temporary tables, and sort dynamically in SQL statements. The connection used in this method is self-connection, and the connection relationship is generally greater than. The copy 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-Halls Reginald 3 Carson Cheryl 4 DeFrance Michel 5 del Castillo Innes 6 Dull Ann 7 Greene Morningstar....... Disadvantages: 1. Use self-join, so this method is not suitable for dealing with a large number of rows. It is suitable for processing hundreds of lines. For large tables, be sure to use indexes to avoid extensive searches, or the first method. two。 Duplicate values cannot be handled properly. When duplicate values are compared, discontiguous row numbers appear. If you don't want this to happen, you can hide the sort sequence when you insert the results in the spreadsheet and use the spreadsheet number instead. Or use the advantage of the first method: these queries can be used to insert row numbers in the result set in the view and result formatting, so you can cache the result set now, and then use DataView to add the filter condition RowNum > PageIndex*PageSize And RowNum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.