在 Microsoft SQL Server 2000 中模拟 MySQL LIMIT 子句

2021-11-20 00:00:00 sql mysql zend-framework sql-server

当我处理 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.

所以我的问题是，您能否想出一种方法来模拟 Microsoft SQL Server 2000 中的 LIMIT 功能，仅使用 SQL?不使用游标或 T-SQL 或存储过程.它必须支持 LIMIT 的两个参数，包括计数和偏移量.使用临时表的解决方案也是不可接受的.

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 中使用 ROW_NUMBER() 函数，则存在一个很好的解决方案服务器 2005.

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

相关文章