C# row_number实现数据库分页

来看看关于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

 

转载于:https://www.cnblogs.com/wangxinming/archive/2012/11/10/2763969.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值
>