在 Microsoft SQL Server 2000 中模拟 MySQL LIMIT 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/216673/
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
Emulate MySQL LIMIT clause in Microsoft SQL Server 2000
提问by Bill Karwin
When I worked on the Zend Framework's database component, we tried to abstract the functionality of the LIMITclause supported by MySQL, PostgreSQL, and SQLite. That is, creating a query could be done this way:
当我在Zend Framework 的数据库组件上工作时，我们试图抽象出LIMITMySQL、PostgreSQL 和 SQLite 支持的子句的功能。也就是说，可以通过以下方式创建查询：
$select = $db->select();
$select->from('mytable');
$select->order('somecolumn');
$select->limit(10, 20);
When the database supports LIMIT, this produces an SQL query like the following:
当数据库支持 时LIMIT，这会生成一个 SQL 查询，如下所示：
SELECT * FROM mytable ORDER BY somecolumn LIMIT 10, 20
This was more complex for brands of database that don't support LIMIT(that clause is not part of the standard SQL language, by the way). If you can generate row numbers, make the whole query a derived table, and in the outer query use BETWEEN. This was the solution for Oracle and IBM DB2. Microsoft SQL Server 2005 has a similar row-number function, so one can write the query this way:
对于不支持的数据库品牌，这更加复杂LIMIT（顺便说一下，该子句不是标准 SQL 语言的一部分）。如果可以生成行号，请将整个查询设为派生表，并在外部查询中使用BETWEEN. 这是 Oracle 和 IBM DB2 的解决方案。Microsoft SQL Server 2005 有一个类似的行号函数，因此可以这样编写查询：
SELECT z2.*
FROM (
SELECT ROW_NUMBER OVER(ORDER BY id) AS zend_db_rownum, z1.*
FROM ( ...original SQL query... ) z1
) z2
WHERE z2.zend_db_rownum BETWEEN @offset+1 AND @offset+@count;
However, Microsoft SQL Server 2000 doesn't have the ROW_NUMBER()function.
但是，Microsoft SQL Server 2000 没有该ROW_NUMBER()功能。
So my question is, can you come up with a way to emulate the LIMITfunctionality in Microsoft SQL Server 2000, solely using SQL? Without using cursors or T-SQL or a stored procedure. It has to support both arguments for LIMIT, both count and offset. Solutions using a temporary table are also not acceptable.
所以我的问题是，你能想出一种方法来模拟LIMITMicrosoft SQL Server 2000 中的功能，仅使用 SQL 吗？不使用游标或 T-SQL 或存储过程。它必须支持 的两个参数LIMIT，计数和偏移量。使用临时表的解决方案也是不可接受的。
Edit:
编辑：
The most common solution for MS SQL Server 2000 seems to be like the one below, for example to get rows 50 through 75:
MS SQL Server 2000 最常见的解决方案似乎如下所示，例如获取第 50 到 75 行：
SELECT TOP 25 *
FROM (
SELECT TOP 75 *
FROM table
ORDER BY BY field ASC
) a
ORDER BY field DESC;
However, this doesn't work if the total result set is, say 60 rows. The inner query returns 60 rows because that's in the top 75. Then the outer query returns rows 35-60, which doesn't fit in the desired "page" of 50-75. Basically, this solution works unless you need the last "page" of a result set that doesn't happen to be a multiple of the page size.
但是，如果总结果集是 60 行，则这不起作用。内部查询返回 60 行，因为它在前 75 行中。然后外部查询返回第 35-60 行，这不适合所需的 50-75“页面”。基本上，除非您需要结果集的最后一个“页面”而不是页面大小的倍数，否则此解决方案是有效的。
Edit:
编辑：
Another solution works better, but only if you can assume the result set includes a column that is unique:
另一种解决方案效果更好，但前提是您可以假设结果集包含唯一的列：
SELECT TOP n *
FROM tablename
WHERE key NOT IN (
SELECT TOP x key
FROM tablename
ORDER BY key
);
Conclusion:
结论：
No general-purpose solution seems to exist for emulating LIMITin MS SQL Server 2000. A good solution exists if you can use the ROW_NUMBER()function in MS SQL Server 2005.
似乎不存在用于LIMIT在 MS SQL Server 2000 中进行模拟的通用解决方案。如果您可以ROW_NUMBER()在 MS SQL Server 2005 中使用该功能，则存在一个很好的解决方案。
回答by Bill Karwin
SELECT TOP n *
FROM tablename
WHERE key NOT IN (
SELECT TOP x key
FROM tablename
ORDER BY key
DESC
);
回答by Florian Fankhauser
Here is another solution which only works in Sql Server 2005 and newer because it uses the except statement. But I share it anyway. If you want to get the records 50 - 75 write:
这是另一个仅适用于 Sql Server 2005 及更新版本的解决方案，因为它使用了 except 语句。但我还是分享了。如果你想获得记录 50 - 75 写：
select * from (
SELECT top 75 COL1, COL2
FROM MYTABLE order by COL3
) as foo
except
select * from (
SELECT top 50 COL1, COL2
FROM MYTABLE order by COL3
) as bar
回答by Florian Fankhauser
When you need LIMIT only, ms sql has the equivalent TOP keyword, so that is clear. When you need LIMIT with OFFSET, you can try some hacks like previously described, but they all add some overhead, i.e. for ordering one way and then the other, or the expencive NOT IN operation. I think all those cascades are not needed. The cleanest solution in my oppinion would be just use TOP without offset on the SQL side, and then seek to the required starting record with the appropriate client method, like mssql_data_seek in php. While this isn't a pure SQL solution, I think it is the best one because it doesn't add any overhead (the skipped-over records will not be transferred on the network when you seek past them, if that is what worries you).
当您只需要 LIMIT 时，ms sql 具有等效的 TOP 关键字，这很清楚。当您需要 LIMIT 和 OFFSET 时，您可以尝试一些前面描述的技巧，但它们都会增加一些开销，即以一种方式排序然后另一种方式排序，或者昂贵的 NOT IN 操作。我认为不需要所有这些级联。我认为最干净的解决方案是在 SQL 端使用 TOP 而不使用偏移量，然后使用适当的客户端方法寻找所需的起始记录，例如 php 中的 mssql_data_seek。虽然这不是一个纯粹的 SQL 解决方案，但我认为它是最好的解决方案，因为它不会增加任何开销（当您寻找跳过的记录时，它们不会在网络上传输，如果这让您担心的话）。
回答by Barka
I would try to implement this in my ORM as it is pretty simple there. If it really needs to be in SQL Server then I would look at the code generated by linq to sql for the following linq to sql statement and go from there. The MSFT engineer who implemented that code was part of the SQL team for many years and knew what he was doing.
我会尝试在我的 ORM 中实现它，因为它在那里非常简单。如果它真的需要在 SQL Server 中，那么我会查看 linq to sql 为以下 linq to sql 语句生成的代码，然后从那里开始。实现该代码的 MSFT 工程师在 SQL 团队工作多年，并且知道他在做什么。
var result = myDataContext.mytable.Skip(pageIndex * pageSize).Take(pageSize)
var 结果 = myDataContext.mytable.Skip(pageIndex * pageSize).Take(pageSize)
