如何在OpenEdge SQL中模拟SELECT ... LIMIT，OFFSET? [英] How to mimic SELECT ... LIMIT, OFFSET in OpenEdge SQL?

问题描述

能够在查询中返回的所有行中选择滑动窗口"子集在大多数SQL实现中是很常见的.常见的用例是分页.例如，假设我有一个搜索页面，每个页面上有10个结果.对于支持LIMIT和OFFSET关键字的实现，用于返回每一页结果的查询如下:第一页将使用SELECT ... LIMIT 10 OFFSET 0，第二页将使用SELECT ... LIMIT 10 OFFSET 10，第三页将使用SELECT ... LIMIT 10 OFFSET 20等等(请注意，OFFSET在LIMIT之前生效).

It's a common thing in most SQL implementations to be able to select a "sliding window" subset of all the rows returned in a query. A common use case for this is pagination. For example, say I have a search page with 10 results on each page. For implementations that support LIMIT and OFFSET keywords, the query used to return results for each page would be as follows: page one would use SELECT ... LIMIT 10 OFFSET 0, page 2 would use SELECT ... LIMIT 10 OFFSET 10, page 3 would use SELECT ... LIMIT 10 OFFSET 20, etc. (note that the OFFSET takes effect before the LIMIT).

无论如何，我试图在OpenEdge的SQL引擎中模仿此功能.我已经弄清楚了SELECT TOP基本上等同于LIMIT，但是我找不到与OFFSET类似的东西(我不认为有 exact 等效). SQL Server和Oracle也缺少OFFSET，但是它们分别具有一个名为ROWCOUNT和ROWNUM的伪列，可以用来模拟使用嵌套选择的行为(请参见此处).

Anyway, I'm trying to mimic this functionality in OpenEdge's SQL engine. I've already figured out that SELECT TOP is basically equivalent to LIMIT, however I can't find anything similar to OFFSET (I don't think there is an exact equivalent). SQL Server and Oracle also lack an OFFSET, but they have a pseudocolumn called ROWCOUNT and ROWNUM, respectively, that can be used to mimic the behavior using nested selects (see here and here).

在 10.2B SQL参考文档中，第49页底部有一个标题为 TOP子句的小节

In the 10.2B SQL Reference doc, p49 there is a subsection entitled TOP clause that says at the bottom

SELECT TOP是Oracle ROWNUM的功能等效项 功能.请注意，SELECT TOP的定义仅取决于 限制结果集大小，然后优化器确定如何使用 此限制可实现最佳数据访问.因此，SELECT TOP并没有全部 用于定义Oracle ROWNUM含义的过程规则" 短语.

SELECT TOP is the functional equivalent of the Oracle ROWNUM functionality. Note that SELECT TOP is defined simply in terms of a limit on the result set size, and the optimizer determines how to use this limit for best data access. Thus, SELECT TOP does not have all the "procedural rules" used to define the meaning of the Oracle ROWNUM phrase.

但是，这似乎不准确，因为根据TOP的语法，它不能像ROWNUM可以那样用作谓词(例如，我不能说SELECT * FROM Customer WHERE TOP > 5 AND TOP < 10).因此TOP在功能上不等同于ROWNUM.

However, this seems to be inaccurate as according to TOP's syntax it cannot be used as a predicate like ROWNUM can (e.g. I can't say SELECT * FROM Customer WHERE TOP > 5 AND TOP < 10). So TOP is not functionally equivalent to ROWNUM.

有什么方法可以模仿OFFSET，还是我不走运?

Is there any way to mimic OFFSET, or am I out of luck?

推荐答案

OpenEdge 11.2添加了对

OpenEdge 11.2 added support for OFFSET and FETCH clauses to SQL SELECT queries; versions of OpenEdge below 11.2 do not support OFFSET/FETCH.

从 11.2产品文档"SQL参考"文档中:

From the 11.2 product documentation "SQL Reference" document:

The OFFSET clause specifies the number of rows to skip, before starting to return rows
from the query expression. The FETCH clause specifies the number of rows to return,
after processing the OFFSET clause.

值得注意的是， TOP和OFFSET/FETCH子句是互斥的-TOP不能在使用OFFSET或FETCH的查询中使用.

It's worth noting that the TOP and OFFSET/FETCH clauses are mutually exclusive - TOP cannot be used in a query that uses OFFSET or FETCH.

跳过前10行，并返回其余的合格行:

Skip the first 10 rows and return the rest of the qualified rows:

SELECT OrderID,OrderDate,custID,filler
FROM dbo.Orders OFFSET 10;

返回前10行，而不跳过任何行:

Return the first 10 rows without skipping any:

SELECT OrderID,OrderDate,custID,filler
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderID DESC
FETCH FIRST 10 ROWS ONLY;

返回查询结果集中的第51至60行:

Return rows 51 through 60 in the result set of the query:

SELECT OrderID,OrderDate,custID,filler
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderID DESC
OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY;

这篇关于如何在OpenEdge SQL中模拟SELECT ... LIMIT，OFFSET?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！