当前位置: 网站首页 > 热门专题 > 数据库恢复专题 >

SQL Server 实现分页的方式

时间:2010-08-23 23:19  来源:   点 击:
导读』:MsSQL:SQL Server 实现分页的方式,Mssql基础教程软件学院..
关键字:MsSQL,Mssql基础教程

2000:

  首先获得所有的记录集合的存储过程:

create   PROCEDURE [dbo].[P_GetOrderNumber]
AS
 select count(orderid) from orders;----orders为表
 RETURN

分页的存储过程

create  procedure [dbo].[P_GetPagedOrders2000]
(@startIndex int,  ---开始页数
@pageSize int----每一页显示的数目
)
as
set nocount on
declare @indextable table(id int identity(1,1),nid int)  ----定义一个表变量
declare @PageUpperBound int
set @PageUpperBound=@startIndex+@pagesize-1
set rowcount @PageUpperBound
insert into @indextable(nid) select orderid from orders order by orderid desc
select O.orderid,O.orderdate,O.customerid,C.CompanyName,E.FirstName+' '+E.LastName as EmployeeName
from orders O
left outer join Customers C
on O.CustomerID=C.CustomerID
left outer join Employees E
on O.EmployeeID=E.EmployeeID
inner join @indextable t on
O.orderid=t.nid
where t.id between @startIndex and @PageUpperBound order by t.id    ----实现分页的关键
set nocount off


2005:

create  [dbo].[P_GetPagedOrders2005]
(@startIndex INT,
 @pageSize INT
 )
AS
begin
WITH orderList AS (
SELECT ROW_NUMBER() OVER (ORDER BY O.orderid DESC)AS Row, O.orderid,O.orderdate,O.customerid,C.CompanyName,E.FirstName+' '+E.LastName as EmployeeName
from orders O
left outer join Customers C
on O.CustomerID=C.CustomerID
left outer join Employees E
on O.EmployeeID=E.EmployeeID)

SELECT orderid,orderdate,customerid,companyName,employeeName
FROM orderlist
WHERE Row between @startIndex and @startIndex+@pageSize-1
end

很强大!
(0)
0%
烂透了!
(0)
0%
编辑:

分享与收藏:
您可能感兴趣的文章

数据恢复

热门技术文章

硬盘常见问题