来看看关于C# row_number实现数据库分页代码吧。ROW_NUMBER生成以一个以表里的某一个列为排序[这里的排序效果与select * from table order by id 这种效果一样]的虚拟列(看过也可以用getdate()),并自动生成编号。
比如select ROW_NUMBER() over (order by id) as RowId,* from adonet查出的结果是以ID排序,在前面多加一列RowId。
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 10 000009 onepc9 cnblogs - 9 2011-10-25 22:55:04.093 select ROW_NUMBER() over (order by id desc) as RowId,* from adonet ---ID为desc序排列 RowId id name addr datet 1 046606 00000 cnblogs - 46606 2014-06-22 17:25:04.093 2 046605 11 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 10 046597 onepc46597 cnblogs - 46597 2014-06-22 12:55:04.093 |
select ROW_NUMBER() over (order by id desc) as RowId,* from adonet order by id或者rowid 这里排的话,查下表就清楚了。
MSSQL存储过程
--select id,name,addr,datet from adonet --select id,name,addr,datet,ROW_NUMBER() over (order by datet) aaa from adonet order by aaa desc --select GETDATE() --select * from (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, --分页大小 @page int --当前第几页 --@outfynum int output with 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 |
执行
row_fy 100,1
---------------------------------------------------------------------------------------------------------------------------------
SQL 存储过程示例:
create procedure SP_ViewAllProductsInfo_v2
(
@GSID int,
@PageIndex int,
@PageSize int
)
as
begin
select top (@PageSize) * from
(
select row_number() over (order by ID desc) as RowNumber,*
from qy_spzs where GsID=@GSID and del=0
)TempTable
where RowNumber>@PageSize*(@PageIndex-1)
end