求SQL分页语句！！
求SQL分页语句！！
我用的是AspNetPager这个分页控件！！

------解决方案--------------------
SELECT Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 1 AND Row <= 10



The second page of 10 records would then be as follows:



SELECT Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 11 AND Row <= 20



If you have a lot of records, using TOP X in the inner SELECT clause may speed up things a bit as there is no use returning 1000 records if you are only going to grab records 11 through 20:



SELECT Description, Date
FROM (SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 11 AND Row <= 20



We can rap this up in a Stored Procedure as follows:



CREATE PROCEDURE dbo.ShowLog
@PageIndex INT, 
@PageSize INT 
AS

BEGIN 

WITH LogEntries AS ( 
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description 
FROM LOG)

SELECT Date, Description
FROM LogEntries 
WHERE Row between 

(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize


END 



It is only available in SQL Server 2005, but it is a heck of a lot easier and more intuitive than creating temp tables and using other stored procedures that I have used in the past. However, if you want to target your application for SQL Server 2000 use, I would stick with a record paging solution that works for both SQL Server 2005 and SQL Server 2000 Databases.

The code write in store procedure:
CREATE Procedure WebOrdersSearchOrderIdByCustomerNumberForPaging
(
@customerNumber VARCHAR(15),
@SortField VARCHAR(20) = 'orderId',
@SortType VARCHAR(10) = 'DESC',
@PageSize INT = 10,
@PageIndex INT = 0,
@TotalCount INT OUTPUT
)
AS
BEGIN
SELECT @TotalCount = count(orderId) FROM orders
WHERE customerNumber = @customerNumber

SET @sqlSelect = 'SELECT orderId FROM ('
SET @sqlSelect= @sqlSelect + 'SELECT ROW_NUMBER() OVER(ORDER BY ' + @SortField + ' ' + @SortType + ') AS rowNumber'
SET @sqlSelect= @sqlSelect + ', orderId'
SET @sqlSelect= @sqlSelect + ' FROM orders '
SET @sqlSelect= @sqlSelect + ') AS PagingTable '
SET @sqlSelect = @sqlSelect + ' WHERE rowNumber >=' + CONVERT(VARCHAR, @PageSize * @PageIndex)
SET @sqlSelect = @sqlSelect + ' AND rowNumber <' + CONVERT(VARCHAR, @PageSize * (@PageIndex + 1))
EXEC(@sqlSelect)

END

GO
------解决方案--------------------

SQL code

select top pangeSize * from table where id not in (select top (currentPage-1)*pageSize id from table)

------解决方案--------------------
string sql3 = "select distinct  top " + pageSize * pageIndex + " mid from tab_mother  order by mid desc";
                   sql = "select distinct  top " + pageSize + " * from tab_mother where mid < ( select min(mid) from (" + sql3 + ") a) order by mid desc";

------解决方案--------------------
SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ProductID ASC) RowNum, * FROM Production.Product ) OrderData WHERE RowNum BETWEEN @iRowCount*(@iPageNo-1)+1 and @iRowCount*@iPageNo