如何使用 Microsoft SQL Server 实现 LIMIT？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/603724/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to implement LIMIT with Microsoft SQL Server?
提问by Bigballs
I have this query with mysql :
我对 mysql 有这个查询：
select * from table1 LIMIT 10,20
How can I do this with Microsoft sql ?
如何使用 Microsoft sql 执行此操作？
采纳答案by Leon Tayson
Starting SQL SERVER 2005, you can do this...
启动 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;
or something like this for 2000 and below versions...
或 2000 及以下版本的类似内容...
SELECT TOP 10 * FROM (SELECT TOP 20 FROM Table ORDER BY Id) ORDER BY Id DESC
回答by ceejayoz
Clunky, but it'll work.
笨重，但它会工作。
SELECT TOP 10 * FROM table WHERE id NOT IN (SELECT TOP 10 id FROM table ORDER BY id) FROM table ORDER BY id
MSSQL's omission of a LIMIT clause is criminal, IMO. You shouldn't have to do this kind of kludgy workaround.
MSSQL 对 LIMIT 子句的遗漏是犯罪行为，IMO。你不应该做这种笨拙的解决方法。
回答by user4047259
Starting with SQL SERVER 2012, you can use the OFFSET FETCH Clause:
从 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
http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx
This may not work correctly when the order by is not unique.
当 order by 不唯一时，这可能无法正常工作。
If the the query is modified to ORDER BY OrderDate, the result set returned is not as expected.
如果将查询修改为 ORDER BY OrderDate，则返回的结果集不符合预期。
回答by Bill Karwin
This is almost a duplicate of a question I asked in October: Emulate MySQL LIMIT clause in Microsoft SQL Server 2000
这几乎是我在 10 月份问的一个问题的重复： Emulate MySQL LIMIT clause in Microsoft SQL Server 2000
If you're using Microsoft SQL Server 2000, there is no good solution. Most people have to resort to capturing the result of the query in a temporary table with a IDENTITYprimary key. Then query against the primary key column using a BETWEENcondition.
如果您使用的是 Microsoft SQL Server 2000，则没有好的解决方案。大多数人不得不求助于在带有IDENTITY主键的临时表中捕获查询结果。然后使用BETWEEN条件查询主键列。
If you're using Microsoft SQL Server 2005 or later, you have a ROW_NUMBER()function, so you can get the same result but avoid the temporary table.
如果您使用的是 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;
You can also write this as a common table expressionas shown in @Leon Tayson's answer.
回答by Jeremy
This is how I limit the results in MS SQL Server 2012:
这就是我在 MS SQL Server 2012 中限制结果的方式：
SELECT *
FROM table1
ORDER BY columnName
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
NOTE: OFFSETcan only be used with or in tandem to ORDER BY.
注意：OFFSET只能与ORDER BY.
To explain the code line OFFSET xx ROWS FETCH NEXT yy ROW ONLY
解释代码行 OFFSET xx ROWS FETCH NEXT yy ROW ONLY
The xxis the record/row number you want to start pulling from in the table, i.e: If there are 40 records in table 1, the code above will start pulling from row 10.
该xx是你要开始从下表中，即拉动记录/行数：如果有40条记录在表1中，上面的代码将开始从第10行拉。
The yyis the number of records/rows you want to pull from the table.
该yy是你想从表中拉记录/行数。
To build on the previous example: If table 1 has 40 records and you began pulling from row 10 and grab the NEXT set of 10 (yy).
That would mean, the code above will pull the records from table 1 starting at row 10 and ending at 20. Thus pulling rows 10 - 20.
以前面的示例为基础：如果表 1 有 40 条记录，并且您从第 10 行开始提取并获取 NEXT 集 10 ( yy)。这意味着，上面的代码将从表 1 中从第 10 行开始到第 20 行拉出记录。因此拉出第 10 - 20 行。
Check out the link for more info on OFFSET
查看链接以获取有关OFFSET 的更多信息
回答by Quassnoi
SELECT *
FROM (
SELECT TOP 20
t.*, ROW_NUMBER() OVER (ORDER BY field1) AS rn
FROM table1 t
ORDER BY
field1
) t
WHERE rn > 10
回答by Quassnoi
Syntactically MySQL LIMIT query is something like this:
语法上 MySQL LIMIT 查询是这样的：
SELECT * FROM table LIMIT OFFSET, ROW_COUNT
This can be translated into Microsoft SQL Server like
这可以翻译成 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
Now your query select * from table1 LIMIT 10,20will be like this:
现在您的查询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
回答by lepe
This is one of the reasons I try to avoid using MS Server... but anyway. Sometimes you just don't have an option (yei! and I have to use an outdated version!!).
这是我尽量避免使用 MS Server 的原因之一……但无论如何。有时你只是没有选择（是的！我必须使用过时的版本！！）。
My suggestion is to create a virtual table:
我的建议是创建一个虚拟表：
From:
从：
SELECT * FROM table
To:
到：
CREATE VIEW v_table AS
SELECT ROW_NUMBER() OVER (ORDER BY table_key) AS row,* FROM table
Then just query:
然后只需查询：
SELECT * FROM v_table WHERE row BETWEEN 10 AND 20
If fields are added, or removed, "row" is updated automatically.
如果添加或删除字段，“行”会自动更新。
The main problem with this option is that ORDER BY is fixed. So if you want a different order, you would have to create another view.
此选项的主要问题是 ORDER BY 已修复。因此，如果您想要不同的顺序，则必须创建另一个视图。
UPDATE
更新
There is another problem with this approach: if you try to filter your data, it won't work as expected. For example, if you do:
这种方法还有另一个问题：如果您尝试过滤数据，它将无法按预期工作。例如，如果你这样做：
SELECT * FROM v_table WHERE field = 'test' AND row BETWEEN 10 AND 20
WHERE becomes limited to those data which are in the rows between 10 and 20 (instead of searching the whole dataset and limiting the output).
WHERE 变得仅限于 10 到 20 行之间的那些数据（而不是搜索整个数据集并限制输出）。
回答by M Danish
Must try. In below query, you can see group by, order by, Skip rows, and limit rows.
一定要试。在下面的查询中，您可以看到分组依据、排序依据、跳过行和限制行。
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
回答by Julian Moreno
SELECT
*
FROM
(
SELECT
top 20 -- ($a) number of records to show
*
FROM
(
SELECT
top 29 -- ($b) last record position
*
FROM
table -- replace this for table name (i.e. "Customer")
ORDER BY
2 ASC
) AS tbl1
ORDER BY
2 DESC
) AS tbl2
ORDER BY
2 ASC;
-- Examples:
-- Show 5 records from position 5:
-- $a = 5;
-- $b = (5 + 5) - 1
-- $b = 9;
-- Show 10 records from position 4:
-- $a = 10;
-- $b = (10 + 4) - 1
-- $b = 13;
-- To calculate $b:
-- $b = ($a + position) - 1
-- For the present exercise we need to:
-- Show 20 records from position 10:
-- $a = 20;
-- $b = (20 + 10) - 1
-- $b = 29;
