欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

sqlserver2005使用row_number() over分页的实现方法

程序员文章站 2022-06-11 11:07:46
语法:row_number() over(partition by column order by column)
例子: 复制代码 代码如下: se...
语法:row_number() over(partition by column order by column) <br>
例子:
复制代码 代码如下:

select * from (
    select *, row_number() over(order by a.createtime desc ) as rownumber from table_name as a
  ) as b
  where rownumber between 1 and 5

将会返回table表

其中有一列名字为 rownumber, 编号从1开始

示例:
xlh row_num
1700 1
1500 2
1085 3
710 4

有了row_num 编号之后是不是很方便分页呀! 哈哈

只要使用
where rownumber between
就可以实现分页了 呵呵(从此分页就是这么简单)

例子:
复制代码 代码如下:

select *
from(
select row_number() over( order by psio.createtime desc ) as rownumber,psio.seqno,psio.createtime from dbo.output psio
inner join album ppa on psio.ppaid=ppa.ppaid
where ppa.ppaid=103--ppaid=3.ppaid
) t where rownumber between 1 and 5 order by 1

在当前select里面不能采用 rownumber字段,并且不能使用排序

方式一
select top @pagesize * from company where id not in
(select top @pagesize*(@pageindex-1) id from company)

方式二row_number()over

--row_number() 就是生成一个有顺序的行号,而他生成顺序的标准,就是后面紧跟的over(order by id)
--还必须添加over语句以便告诉sql server你希望怎样添加行序号。
select getdate()
select * from company where id in (
--搜索出settable表中所有的编号,也就是company表中的id,这里只不过要得到num(即有顺序的编号)
select id from
--搜索出出表中的所有的id,并且新建一列num用来存取排序的编号,并且把这张表赋值给settable
(select id,row_number() over (order by id) as
num from company)
as settable
--添加搜索条件页索引和页大小
where num between (@pageindex-1)*@pagesize+1 and @pageindex*@pagesize)
select getdate()

方式三
select * from (select row_number() over(order by id asc) as rownum,
id
from company ) as d
where rownum between (@pageindex-1)*@pagesize+1 and @pageindex*@pagesize
order by id asc

sql server 2000的自定义分页,但是在sql server 2000中,要实现显示某一页,就返回那一页数据的效果的方法实在不尽人意.网上很多通用的分页存储过程,但看着就头大.如果使用我前面提到的使用in,not in,top来进行返回特定页,特殊的限制又会比较多(比如id要递增).现在sql server 2005中提供了一个函数row_number(),可以使自定义分页变得简单许多.
我们先来看看row_number()是干什么的.执行下面这段sql语句:
select [reportid],[username], [reportid],
[timestart], [timeend],row_number() over (order by reportid) as rowno
from [executionlog]
很简单,row_number() 就是生成一个顺序的行号,而他生成顺序的标准,就是后面紧跟的over(order by reportid).现在,你看到了自定义分页的影子了吗?:)下面,我们看看怎么具体应用这个rowno进行分页.
现在,假设我每一页的数据是10条,我们就可以使用如下所示的sql语句返回指定页的数据:
@"
select top 10 *
from
(
select top 10 [instancename], [username], [reportid],
[timestart], [timeend],row_number() over (order by reportid) as rowno
from [executionlog]
) as a
where rowno > " + pageindex*10
pageindex就是我们需要数据的页数.很简单,不是吗?并且,这种方式几乎没有什么限制,因为他相当于对于任何检索,都生成了一个新的排序列.我们就可以使用该列进行自定义分页.
================
下面举个例子:
row_number函数
  sql server2005为我们引入了一个row_number函数。你是否曾经需要为你的查询结果集做行序号?你有时会发现能够为行做序号是一件很有用的事情。从前,你不得不作棘手的事,像创建一个有序号列的临时表,然后把你的select结果插入到这个临时表中。现在,用row_number函数,你就可以获得添加在你的结果集的增加列中的行序号。为了获得行序号,你只要简单的将row_number函数作为一列添加进你的select语句中。你还必须添加over语句以便告诉sql server你希望怎样添加行序号。
  select row_number() over(order by employee_id) as 'row number', * from
  dbo.employee

结果
row number employee_id firstname lastname soc_sec
1 5623222 tim jones 123-65-8745
2 5632111 rob kinkad 456-69-8754
3 6365666 jim miller 236-56-8989
4 7563333 joe roberts 564-89-5555

  这个查询返回所有的雇员和一个显示每条记录在哪一行的一个序号。over语句使sql server基于employee_id列增加行序号。换句话说,产生了行序号,就好像数据按employee_id做了排序。这是很重要的一点,因为你仍然可以改变select的排序顺序。以下面的查询为例:
  select row_number() over(order by employee_id) as 'row number', * from
  dbo.employee
  order by soc_sec

结果
row number employee_id firstname lastname soc_sec
1 5623222 tim jones 123-65-8745
3 6365666 jim miller 236-56-8989
2 5632111 rob kinkad 456-69-8754
4 7563333 joe roberts 564-89-5555

  注意第二个结果集数据是按社会安全编号来排序的,但是行号仍然创建得好像数据是按employee_id排序的。
-------------------------------分页存储过程
if exists (select * from sysobjects where name='p_student')
drop procedure p_student
go
create procedure p_student
@startindex int,
@pagesize int
as
begin with studentlist as (
select row_number() over (order by o.stuid ) row,
o.stuid,o.stuname,o.stuage,o.stuinfo
from student o)
select row, stuid,stuname,stuage,stuinfo
from studentlist
where row between @startindex and @startindex+@pagesize-1
end

-------------------分页2---------
set ansi_nulls on
set quoted_identifier on
go
alter procedure [dbo].[deer_page]
(
@startindex int
,@pagesize int
,@strsql varchar(5000) ---查询条件
,@tablename varchar(50)
,@docount as bit=1 -- 0值返回记录总数, 非 0 值则返回记录
)
as
begin tran
if @docount=0
goto getcount
else
goto getsearch
getcount: --返回记录总数
declare @searchsql as nvarchar(4000)
set @searchsql= 'select count(*) as total from '+@tablename+' where intreserve1=0'
exec sp_executesql @searchsql
--print @searchsql
commit tran
return
getsearch: --返回记录
declare @sqlquery varchar(4000)
set @sqlquery='select * from
(select row_number() over (order by o.id ) row, * from '+@tablename+' o where intreserve1=0) as temp
where row between '+cast(@startindex as varchar) +' and '+cast(@startindex+@pagesize-1 as varchar)+ @strsql
---print @sqlquery
execute(@sqlquery)
commit tran