For example, the result identified by the Select Row_number () over (ordered by ID) as rowid,* from ADONet is sorted by ID, with an additional column of RowId in front.
| The code is as follows |
Copy Code |
| RowId ID Name Addr Datet 1 000000 onepc0 cnblogs-0 2011-10-25 18:25:04.093
2 000001 onepc1 cnblogs-1 2011-10-25 18:55:04.093
3 000002 onepc2 cnblogs-2 2011-10-25 19:25:04.093
4 000003 onepc3 cnblogs-3 2011-10-25 19:55:04.093
5 000004 onepc4 cnblogs-4 2011-10-25 20:25:04.093
6 000005 onepc5 cnblogs-5 2011-10-25 20:55:04.093
7 000006 onepc6 cnblogs-6 2011-10-25 21:25:04.093
8 000007 onepc7 cnblogs-7 2011-10-25 21:55:04.093
9 000008 onepc8 cnblogs-8 2011-10-25 22:25:04.093
Ten 000009 onepc9 cnblogs-9 2011-10-25 22:55:04.093 Select Row_number () over (ordered by id DESC) as rowid,* from adonet---ID is desc order RowId ID Name Addr Datet 1 046606 00000 cnblogs-46606 2014-06-22 17:25:04.093
2 046605 cnblogs-46605 2014-06-22 16:55:04.093
3 046604 onepc46604 cnblogs-46604 2014-06-22 16:25:04.093
4 046603 onepc46603 cnblogs-46603 2014-06-22 15:55:04.093
5 046602 onepc46602 cnblogs-46602 2014-06-22 15:25:04.093
6 046601 onepc46601 cnblogs-46601 2014-06-22 14:55:04.093
7 046600 onepc46600 cnblogs-46600 2014-06-22 14:25:04.093
8 046599 onepc46599 cnblogs-46599 2014-06-22 13:55:04.093
9 046598 onepc46598 cnblogs-46598 2014-06-22 13:25:04.093
Ten 046597 onepc46597 cnblogs-46597 2014-06-22 12:55:04.093 |
Select Row_number () over (the ORDER by id DESC) as rowid,* the from adonet, or RowId here, check out the table.
MSSQL Stored Procedures
| The code is as follows |
Copy Code |
| --select Id,name,addr,datet from ADONet
--select Id,name,addr,datet,row_number () (Order by Datet) AAA from ADONet ORDER BY AAA DESC
--select GETDATE ()
--select * FROM (the Select Row_number () over (order by ID) as rowid,* from ADONet) temptable
--where Rowid>=1 and rowid<=100
Use Wentest
if exists (SELECT * from sys.sysobjects where type= ' P ' and name= ' Row_fy ')
drop procedure Row_fy
Go
CREATE PROCEDURE Row_fy
@pagesize int,--Paging size
@page INT--current first few pages
--@outfynum int output
With encryption--encryption
As
--declare @temp int
SELECT * FROM
(
Select Row_number () over (order by ID) as rowid,* from
ADONet
) as TempTable
where rowid>= (@page-1) * @pagesize +1 and rowid<= @page * @pagesize |
Perform
Row_fy 100,1
