介绍
有了
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 First 30 records in to Temp table
SELECT
TOP
30
*
INTO
#TEMP
from
Customers
ORDER
BY
CompanyName
ASC
--Select Bottom 10 records in another temp table
SELECT
TOP
10
*
INTO
#TEMP2
from
#Temp
ORDER
BY
CompanyName
DESC
-- GET THE RECORDS
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 )
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