SQL Server 2005对结果集分页

SQL Server 2005对结果集分页

介绍 

有了SQL Server 2005之后,我们使用分页查询比老版本容易多了。在本文将主要用到的是NorthWind数据库,所以可以仿照编写所列举的范例,尽量使例子简单化;因为任何复杂的东西都会造成混淆,我们从“传统”的方法着手,比如:SELECT, TOP,然后进入SQL Server 2005 分页的例子。

背景

我们常常会碰到这样的一个问题或者说一系列问题,“如何在SQL中进行分页?在有很多个记录的情况下又如何做,比如10000个记录或者更多呢?”

这必定是一个普遍的问题,每一个开发人员必须处理或者说解决的。具有非常大的数据库集的工作和分页大小是怎样的?从多种表所得到的结果集又是如何?

因此,决定具体结合SQL Server 2005来研究这些问题。下面的方法是至今为止最容易的,建议采纳。但是这是非常罕见的,并非易事。 

select * from mytable Where ID between 20 and 30 

SQL Top

SQL Top(从结果集返回记录)非常善于从结果集的每个尾部返回大量的记录。下面的例子通过命令qty获得了前10名顾客。在论坛上这是一个非常普遍的问题。尽管TOP也可以拉动一定百分比的记录,但是我们这里不讨论。 

select top 10 * from customers -- This is a very basic example. 
select TOP 10 Customers.CustomerID, Customers.CompanyName, count(*) OrderCount 
from Customers inner join Orders on Orders.CustomerID = Customers.CustomerID 
GROUP BY Customers.CustomerID, Customers.CompanyName 
ORDER BY OrderCount DESC 

   这是很有用的,当你要把记录从11拉到20时就可以使用临时表。

SELECT TOP 30 * INTO #TEMP from Customers ORDER BY CompanyName ASC 
SELECT TOP 10 * INTO #TEMP2 from #Temp ORDER BY CompanyName DESC 
SELECT * FROM #TEMP2 

这对少数前几个页面或者前几个用户是种惩罚。如果拥有这样的用户,他们想从一页之后返回页面,将以使1000条记录返回10条来结束,这是非常低效的。可以在第一个临时表中内置一个身份,然后用一个SELECT声明来作替代。

TOP的替代 

有一个这样的TOP替代,它使用的是rowcount(行计数)。使用行计数要小心。如果它不关闭的话,将陷入各种各样的困境。 

SET rowcount 10 SELECT * from Customers ORDER BY CompanyName 

WITH, ROW_NUMBER (行数)and OVER 

这对SQL Server 2005来说非常新鲜并且看上去非常有用。下面一个例子显示从一个结果集得到20至19条记录。刚开始有一点惊奇,但是浏览了查询器后发现它是如此简单。

With Cust AS (SELECT CustomerID, CompanyName, ROW_NUMBER() OVER (order by CompanyName) as RowNumber FROM Customers ) 

With Cust AS (SELECT NewsElectiveID, ROW_NUMBER() OVER (order by NewsElectiveID) as RowNumber FROM NewsElective )

select * from Cust Where RowNumber Between 20 and 30

SQL Server 2005的WITH指定了一个临时命名的结果,很像SQL Server以前版本中的临时表。但是,输入部分是ROW_NUMBER和OVER声明,它根据公司的名称在每组中创建行数。这就像通过命令条文向临时表添加一个身份种子。

我希望你赞成我的看法。如果不,运行代码并查看结果集。对大表来说速度真的非常快; 表的速度超过250,000条记录,对此我留下了深刻的印象。

一起整合到储存过程中

现在我们把它一起整合到储存过程,这个储存过程我们可以通过应用程序来使用。暂时不展示.NET Datagrid或者相似的控件,因为是本文探讨范围之外。下面看到的储存过程使用了灵活的页面大小和页面数目,所以可以随意地选择任何页面。这样,如果想跳过前十页去寻找某一条记录就非常方便了。下面的例子是从第一页开始分页的,而不是从第0页,但也可以随意更改。 

CREATE PROC GetCustomersByPage 
@PageSize int, @PageNumber int 
AS 
Declare @RowStart int 
Declare @RowEnd int 
if @PageNumber > 0 
Begin 
SET @PageNumber = @PageNumber -1 
SET @RowStart = @PageSize * @PageNumber + 1; 
SET @RowEnd = @RowStart + @PageSize - 1 ; 
With Cust AS ( SELECT CustomerID, CompanyName, ROW_NUMBER() OVER (order by CompanyName) as RowNumber FROM Customers ) 
select * from Cust Where RowNumber >= @RowStart and RowNumber <= @RowEnd 

end 

END 


   运行这个程序,仅需指定页面大小和页数目(GetCustomersByPage, @PageSize and @PageNumber),代码如下:

exec GetCustomersByPage 10, 1

1

<!--EndFragment-->
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值