[SQL Server] problem summary 6-extracting row number row_number Paging

 

Different methods in SQL 2000 and 2005

 

 

Let's talk about

 

Create Table # temp1
(
Id int not null,
Code varchar (10) Not null
Primary Key (ID, code)
)

Insert into # temp1 (ID, code) values (1, '01 ')
Insert into # temp1 (ID, code) values (3, '01 ')
Insert into # temp1 (ID, code) values (100, '01 ')
Insert into # temp1 (ID, code) values (5, '02 ')

-- The expected result is that the ID in the table is updated to 1, 2, 3, and 4 (the specific ID in the ID is 1, and the ID is 2, it doesn't matter)
-- Requirement: Under SQL2000, temporary tables or other tables cannot be used.
/*
1, 01
2,01
3, 02
4,01
*/

Select * from # temp1

Drop table # temp1

Method 1: declare @ num int
Set @ num = 0
Update # temp1 Set ID = @ num, @ num = @ num + 1 Method 2: Select No = (select count (1) from # temp1 where ID <=. ID ),*
From # temp1

--------------------------------------------------------------

 

Method Under 2005

 

Table
Col1 col2 col3
A B type
A c type
A d type
K f type
K c type
K G type
How to implement the following functions:
Col1 col2 col3
A B type A1
A c type A2
A d type A3

K f type A1
K c type A2
K G type A3

-------------------------

 

 

If not object_id (n'tempdb .. # 1') is null
Drop table #1
Go
Create Table #1 ([col1] nvarchar (1), [col2] nvarchar (1), [col3] nvarchar (50 ))
Insert #1
Select n 'A', N 'B', n' type a' Union all
Select n 'A', N 'C', n' type a' Union all
Select n 'A', N 'D', N 'Type a' Union all
Select n 'k', N 'F', N 'Type a' Union all
Select n 'k', N 'C', n' type a' Union all
Select n 'k', N 'G', N 'Type'
Go

 

Select *,
Row = [col3] + rtrim (row_number () over (partition by [col1] Order by [col3])

From #1

 

/*
Col1 col2 col3
A B type A1
A c type A2
A d type A3
K f type A1
K c type A2
K G type A3

*/

 

------------------------------------

Syntax:
Row_number () over ([<partition_by_clause>] <order_by_clause>)
Note:
The order by clause determines the order in which a unique row_number is allocated for rows in a specific partition.
Parameters
<Partition_by_clause>
Partition the result set generated by the from clause into the partition where the row_number function is applied.
<Order_by_clause>
Determine the order in which the row_number value is allocated to the rows in the partition. For more information, see the order by clause (TRANSACT-SQL ).
For more information, see the over clause (TRANSACT-SQL ).
Example
1. The following example returns the row_number of the salesperson in adventureworks based on the sales amount from the beginning to the present.
Use adventureworks
Go
Select C. firstname, C. lastname, row_number () over (order by salesytd DESC) as 'row number', S. salesytd, A. postalcode
From sales. salesperson s join person. Contact C on S. salespersonid = C. contactid
Join person. Address a on A. addressid = C. contactid
Where territoryid is not null and salesytd <> 0
2. In the following example, rows with a row number of 50 to 60 (inclusive) are returned and sorted by orderdate.
Use adventureworks;
Go
With orderedorders
(Select salesorderid, orderdate,
Row_number () over (order by orderdate) as rownumber
From sales. salesorderheader)
Select *
From orderedorders
Where rownumber between 50 and 60;
3. Implement paging storage process (data is fixed to a table)
 
Set ansi_nulls on
Go
Set quoted_identifier on
Go
-- ===================================================== ======
-- Author: cxw
-- Create Date: 2006/11/14
-- Description: Use the row_number function to implement paging storage.
 
-- Exec dividepageforrow_number 2, 1 ,'',''
 
-- ===================================================== ======
Create procedure dividepageforrow_number
 
@ Ipagesize int -- number of pages per page
, @ Iindexpage int -- page number
, @ Vorderby varchar (50) -- Sort Field
, @ Irowcount int -- total number of records
 
As
Begin
Set nocount on;
With pagetable
(
Select row_number () over (order by reasonid) tid, * from
)
Select *
From pagetable
Where TID between (@ iIndexPage-1) * @ ipagesize + 1) and @ ipagesize * @ iindexpage
 
Set nocount off;
 
End
Go