在Microsoft SQL Server 2000中模拟MySQL LIMIT子句 [英] Emulate MySQL LIMIT clause in Microsoft SQL Server 2000

问题描述

当我使用 Zend Framework的数据库组件时，我们尝试了来抽象MySQL，PostgreSQL和SQLite支持的LIMIT子句的功能.也就是说，可以通过以下方式创建查询:

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:

$select = $db->select();
$select->from('mytable');
$select->order('somecolumn');
$select->limit(10, 20);

当数据库支持LIMIT时，将生成如下所示的SQL查询:

When the database supports LIMIT, this produces an SQL query like the following:

SELECT * FROM mytable ORDER BY somecolumn LIMIT 10, 20

对于不支持LIMIT的品牌数据库，这更为复杂(顺便说一句，该子句不是标准SQL语言的一部分).如果可以生成行号，则使整个查询成为派生表，并在外部查询中使用BETWEEN.这是针对Oracle和IBM DB2的解决方案. Microsoft SQL Server 2005具有类似的行号功能，因此可以通过以下方式编写查询:

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:

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;

但是，Microsoft SQL Server 2000没有ROW_NUMBER()功能.

However, Microsoft SQL Server 2000 doesn't have the ROW_NUMBER() function.

所以我的问题是，您能否提出一种仅使用SQL在Microsoft SQL Server 2000中模拟LIMIT功能的方法?无需使用游标或T-SQL或存储过程.它必须同时支持LIMIT的两个参数，即count和offset.使用临时表的解决方案也不可接受.

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.

MS SQL Server 2000的最常见解决方案似乎类似于以下解决方案，例如，获取第50至75行:

The most common solution for MS SQL Server 2000 seems to be like the one below, for example to get rows 50 through 75:

SELECT TOP 25 *
FROM ( 
  SELECT TOP 75 *
  FROM   table 
  ORDER BY BY field ASC
) a 
ORDER BY field DESC;

但是，如果总结果集为60行，则此方法不起作用.内部查询返回60行，因为该行位于前75位.然后外部查询返回35-60行，该行不适合所需的页面" 50-75.基本上，除非您需要结果集的最后一个页面"恰好不是页面大小的倍数，否则此解决方案才有效.

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.

另一种解决方案效果更好，但前提是您可以假定结果集包含唯一的列:

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
);

结论:

在MS SQL Server 2000中似乎不存在用于模拟LIMIT的通用解决方案.如果可以在MS SQL Server 2005中使用ROW_NUMBER()函数，则存在一个好的解决方案.

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.

推荐答案

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

这篇关于在Microsoft SQL Server 2000中模拟MySQL LIMIT子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！