问题描述
我正在尝试做类似的事情:
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
我也发现::>
SELECT TOP 10 * FROM stuff;
...但这不是我想要做的，因为我无法指定起始限制.
... but it's not what I want to do since I can't specify the starting limit.
我还有其他方法可以做到这一点吗?
Is there another way for me to do that ?
另外，只是好奇，是否有原因 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！