Question

一般BBS的分页的SQl文是什么思路？

我现在是这样做的。如果每页容量30条，
第二页的时候我先选取top60再去掉top30。
这样做的好处就是可以过滤掉flag==-1的情况。保证有30条在界面上。

但是有一个坏处就是如果到了几千页的时候我相当于要取top5000
再去掉top4970。
速度就很慢了。

一般的BBS是怎么过滤条数的呢、

Answer 1

一般分页的思路都是像你那样说的.
但是如果你使用的是SQL2005.
有个新增的函数可以使用,达到简单快速分页的效果.
比如

SELECT Row_Number() OVER (ORDER BY createdate) as rownumber , * FROM tablename WHERE rownumber BETWEEN 4970 AND 5000

上面的row_number() 函数生成的是一列 按照 createdate字段 进行排序的序号. 那么 rownumber BETWEEN 4970 AND 5000 自然就是第4970到第5000条的记录了.

使用2000的 好象还有一种思路.
比如你需要查出 4970 到 5000 的记录.
可以先查出 top 5000, 然后把这 5000 条记录 逆序(order by 字段 desc) 查出来 再TOP 30 ,就是 4970 到 5000 的记录了.

比如 SELECT TOP 30 FROM ( SELECT TOP 5000 FROM tablename ORDER BY createdate ) ORDER BY createdate DESC

Answer 2

内容摘录：Introduction
Developers and database administrators have long debated methods for paging recordset results from Microsoft SQL Server, trying to balance ease of use with performance. The simplest methods were less efficient because they retrieved entire datasets from SQL Server before eliminating records which were not to be included, while the best-performing methods handled all paging on the server with more complex scripting. The ROW_NUMBER() function introduced in SQL Server 2005 provides an efficient way to limit results relatively easily.

Paging Efficiency

In order to scale well, most applications only work with a portion of the available data at a given time. Web-based data maintenance applications are the most common example of this, and several data-bindable ASP.NET classes (such as GridView and Datagrid) have built-in support for paging results. While it is possible to handle paging within the web page code, this may require transferring all of the data from the database server to the web server every time the control is updated. To improve performance and efficiency, data which will not be used should be eliminated from processing as early as possible.

Paging Methods

Many popular databases offer functions allowing you to limit which rows are returned for a given query based upon their position within the record set. For example, MySQL provides the LIMIT qualifier, which takes two parameters. The first LIMIT parameter specifies which (zero-based) row number will be the first record returned, and the second parameter specifies the maximum number of records returned. The query:

SELECT * FROM table LIMIT 20,13

...will return the 20th through the 32nd records -- assuming at least 33 records are available to return. If fewer than 33 records are available, the query will return all records from record 20 on. If fewer than 20 records are available, none will be returned.

SQL Server does not have this functionality, however the 2005 release does have a number of other new tricks. For instance, support for CLR procedures means it is possible to use existing paging methods to write VB.NET or C# code that would execute within the SQL Server environment. Unfortunately, CLR procedures are not as efficient as native Transact SQL. To ensure best performance, queries should still be written in TSQL whenever practical.

Using ROW_NUMBER()

TSQL in the 2005 release includes the ROW_NUMBER() function, which adds an integer field to each record with the record's ordinal result set number. Stated more simply, it adds the record's position within the result set as an additional field so that the first record has a 1, the second a 2, etc. This may appear to be of little value, however by using nested queries we can use this to our advantage.

Answer 3

使用SQL Server2005或更高版本都有Row_Number函数可以直接使用，使用Top 注意

Answer 4

支持下 zc860406