SQL Server paging query

问题:

Urggggg! I've been struggling with this for a long time! I can do it with MySQL so easy but not with SQL Server :(

Here are the simplified tables which should be joined all together.

Combining all of them by using inner join syntax, I have to write a query to use for paging in the future (btw, PHP). Let's say I need all ID, Name, and Date info which a picture is taken between 2012-10-01 and 2012-10-30.... and 20 people per page.

What would be the easiest query to achieve the goal here? (I tried NOT IN.. but it was so buggy cuz I'm not used to 'NOT IN' stuff...)

You can ignore the performance speed.

Thank you!

回答1:

This is how I would do it in 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

Note that I'm using a little integer division trickery to calculate page number.

Since pre-2005 sadly doesn't have row_number(), I'd use an intermediate table with an identity column:

    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 

回答2:

Here is how I did it a long time ago..

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

The innermost query, SELECT TOP x, grabs the first x number of rows, the second query SELECT TOP y, gets the last y of x rows, and the outermost query, SELECT * puts the results in the right order.

Here is a blog post that explains how it works Here is a blog article I wrote back in 2006 talking about it http://code.rawlinson.us/2006/12/t-sql-query-paging.html

The tl;dr; of the post is from this paragraph:

For example, let’s say we want the first page, so the top 20 results. That is pretty easy, just use SELECT TOP 20 …. but what about the second or subsequent pages? How do you get the 21-40 items? It’s easier than you might suspect. What you are actually trying to get is the bottom y of the top x results. To look at that another way you want the top y of the top x results ordered backwards.

It's up to you how you calculate and provide the x and y values to the query.

回答3:

Try this with Sql Server 2008 + AdventureWorks database

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

回答4:

Microsoft added native paging features in SQL Server 2012 and above using "OFFSET" and "FETCH". You can use this feature as below:

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

For the OFFSET __ and FETCH NEXT __ clauses, you can specify constant values (as above), or you can specify variables, expressions, or constant scalar subqueries.

回答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

回答6:

I liked Taha Siddiqui's answer except that it requires modifying the passed in query and it didn't work for an SQL UNION statement that I have to use due to some very poor design decisions made by a former co-worker.

The generic SQL Server query is:

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;

I created a Java function that assumes one-based paging:

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

I have not yet checked performance on a large table.

回答7:

You can use the methods from the following class

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

回答8:

There is a another way I have found to do this into Sql server 2012

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