SQL Server分页查询

Question

哎呀呀呀呀！我已经苦恼了很久！我可以使用MySQL轻松做到，但不是用SQL Server :(

这里是应该全部联接在一起的简化表格。

通过使用内部联接语法来组合它们所有，我必须编写一个查询以备将来分页使用(PHP)。 假设我需要在2012-10-01至2012-10-30之间拍摄的所有ID、名称和日期信息......每页20个人。

在这里实现目标最简单的查询是什么？（我尝试过NOT IN.. 但因为我不习惯“NOT IN”的东西，它有点小瑕疵...）

您可以忽略性能速度。

谢谢！

- Raccoon

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 

- Mike Monteiro

---

我所指的“分页”是指..您从总量中获取部分信息。Stackoverflow本身就是一个很好的例子。如果您搜索某些内容，它不会显示整个结果，而只显示第一页的一部分。如果您进入下一页，则会得到另一小部分信息，以此类推。这就是在此处应该做的。 - Raccoon

Row_Number函数是在2005年引入的，如果你真的要基于SQL 2000设计一个应用程序...请检查是否有使用较新版本的Microsoft SQL Server免费Express Editions的选项。 - SwissCoder

啊，我开始意识到@Raccoon可能指的是“MS Server 2003”的含义。你是指MS Access 2003吗？ - Lukas Eder

我不明白这个技巧 WHERE (rowNum / 20) + 1 = 1 ?? 如果我想获取1到20条记录，那么怎么办？ - Thomas

诀窍是：WHERE（rowNum / [每页大小]）+1 = [要检索的页数]。因此，如果您希望您的页面有10个项目，并且您想要第7页（第61-70行），则应执行以下操作：WHERE（rowNum / 10）+1 = 7。 - Mike Monteiro

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 *将结果以正确的顺序呈现。这篇博客文章解释了其工作原理：http://code.rawlinson.us/2006/12/t-sql-query-paging.html。简而言之，在第一页时，我们想要前20个结果，只需使用SELECT TOP 20...即可，但是对于第二页或后续页，如何获取21-40项呢？这比您想象的要容易。实际上，您想要获取的是前x行数据的最后y行，换句话说，您需要反向排序前x行数据，并获取前y行数据。关于如何计算和提供查询的x和y值，由您决定。

- Bill Rawlinson

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 __ 子句，您可以指定常量值（如上所示），也可以指定变量、表达式或常量标量子查询。

- Peyman Mehrabani

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

- wwanich

Answer 5

我喜欢Taha Siddiqui的答案，但它需要修改传入的查询，并且对于我必须使用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;

我创建了一个假设基于1的分页的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();
    }

我尚未检查大表的性能。

- howard

Answer 6

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

- Muhammad ADIL

Answer 7

我们可以通过CTE（公共表达式）实现相同的效果。首先，我们需要设置当前页面编号和结果的偏移量，从中我们需要获取结果。然后，我们必须按ROW_NUMBER对结果集进行排序，并使用cte功能存储结果。然后，根据行号过滤结果与页面大小和偏移量匹配。 SQL查询如下：

DECLARE @PageSize INT=1 ,@PageNumber INT=2

DECLARE @Offset int =(@PageSize * (@PageNumber - 1))+1

;WITH Results_CTE AS
(

    SELECT *,
                   ROW_NUMBER() OVER (ORDER BY FieldName) AS RowNum
    FROM TABLENAME
  )  

  SELECT *
  FROM Results_CTE
  WHERE RowNum>=@Offset AND RowNum < @Offset + @PageSize

END

https://amonghorizon.blogspot.com/2020/07/sql-server-query-for-pagination-control.html

- Hashim KM

Answer 8

您可以使用以下类中的方法。

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();
    }
}

- Taha Rehman Siddiqui

Answer 9

我发现了另一种在Sql server 2012中实现此操作的方法

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

- Systematix Infotech