在Microsoft SQL Server 2000中模拟MySQL限制子句

When I worked on the Zend Framework's database component, we tried to abstract the functionality of the LIMIT clause supported by MySQL, PostgreSQL, and SQLite. That is, creating a query could be done this way:

当我研究Zend框架的数据库组件时，我们试图抽象MySQL、PostgreSQL和SQLite支持的LIMIT子句的功能。也就是说，创建查询可以这样做:

$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语言的一部分)。如果可以生成行号，则将整个查询作为派生表，并在外部查询中使用。这是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 LIMIT functionality 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.

所以我的问题是，你能想出一个方法来模拟微软SQL Server 2000的极限功能，仅仅使用SQL吗?不使用游标或T-SQL或存储过程。它必须支持极限的两个论点，计数和抵消。使用临时表的解决方案也不被接受。

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 LIMIT in MS SQL Server 2000. A good solution exists if you can use the ROW_NUMBER() function in MS SQL Server 2005.

在MS SQL Server 2000中，没有一个通用的解决方案似乎存在于仿真限制。如果您可以在MS SQL Server 2005中使用ROW_NUMBER()函数，那么就存在一个很好的解决方案。

4 个解决方案

#1

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

#2

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关键字，所以这一点很清楚。当您需要使用偏移量进行限制时，您可以尝试一些类似前面描述的技巧，但是它们都增加了一些开销，例如，以一种方式订购然后另一种方式订购，或者昂贵的不使用。我认为这些瀑布是不需要的。我的oppinion中最干净的解决方案是使用TOP而不使用SQL端的偏移量，然后使用适当的客户端方法(如php中的mssql_data_seek)查找所需的起始记录。虽然这不是一个纯粹的SQL解决方案，但我认为它是最好的解决方案，因为它不增加任何开销(如果您担心会发生这种情况，那么当您查找它们时，跳投记录不会在网络上传输)。

#3

SELECT TOP n *
FROM tablename
WHERE key NOT IN (
    SELECT TOP x key
    FROM tablename
    ORDER BY key
    DESC
);

#4

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到SQL为以下linq到SQL语句生成的代码，并从那里开始。实现该代码的MSFT工程师多年来一直是SQL团队的一员，他知道自己在做什么。

var result = myDataContext.mytable.Skip(pageIndex * pageSize).Take(pageSize)

var = myDataContext.mytable结果。跳过(pageIndex *页大小),(页大小)

#1