在数据库中排序和分页 [英] Sorting and Paging in the Database

问题描述

我想利用SQL 2005的Row_Number功能来进行DB

的侧面分页。但是，目前我在前端进行排序。


如果我执行排序然后要求第二页，我将检索错误的

项目对于tha页面，因为排序项目是错误的。


是否可以做这样的事情？


使用Cust AS

（SELECT CustomerID，CompanyName，

ROW_NUMBER（）OVER（由@OrderField订购）作为RowNumber

来自客户）

来自Cust的选择*




TIA


MattC

Hi,

I wanted to take advantage of the Row_Number feature of SQL 2005 to to DB
side paging. However, currently I perform my sorting on the front end.

If I perform a sort and then ask for page two, I will retrieve the wrong
items for tha page as the sort item would be wrong.

Is it possible to do something like this?

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by @OrderField) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber Between @Start and @End

TIA

MattC

推荐答案

在数据库中分页通常是一个坏主意，因为你引入了

load isn'是必要的，可能会导致负载瓶颈。请问

分页是不是正常工作？你需要设置正确的页面索引，你有没有这样做？b / b

-

问候，

Alvin Bruney

------------------------------------ ------------------

无耻的作者插件

.NET Services for .NET即将推出...

OWC亚马逊上的黑皮书和
www.lulu.com/owc

专业版VSTO 2005 - Wrox / Wiley

" MattC" < [e-mail]在留言中写道

新闻：eI ************** @ TK2MSFTNGP02.phx.gbl ...

Paging in the database is frequently a bad idea simply because you introduce
load that isn''t necessary and may cause bottlenecks with load. What about
the paging isn''t working? You need to set the correct page index, have you
done so?

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
OWC Black book on Amazon and
www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley
"MattC" <[e-mail] in message
news:eI**************@TK2MSFTNGP02.phx.gbl...

我想利用SQL 2005的Row_Number功能来进行DB

的侧面分页。但是，目前我在前端进行排序。


如果我执行排序然后要求第二页，我将检索错误的

项目对于tha页面，因为排序项目是错误的。


是否可以做这样的事情？


使用Cust AS

（SELECT CustomerID，CompanyName，

ROW_NUMBER（）OVER（由@OrderField订购）作为RowNumber

来自客户）

来自Cust的选择*




TIA


MattC

Hi,

I wanted to take advantage of the Row_Number feature of SQL 2005 to to DB
side paging. However, currently I perform my sorting on the front end.

If I perform a sort and then ask for page two, I will retrieve the wrong
items for tha page as the sort item would be wrong.

Is it possible to do something like this?

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by @OrderField) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber Between @Start and @End

TIA

MattC

好的100行我会在中间层完成所有操作。对于10,000，那么我不会想要下线并看不到9,950行，


因此，对于我平庸的大小桌子，我使用了PagedDataSource的东西。我是什么

寻找是一种使用一些分页代码的通用方式，用于我的''较大''

表。


虽然如果我只做了几次，代码维护也不会太单独写个别的每个人。


谢谢


MattC

" Alvin Bruney [MVP]" <没有电子邮件地址的人在留言中写道

新闻：O％**************** @ TK2MSFTNGP03.phx.gbl ...

Ok for 100 rows I would do it all in my middle tier. For 10,000 then I
don''t want that coming down the line and having 9,950 rows unseen,

So for my mediocre sized table I employ PagedDataSource stuff. What I was
looking for was a generic way of using some paging code for my ''larger''
tables.

Although if I''m only doing this a few times the code maintenance wont be too
bad on writing each one individually.

Thanks

MattC
"Alvin Bruney [MVP]" <some guy without an email addresswrote in message
news:O%****************@TK2MSFTNGP03.phx.gbl...

在数据库中进行分页通常是一个坏主意，因为你需要引入不必要的负载并且可能导致负载瓶颈。 />
分页怎么办？你需要设置正确的页面

索引，你有没有这样做过？


-

问候，

Alvin Bruney

------------------------------------ ------------------

无耻的作者插件

.NET Services for .NET即将推出...

OWC亚马逊上的黑皮书和
www.lulu.com/owc

专业版VSTO 2005 - Wrox / Wiley


" MattC" < [e-mail]在留言中写道

新闻：eI ************** @ TK2MSFTNGP02.phx.gbl ...

Paging in the database is frequently a bad idea simply because you
introduce load that isn''t necessary and may cause bottlenecks with load.
What about the paging isn''t working? You need to set the correct page
index, have you done so?

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
OWC Black book on Amazon and
www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley
"MattC" <[e-mail] in message
news:eI**************@TK2MSFTNGP02.phx.gbl...

>

我想利用SQL 2005的Row_Number功能来进行数据库边缘分页。但是，目前我在前端进行排序。

如果我执行排序然后请求第二页，我会检索错误的页面，因为排序项目会是错的。

是否可以做这样的事情？

使用Cust AS
（选择CustomerID，CompanyName，
ROW_NUMBER（）OVER（订单来自@OrderField）作为RowNumber
来自客户）
选择*
来自Cust
@Start和@End之间的行号是什么

TIA
MattC

>Hi,

I wanted to take advantage of the Row_Number feature of SQL 2005 to to DB
side paging. However, currently I perform my sorting on the front end.

If I perform a sort and then ask for page two, I will retrieve the wrong
items for tha page as the sort item would be wrong.

Is it possible to do something like this?

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by @OrderField) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber Between @Start and @End

TIA

MattC

5月16日凌晨3:20，Alvin Bruney [MVP] QUOT; <没有电子邮件的人

addresswrote：

On May 16, 3:20 am, "Alvin Bruney [MVP]" <some guy without an email
addresswrote:

只是因为你介绍了数据库中的分页通常是一个坏主意

负载不是必需的，可能会导致负载瓶颈。请问

分页是不是正常工作？你需要设置正确的页面索引，你有没有这么做？

Paging in the database is frequently a bad idea simply because you introduce
load that isn''t necessary and may cause bottlenecks with load. What about
the paging isn''t working? You need to set the correct page index, have you
done so?

Alvin，你认真对待sql分页吗？

Alvin, are you serious about sql paging?

这篇关于在数据库中排序和分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！