SQL Server2005为我们引入了一个ROW_NUMBER函数。你是否曾经需要为你的查询结果集做行序号?你有时会发现能够为行做序号是一件很有用的事情。从前,你不得不作棘手的事,像创建一个有序号列的临时表,然后把你的SELECT结果插入到这个临时表中。现在,用ROW_NUMBER函数,你就可以获得添加在你的结果集的增加列中的行序号。为了获得行序号,你只要简单的将ROW_NUMBER函数作为一列添加进你的SELECT语句中。你还必须添加OVER语句以便告诉SQL Server你希望怎样添加行序号。
举个实际应用例子:
alter proc GetPage(
@pageSize int =10, --页数大小
@pageIndex int =6, --页码
@totalRows int =1 output, --总行数
@totalPages int =1 output, --总页数
@name varchar(100) = 'sys', --查询条件1
@xtype varchar(10) = 'S' --查询条件2
)
as
/*
*功能描述:根据查询条件分页输出
*参数说明:将查询条件分开作为每个条件单独的参数传入,如:查询条件1、查询条件2
* 不用将整个where条件作为一个参数传入,这样的话用动态sql,失去了proc预编译的意义,其二,代码不好维护
*说明:代码复杂的话建议使用tempTable,注意order by和where中的索引的正确建立;每个查询建立各自的存储过程
*/
begin
if @pageIndex = 0 return;
select @totalRows = count(1) from sysobjects
where name like '%'+@name+'%'
and 1=(case when isnull(@xtype,'')='' then 1 when isnull(@xtype,'')<>'' and xtype = @xtype then 1 else 2 end )
set @totalPages = (@totalRows-1)/@pageSize +1
if @pageIndex > @totalPages return
if @pageIndex =1
select top (@pageSize) id,name from sysobjects where name like '%'+@name+'%'
and 1=(case when isnull(@xtype,'')='' then 1 when isnull(@xtype,'')<>'' and xtype = @xtype then 1 else 2 end)order by name
else if @pageIndex = @totalPages
select * from (select top (@totalRows - @pageSize*(@pageIndex-1)) id,name from sysobjects
where name like '%'+@name+'%'
and 1=(case when isnull(@xtype,'')='' then 1 when isnull(@xtype,'')<>'' and xtype = @xtype then 1 else 2 end )order by name desc) as a order by name
else
select * from (select top (@pageSize*@pageIndex) id,name ,ROW_NUMBER() OVER (ORDER BY name) AS rowNo from sysobjects
where name like '%'+@name+'%' and 1=(case when isnull(@xtype,'')='' then 1 when isnull(@xtype,'')<>'' and xtype = @xtype then 1 else 2 end ) order by name ) as a
where a.rowNo between (@pageSize*@pageIndex)-@pageSize+1 and (@pageSize*@pageIndex)
end