sql - oracle rownum 1




如何在訂購後限制Oracle查詢返回的行數? (9)

(未經測試)可能會完成這項工作

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

還有分析功能等級,可以用來排序。

有沒有辦法讓Oracle查詢的行為像包含MySQL limit條款一樣?

MySQL ,我可以這樣做:

select * 
from sometable
order by name
limit 20,10

以獲得第21到第30排(跳過前20,下10)。 這些行在order by之後被選中,所以它按字母順序從第20個名字開始。

Oracle ,人們提到的唯一的東西是rownum偽列,但是 order by 之前進行評估,這意味著:

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

將返回隨機設置的10行按名稱排序,這通常不是我想要的。 它也不允許指定偏移量。


你可以像這樣使用子查詢

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

還可以看看On ROWNUM主題, 在Oracle / AskTom中限制結果以獲取更多信息。

更新 :為了限制結果的下限和上限,事情會變得更加臃腫

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(從指定的AskTom文章複製)

更新2 :從Oracle 12c(12.1)開始,有一種可用於限制行或從偏移量開始的語法。

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

有關更多示例,請參閱此答案 。 感謝Krumia的提示。


只有一個嵌套查詢的分析解決方案:

SELECT * FROM
(
   SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
) 
WHERE MyRow BETWEEN 10 AND 20;

可以使用Rank()替換Row_Number()但如果名稱有重複值,則可能會返回比您期望的更多的記錄。


在Oracle 12c上(請參閱SQL參考中的行限制子句):

SELECT * 
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

如果您不在Oracle 12C上,則可以使用下面的TOP N查詢。

SELECT *
 FROM
   ( SELECT rownum rnum
          , a.*
       FROM sometable a 
   ORDER BY name
   )
WHERE rnum BETWEEN 10 AND 20;

你甚至可以在子句中使用如下的子句

WITH b AS
( SELECT rownum rnum
      , a.* 
   FROM sometable a ORDER BY name
) 
SELECT * FROM b 
WHERE rnum BETWEEN 10 AND 20;

實際上,我們正​​在創建一個內聯視圖並將rownum重命名為rnum。 您可以在主查詢中使用rnum作為過濾條件。


少SELECT語句。 另外,性能消耗較少。 致謝:[email protected]

SELECT *
    FROM   (SELECT t.*,
                   rownum AS rn
            FROM   shhospede t) a
    WHERE  a.rn >= in_first
    AND    a.rn <= in_first;

我為以下方法做了一些性能測試:

Asktom

select * from (
  select a.*, ROWNUM rnum from (
    <select statement with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

分析

select * from (
  <select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW

短的選擇

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

結果

表有1000萬條記錄,排序在一個沒有建立索引的日期時間行上:

  • 解釋計劃對所有三個選擇顯示相同的值(323168)
  • 但勝利者是AskTom(分析後緊隨其後)

選擇前10行:

  • AskTom:28-30秒
  • 分析:33-37秒
  • 短的選擇:110-140秒

選擇100,000到100,010之間的行:

  • AskTom:60秒
  • 分析:100秒

選擇9,000,000和9,000,010之間的行:

  • AskTom:130秒
  • 分析:150秒

我開始準備參加Oracle 1z0-047考試,針對12c進行了驗證。在為此準備時,我遇到了一種名為'FETCH FIRST'的12c增強功能。它使您能夠根據您的方便獲取行/限制行。 它有幾個選項可用

- FETCH FIRST n ROWS ONLY
 - OFFSET n ROWS FETCH NEXT N1 ROWS ONLY // leave the n rows and display next N1 rows
 - n % rows via FETCH FIRST N PERCENT ROWS ONLY

例:

Select * from XYZ a
order by a.pqr
FETCH FIRST 10 ROWS ONLY

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

發現值越大

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

少於數值就會發現

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




limit