Wie kann ich die Anzahl der von einer Oracle-Abfrage zurückgegebenen Zeilen nach der Bestellung begrenzen?

Question

Gibt es eine Möglichkeit, eine Oracle Abfrage so verhalten, als enthalte sie eine MySQL limit Klausel?

In MySQL kann ich dies tun:

select * 
from sometable
order by name
limit 20,10

um die 21. bis 30. Reihe zu erhalten (die ersten 20 überspringen, die nächsten 10 geben). Die Zeilen werden nach der order by Die Liste beginnt also wirklich mit dem 20. Namen in alphabetischer Reihenfolge.

In Oracle ist das einzige, was die Leute erwähnen, die rownum Pseudo-Spalte, aber sie wird ausgewertet vor order by Dies bedeutet Folgendes:

select * 
from sometable
where rownum <= 10
order by name

gibt eine zufällige Menge von zehn nach Namen geordneten Zeilen zurück, was normalerweise nicht das ist, was ich will. Außerdem ist es nicht möglich, einen Offset festzulegen.

Answer 1

Für jede von einer Abfrage zurückgegebene Zeile gibt die Pseudospalte ROWNUM eine Zahl zurück, die die Reihenfolge angibt, in der Oracle die Zeile aus einer Tabelle oder einer Gruppe von verbundenen Zeilen auswählt. Die erste ausgewählte Zeile hat einen ROWNUM-Wert von 1, die zweite einen von 2 und so weiter.

  SELECT * FROM sometable1 so
    WHERE so.id IN (
    SELECT so2.id from sometable2 so2
    WHERE ROWNUM <=5
    )
    AND ORDER BY so.somefield AND ROWNUM <= 100 

Ich habe dies implementiert in oracle Server 11.2.0.1.0

Answer 2

select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
 FROM EMP ) EMP  where ROWID=5

größere Werte dann herausfinden

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID>5

weniger als Werte herausfinden

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID=5

Answer 3

(ungetestet) etwas wie dieses könnte die Aufgabe erfüllen

WITH
base AS
(
    select *                   -- get the table
    from sometable
    order by name              -- in the desired order
),
twenty AS
(
    select *                   -- get the first 30 rows
    from base
    where rownum < 30
    order by name              -- in the desired order
)
select *                       -- then get rows 21 .. 30
from twenty
where rownum > 20
order by name                  -- in the desired order

Es gibt auch die analytische Funktion Rang, mit der man nach etwas ordnen kann.