SQL Server分页查询

Question

厄格！我已经为此挣扎了很长一段时间！我可以用MySQL很容易做到这一点，但用SQL Server就不行了:(

下面是应该连接在一起的简化表。

​

​

通过使用inner语法将它们组合在一起，我必须编写一个查询以供将来分页使用(顺便说一下，PHP)。假设我需要在2012-10-01和2012-10-30之间拍摄照片的所有ID、姓名和日期信息。每页20人。

在这里实现这个目标的最简单的查询是什么？(我试着不在..。但它是如此的错误，因为我不习惯‘不在’的东西…)

您可以忽略性能速度。

谢谢!

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行，最外面的查询

*将结果按正确的顺序排列。

这里有一篇博客文章解释了它是如何工作的这里是我在2006年写的一篇关于它的博客文章http://code.rawlinson.us/2006/12/t-sql-query-paging.html

这篇文章的tl;dr；来自这一段：

例如，假设我们想要第一个页面，所以前20个结果。

。这非常简单，只需使用SELECT TOP20…。但是第二页或后续页呢？你如何获得21-40个项目？这比你想象的要容易。实际上，您想要得到的是前x个结果的底部y。换一种方式看，你想让前x个结果的前y向后排序。

如何计算x和y值并将其提供给查询由您决定。

Answer 3

微软使用"OFFSET" and "FETCH"在SQL Server2012及更高版本中添加了本机分页功能。您可以按如下方式使用此功能：

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