如何在SQL Server中实现LIMIT功能？

Question

我有一个MySQL查询:

select * from table1 LIMIT 10,20

我该如何在SQL Server中实现这个功能？

- Bigballs

---

既然这个问题先被提出，那么另一个问题不就是重复的吗？ - Tab Alleman

请参见：如果一个问题已经有了更好的答案，我应该将其标记为重复吗？ - kenorb

@Bigballs 自 2012 年以来，被接受的答案是错误的且极其低效。 - Panagiotis Kanavos

Answer 1

从SQL SERVER 2005开始，您可以这样做...

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 10 AND 20;

或者对于2000及以下版本，可以使用类似这样的东西...

SELECT TOP 10 * FROM (SELECT TOP 20 FROM Table ORDER BY Id) ORDER BY Id DESC

- Leon Tayson

---

如果表中有例如14行，那么第二个查询将失败。它会给你第5到14行，但你想要的是第11到14行。通常情况下，在结果的最后一页上，如果总行数不是该“页面”大小的倍数，则此查询将失败。 - Bill Karwin

微软又一次把这么简单的事情弄得如此复杂！ - Martin

以下是我在 SQL Server Management Studio 2017 中使用的方法：SELECT * FROM [dbo].<插入表名> WHERE @@ROWCOUNT BETWEEN <插入最小值> 和 <插入最大值>。 - Artorias2718

真是太棒了，在 MS SQL Server 2017 的 select 语句中它工作得像魔法一样。 - PatsonLeaner

这不是任何当前 SQL Server 版本的好答案。这将扫描整个表以计算 ROW_NUMBER()，然后再进行过滤。 - Panagiotis Kanavos

Answer 2

从 SQL SERVER 2012 开始，您可以使用 OFFSET FETCH 子句：

USE AdventureWorks;
GO
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader 
ORDER BY SalesOrderID
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY;
GO

http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx

当使用ORDER BY排序时，如果排序的结果不是唯一的，则此方法可能无法正常工作。

如果将查询修改为按OrderDate排序，则返回的结果集与预期的不同。

- user4047259

---

使用'with'只需要一半的时间来完成查询 - 参见@Leon Tayson的答案。我不知道微软是如何使它变得那么慢的。 - isHuman

为什么这不是被接受的答案？我们都已经到了2018年了！ - Skipper

@Skipper 对的，原来被接受的仍然有效。让我们只是点赞这个来反映更新。 - kronn

@kronn的工作并不等同于“好”。那个查询将扫描并锁定整个表。 - Panagiotis Kanavos

Answer 3

这是我如何在MS SQL Server 2012中限制结果的方法：

SELECT * 
FROM table1
ORDER BY columnName
  OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

注意：OFFSET 只能与ORDER BY一起使用或配合使用。

解释代码行 OFFSET xx ROWS FETCH NEXT yy ROW ONLY

xx是表格中你想要开始提取的记录/行号，例如：如果表格1中有40条记录，则上述代码将从第10行开始提取。

yy是你想要从表格中提取的记录/行数。

以前面的例子为基础：如果表格1有40条记录，并且你从第10行开始提取，然后获取下一个10行(yy)。这意味着上述代码将从表格1的第10行开始提取记录，并在20行结束，因此提取10-20行。

查看链接了解更多关于OFFSET的信息。

- Jeremy

---

同意这个。对我来说，我需要在我的自定义本地查询中使用这个条件，因此JPA的普通findBy子句并没有帮助太多。这个选项按预期工作。请参考我见过的最好的参考资料之一： https://www.sqlservertutorial.net/sql-server-basics/sql-server-offset-fetch/ - techsavvy

Answer 4

这几乎是我去年十月提出的一个问题的重复： 在 Microsoft SQL Server 2000 中模拟 MySQL LIMIT 子句

如果你正在使用 Microsoft SQL Server 2000，没有好的解决方案。大多数人不得不采用在临时表中捕获查询结果，带有 IDENTITY 主键。然后使用 BETWEEN 条件查询主键列。

如果你正在使用 Microsoft SQL Server 2005 或更高版本，则可以使用 ROW_NUMBER() 函数，因此可以获得相同的结果但避免使用临时表。

SELECT t1.*
FROM (
    SELECT ROW_NUMBER OVER(ORDER BY id) AS row, t1.*
    FROM ( ...original SQL query... ) t1
) t2
WHERE t2.row BETWEEN @offset+1 AND @offset+@count;

您也可以将其编写为公共表达式，如@Leon Tayson的答案中所示。

- Bill Karwin

---

ROW_NUMBER() OVER (ORDER BY) 在 ANSI SQL:2003 中是有效的，尽管除了 SQL Server 之外的 DBMSs 支持非常有限。当然，它也相当笨重... - bobince

@bobince：事实证明，Oracle、Microsoft SQL Server 2005、IBM DB2和PostgreSQL 8.4都支持窗口函数。这覆盖了绝大部分的SQL市场。如果使用MySQL、SQLite或上述数据库的旧版本，则仅有零星支持。 - Bill Karwin

Answer 5

SELECT  *
FROM    (
        SELECT  TOP 20
                t.*, ROW_NUMBER() OVER (ORDER BY field1) AS rn
        FROM    table1 t
        ORDER BY
                field1
        ) t
WHERE   rn > 10

- Quassnoi

---

好的，我刚刚检查了一下，SQL Server在ORDER BY子句中有索引列时，会聪明地停止ROW_NUMBER()条件。 - Quassnoi

Answer 6

SELECT TOP 10 * FROM table;

与

是相同的。

SELECT * FROM table LIMIT 0,10;

这篇文章介绍了如何在MsSQL中实现Limit，非常值得一读，特别是评论部分。

- &#211;lafur Waage

---

谢谢，但我想要10到20之间的记录，有办法吗？ - Bigballs

这个回答并没有回应原来的问题，但如果像我这样的人需要知道如何获取前N个结果并通过谷歌等途径来到这里，它是有用的。 - brianlmerritt

Answer 7

在语法上，MySQL的LIMIT查询大致如下：

SELECT * FROM table LIMIT OFFSET, ROW_COUNT

这可以在 Microsoft SQL Server 中进行翻译，如下：

SELECT * FROM 
(
    SELECT TOP #{OFFSET+ROW_COUNT} *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rnum 
    FROM table
) a
WHERE rnum > OFFSET

现在你的查询 select * from table1 LIMIT 10,20 将变成这样：

SELECT * FROM 
(
    SELECT TOP 30 *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rnum 
    FROM table1
) a
WHERE rnum > 10 

- user1134160

Answer 8

Easy way

MYSQL:

SELECT 'filds' FROM 'table' WHERE 'where' LIMIT 'offset','per_page'

MSSQL：

SELECT 'filds' FROM 'table' WHERE 'where' ORDER BY 'any' OFFSET 'offset' 
ROWS FETCH NEXT 'per_page' ROWS ONLY

ORDER BY是必须的

- user7141158

---

这是唯一正确的答案。 - Mr Heelis

Answer 9

一定要尝试。在下面的查询中，你可以看到group by、order by、跳过行和限制行。

select emp_no , sum(salary_amount) from emp_salary
Group by emp_no 
ORDER BY emp_no 
OFFSET 5 ROWS       -- Skip first 5 
FETCH NEXT 10 ROWS ONLY; -- limit to retrieve next 10 row after skiping rows

- M Danish

Answer 10

这是我尽量避免使用微软服务器的原因之一...但无论如何，有时你只是没有选择（耶！我不得不使用过时的版本！！）。

我的建议是创建一个虚拟表：

来自：

SELECT * FROM table

致：

CREATE VIEW v_table AS    
    SELECT ROW_NUMBER() OVER (ORDER BY table_key) AS row,* FROM table

然后只需要查询：

SELECT * FROM v_table WHERE row BETWEEN 10 AND 20

如果添加或删除字段，“行”将自动更新。

这个选项的主要问题是ORDER BY是固定的。因此，如果您想要不同的顺序，您需要创建另一个视图。

更新：

这种方法还有另一个问题：如果您尝试过滤数据，它将无法按预期工作。例如，如果您执行：

SELECT * FROM v_table WHERE field = 'test' AND row BETWEEN 10 AND 20

WHERE仅限于在第10行到第20行之间的数据（而不是搜索整个数据集并限制输出）。

- lepe