问题描述
我正在尝试做类似的事情:
I'm trying to do something like :
SELECT * FROM table LIMIT 10,20
或
SELECT * FROM table LIMIT 10 OFFSET 10
但使用 SQL Server
but using SQL Server
我找到的唯一解决方案 看起来有点矫枉过正:
The only solution I found looks like overkill:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases
) a WHERE row > 5 and row <= 10
我也找到:p>
SELECT TOP 10 * FROM stuff;
...但这不是我想做的，因为我无法指定起始限制.
... but it's not what I want to do since I can't specify the starting limit.
我还有其他方法吗?
另外，只是好奇，SQL Server 不支持 LIMIT 函数或类似函数是否有原因?我不想刻薄，但这听起来确实像是 DBMS 需要的东西……如果确实如此，那么我很抱歉我这么无知！在过去的 5 年里，我一直在使用 MySQL 和 SQL+，所以...
Also, just curious, is there a reason why doesn't SQL Server support the LIMIT function or something similar? I don't want to be mean, but that really sounds like something a DBMS needs ... If it does, then I'm sorry for being so ignorant! I've been working with MySQL and SQL+ for the past 5 years so...
推荐答案
LIMIT 子句不是标准 SQL 的一部分.MySQL、PostgreSQL 和 SQLite 支持它作为 SQL 的供应商扩展.
The LIMIT clause is not part of standard SQL. It's supported as a vendor extension to SQL by MySQL, PostgreSQL, and SQLite.
其他品牌的数据库可能具有类似的功能(例如 Microsoft SQL Server 中的 TOP)，但它们的工作方式并不总是相同.
Other brands of database may have similar features (e.g. TOP in Microsoft SQL Server), but these don't always work identically.
很难在 Microsoft SQL Server 中使用 TOP 来模仿 LIMIT 子句.在某些情况下它不起作用.
It's hard to use TOP in Microsoft SQL Server to mimic the LIMIT clause. There are cases where it just doesn't work.
您展示的使用 ROW_NUMBER() 的解决方案在 Microsoft SQL Server 2005 及更高版本中可用.这是仅作为查询的一部分工作的最佳解决方案(目前).
The solution you showed, using ROW_NUMBER() is available in Microsoft SQL Server 2005 and later. This is the best solution (for now) that works solely as part of the query.
另一种解决方案是使用 TOP 获取第一个 count + offset 行，然后使用 API 查找第一个 偏移量行.
Another solution is to use TOP to fetch the first count + offset rows, and then use the API to seek past the first offset rows.
另见:
- "在 Microsoft SQL Server 2000 中模拟 MySQL LIMIT 子句"
- "ASP.NET 中大型结果集的分页"
这篇关于SQL Server 中的限制 10..20的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！