问题描述
能够在查询中返回的所有行中选择滑动窗口"子集在大多数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
但是,这似乎不准确,因为根据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添加了对 OFFSET
和FETCH
子句到SQL SELECT
查询;版本低于11.2的OpenEdge版本不支持OFFSET
/FETCH
.
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?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!