SQL Server的分页查询(SQL Server paging query)

Urggggg！ 我一直在挣扎与此很长一段时间！ 我可以与MySQL那么容易，但不能与SQL Server做:(

下面是应一起加入了简化表。

通过使用内部联接语法所有的人结合，我必须写一个查询在未来的（顺便说一句，PHP）使用分页。 比方说，我需要它拍摄照片之间每页2012-10-01和二〇一二年十月三十○日....和20人的所有ID，名称和日期信息。

什么是在这里实现这一目标最简单的查询？ （我试过NOT IN ..但它是如此的马车因为我不习惯“不在”的东西...）

您可以忽略的性能速度。

谢谢！

Answer 1:

这是我会怎么做在SQL Server 2005+：

SELECT ID, Name, Photo, CreatedDate, rowNum, (rowNum / 20) + 1 as pageNum
FROM (
    SELECT a.ID, a.Name, b.Photo, c.Created_Date
       , Row_Number() OVER (ORDER BY c.Created_Date ASC) as rowNum
    FROM a
       JOIN b ON a.ID = b.ID
       JOIN c ON c.photo = b.photo
    WHERE c.Created_Date BETWEEN '2012-01-1' AND '2012-10-30'
) x
WHERE (rowNum / 20) + 1 = 1

请注意，我用一个小整数除法权谋计算页码。

由于2005年之前的黯然没有ROW_NUMBER（），我会使用一个中间表与标识列：

    SELECT a.ID, a.Name, b.Photo, c.Created_Date
       , identity(int,1,1) as rowNum
    INTO t
    FROM a
       JOIN b ON a.ID = b.ID
       JOIN c ON c.photo = b.photo
    WHERE c.Created_Date BETWEEN '2012-01-1' AND '2012-10-30'
    ORDER BY c.Created_Date ASC
    GO

    ALTER TABLE t ADD pageNum AS rowNum / 20
    GO

    SELECT ID, Name, Photo, Created_Date, rowNum
    FROM t
    WHERE pageNum = 1 

Answer 2:

这里是我做到了一个很久以前..

    SELECT * FROM (
      SELECT TOP y * FROM (
           SELECT TOP x * FROM sometable
           ORDER BY somefield ASC
      )
      ORDER BY somefield DESC)
ORDER BY somefield

最里面的查询，SELECT TOP X，抓住行的第一个x号，第二个查询SELECT TOP Y，得到x行的最后一个Y，以及最外面的查询，SELECT *结果放置在正确的顺序。

这里是解释博客文章它是如何工作下面是一个博客文章中，我在2006年写了回信谈论它http://code.rawlinson.us/2006/12/t-sql-query-paging.html

该TL;博士; 的帖子是这一段：

举例来说，假设我们要在第一页，那么前20的结果。 这是很容易的，只要使用SELECT TOP 20 ... 但对于第二次或以后的页面？ 你如何获得21-40的项目？ 这是很容易，你可能会怀疑。 什么你实际上是试图得到的是顶的X结果下Y。 看你想向后下令前X结果的顶部Ÿ另一种方式。

这取决于你如何计算并提供x和y的值来查询。

Answer 3:

微软增加了在SQL Server 2012及以上使用本地分页功能， “OFFSET”和“Fetch” 。 您可以使用此功能如下：

-- Skip the first 500 rows and return the next 100
SELECT *
FROM TableName
ORDER BY [ID]
    OFFSET 500 ROWS
    FETCH NEXT 100 ROWS ONLY;

对于OFFSET __和FETCH NEXT __子句，可以指定的恒定值（如上），或可以指定变量，表达式，或恒定标量子查询。

Answer 4:

与SQL Server 2008 +试试这个的AdventureWorks数据库

DECLARE @PageIndex INT, @RowsPerPage INT
DECLARE @StartRow INT, @EndRow INT;

SET @PageIndex = 1;
SET @RowsPerPage = 500;
SET @StartRow = ((@PageIndex - 1) * @RowsPerPage) + 1;
SET @EndRow = @StartRow + @RowsPerPage - 1;

--append#1
WITH PAGE_ROWS
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate DESC, SalesOrderNumber ASC) AS ROW_NO
    , COUNT(*) OVER() AS TOTAL_ROWS
    , *
FROM( 
    --working query
    SELECT S.SalesOrderID
        , S.SalesOrderNumber
        , S.OrderDate
        , S.DueDate
        , S.ShipDate
        , S.Status
        , S.PurchaseOrderNumber
        , C.AccountNumber
        , P.FirstName, P.MiddleName, P.LastName
    FROM [Sales].[SalesOrderHeader] AS S
        LEFT OUTER JOIN [Sales].[Customer] AS C ON C.CustomerID=S.CustomerID
        LEFT OUTER JOIN [Person].[Person] AS P ON P.BusinessEntityID=C.PersonID
--append#2
) AS Src)
SELECT CEILING(TOTAL_ROWS/ CAST(@RowsPerPage AS DECIMAL(20,2))) AS TOTAL_PAGES
    ,*
FROM PAGE_ROWS
WHERE ROW_NO BETWEEN @StartRow AND @EndRow
ORDER BY OrderDate DESC, SalesOrderNumber ASC

Answer 5:

ALTER  Proc [dbo].[Sp_PagingonTable] 
@SearchText varchar(255) = null,
@ChannelName varchar(255)= null,
@ChannelCategory varchar(255)= null,
@ChannelType varchar(255)= null,
@PageNo int,
@PageSize int,
@TotalPages int output
As
DECLARE  @VariableTable TABLE
(
Rownumber INT,
ReferralHierarchyKey BIGINT, 
ReferralID VARCHAR(255), 
ChannelDetail VARCHAR(255),     
ChannelName VARCHAR(255),
ChannelCategory VARCHAR(255),
ChannelType VARCHAR(255)
)
 /*---Inserting Data into variable Table-------------*/
INSERT INTO @VariableTable
SELECT   
 ROW_NUMBER() OVER(ORDER BY [ColumnID) as Rownumber,*
 FROM [dbo].[TableName]
 WHERE (@SearchText IS NULL OR ChannelDetail LIKE '%' + @SearchText + '%')                                 And       (@ChannelName  IS NULL OR ChannelName  = @ChannelName )
/*--Applying Paging on filter Table--*/
SELECT 
        ReferralID
       ,ChannelDetail
       ,ChannelName
       ,ChannelCategory
       ,ChannelType 
    FROM 
        @VariableTable
WHERE Rownumber between (((@PageNo -1) *@PageSize)+1) and @PageNo *    @PageSize
 /*--Getting Total Pages After filter Table---*/
 SELECT @TotalPages =  (Count(*) + @PageSize - 1)/@PageSize FROM  @VariableTable
 SELECT @TotalPages

Answer 6:

我喜欢塔哈西迪基的答案，只是它需要修改查询过去了，有没有为我必须使用SQL UNION语句由于一个前同事提出了一些非常糟糕的设计决策工作。

通用SQL Server查询是：

SELECT * FROM (
    select ROW_NUMBER() OVER (order by ID) as row_num, * FROM (
      <<Put Your Query Here>>
    ) AS tempTable1
) AS tempTable2 WHERE row_num >= ((pageNum -1) * pageSize) AND row_num < ((pageNum -1) * pageSize) + pageSize;

我创建了一个假设为基础的一个寻呼Java函数：

    public static String buildPagingQuery(String sqlStr, String sortExpression, int pageNum, int pageSize) {
        if (StringUtils.isBlank(sortExpression)) { //NOTE: uses org.apache.commons.lang.StringUtils
            sortExpression = " (select 0)";
        }

        int startIndex = ((pageNum - 1) * pageSize) + 1;
        int endIndex = startIndex + pageSize;

        StringBuilder sb = new StringBuilder();

        sb.append("SELECT * FROM (");
        sb.append("SELECT ROW_NUMBER() OVER (ORDER BY ");
        sb.append(sortExpression);
        sb.append(") as row_num, * FROM (");
        sb.append(sqlStr);
        sb.append(") as tempTable1 ");
        sb.append(") AS tempTable2  "); 
        sb.append("WHERE row_num >= ").append(startIndex);
        sb.append(" AND row_num < ").append(endIndex);

        return sb.toString();
    }

我还没有检查了一大桌的表现。

Answer 7:

您可以使用从以下类的方法

internal class PagingHelper
{
    public static String ParseQueryForPagingAndSorting(String strSQL, string SortExpression, int StartIndex, int EndIndex)
    {
        if (String.IsNullOrEmpty(SortExpression))
            SortExpression = " (select 0)";
        StringBuilder sb = new StringBuilder();
        sb.Append("select * from (");
        sb.Append(" select ROW_NUMBER() OVER (ORDER BY " + SortExpression + ") AS row_num,");
        int index = strSQL.ToLower().IndexOf('t', 0);
        sb.Append(strSQL.Substring(index + 2));
        sb.Append(")");
        sb.Append(" AS TempTable");
        sb.Append(" where row_num>=" + StartIndex.ToString() + " AND row_num<=" + EndIndex.ToString());
        return sb.ToString();
    }
    public static String ParseQueryForCount(String strSQL)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append("select count(*) from");
        sb.Append("(");
        sb.Append(strSQL);
        sb.Append(")");
        sb.Append(" AS TempTable");
        return sb.ToString();
    }
}

Answer 8:

有一个我发现这样做到SQL Server 2012这个法子

http://raresql.com/2012/07/01/sql-paging-in-sql-server-2012-using-order-by-offset-and-fetch-next/

文章来源: SQL Server paging query