SQL Server中的行偏移量

SQL Server中是否有任何方法可以从给定的偏移量开始获取结果？例如,在另一种类型的SQL数据库中,可以执行以下操作:

SELECT * FROM MyTable OFFSET 50 LIMIT 25

得到结果51-75.此构造似乎不存在于SQL Server中.

如何在不加载我不关心的所有行的情况下完成此操作？谢谢!

1> Brian Kim..：

---

我会避免使用SELECT *.指定您真正想要的列,即使它们可能都是它们.

SQL Server 2005+

SELECT col1, col2 
FROM (
    SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
    FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow

SQL Server 2000

通过SQL Server 2000中的大型结果集进行高效分页

一种更有效的大结果集寻呼方法

---

我敢肯定他使用了"*",因为它更容易打字并且比"col1,col2,... colN"更好地指出了这一点.

---

至于为什么不使用它,`SELECT*`意味着如果表的结构发生变化,你的查询仍会运行,但会产生不同的结果.如果添加了一列,这可能很有用(尽管你仍然需要在某处使用它); 如果一个列被删除或重命名,那么你的SQL最好明显地破坏,而不是因为变量未初始化而进一步表现得很奇怪.

---

即使您选择了所有列,为什么还建议避免使用SELECT？

---

选择表的所有数据并剪切？如果有5000000000行？选择5000000000行并为每个查询剪切？它对服务器的CPU和内存不够有效.

---

请注意,2012 +使其实施方式更好.请参阅+ Martin Smith的回答

2> Martin Smith..：

---

如果您将按顺序处理所有页面,那么只需记住上一页上看到的最后一个键值,TOP (25) ... WHERE Key > @last_key ORDER BY Key如果存在合适的索引以便有效地搜索,则使用可以是性能最佳的方法 - 如果不是,则使用API​​游标.

对于选择仲裁页面,SQL Server 2005 - 2008 R2的最佳解决方案可能是ROW_NUMBER和BETWEEN

对于SQL Server 2012+,您可以使用增强的ORDER BY子句来满足此需求.

SELECT  *
FROM     MyTable 
ORDER BY OrderingColumn ASC 
OFFSET  50 ROWS 
FETCH NEXT 25 ROWS ONLY 

虽然这个选项的表现有多好还有待观察.

---

现在是时候将它添加到tSQL了

---

仅适用于Sql Server 2012 :(

---

它现在可以在SQL Server Compact 4.0中使用 - > http://msdn.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

3> leoinfo..：

---

这是一种方式(SQL2000)

SELECT * FROM
(
    SELECT TOP (@pageSize) * FROM
    (
        SELECT TOP (@pageNumber * @pageSize) *
        FROM tableName 
        ORDER BY columnName ASC
    ) AS t1 
    ORDER BY columnName DESC
) AS t2 
ORDER BY columnName ASC

这是另一种方式(SQL 2005)

;WITH results AS (
    SELECT 
        rowNo = ROW_NUMBER() OVER( ORDER BY columnName ASC )
        , *
    FROM tableName 
) 
SELECT * 
FROM results
WHERE rowNo between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize

---

SQL2000的解决方案不适用于结果集中的最后一页,除非总行数恰好是页面大小的倍数.

4> Matthias Mei..：

---

您可以使用ROW_NUMBER()函数来获得您想要的:

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) RowNr, id FROM tbl) t
WHERE RowNr BETWEEN 10 AND 20

5> Lukas Eder..：

---

有OFFSET .. FETCH在SQL Server 2012中,但你需要指定一个ORDER BY列.

如果你真的没有任何可以作为ORDER BY列传递的显式列(正如其他人所建议的那样),那么你可以使用这个技巧:

SELECT * FROM MyTable 
ORDER BY @@VERSION 
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY

... 要么

SELECT * FROM MyTable 
ORDER BY (SELECT 0)
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY

当用户没有明确指定订单时,我们在jOOQ中使用它.这将产生相当随机的顺序,而无需任何额外费用.

6> 小智..：

---

对于具有更多和更大数据列的表,我更喜欢:

SELECT 
  tablename.col1,
  tablename.col2,
  tablename.col3,
  ...
FROM
(
  (
    SELECT
      col1
    FROM 
    (
      SELECT col1, ROW_NUMBER() OVER (ORDER BY col1 ASC) AS RowNum
      FROM tablename
      WHERE ([CONDITION])
    )
    AS T1 WHERE T1.RowNum BETWEEN [OFFSET] AND [OFFSET + LIMIT]
  )
  AS T2 INNER JOIN tablename ON T2.col1=tablename.col1
);

-

[CONDITION] can contain any WHERE clause for searching.
[OFFSET] specifies the start,
[LIMIT] the maximum results.

它对具有大型数据(如BLOB)的表具有更好的性能,因为ROW_NUMBER函数只需查看一列,并且只返回匹配的行与所有列.

7> 小智..：

---

请参阅我的select for paginator

SELECT TOP @limit * FROM (
   SELECT ROW_NUMBER() OVER (ORDER BY colunx ASC) offset, * FROM (

     -- YOU SELECT HERE
     SELECT * FROM mytable


   ) myquery
) paginator
WHERE offset > @offset

这解决了分页;)