복붙노트

[SQL] 어떻게 주문 후 오라클 쿼리에 의해 반환 된 행의 수를 제한 하는가?

SQL

어떻게 주문 후 오라클 쿼리에 의해 반환 된 행의 수를 제한 하는가?

그것은 MySQL의 제한 조항을 포함처럼 오라클 쿼리 동작합니다을 만들 수있는 방법이 있습니까?

MySQL은, 나는이 작업을 수행 할 수 있습니다 :

select * 
from sometable
order by name
limit 20,10

30 행에 21을 얻기 위해 (다음 10을주고, 처음 20을 생략). 정말 순으로 20 명을 시작하므로 행이에 의한 주문 후 선택됩니다.

오라클, 사람들이 말할 수있는 유일한 것은 ROWNUM 의사 열이지만,이 뜻에 의해 순서 전에 평가 :

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

일반적으로 내가 원하는 것이 아니다 이름으로 정렬 열 행의 임의의 집합을 반환합니다. 그것은 또한 오프셋 지정을 허용하지 않습니다.

해결법

  1. ==============================

    1.오라클 12C R1 (12.1)에서 시작, 절을 제한 행이있다. 그것은 익숙한 LIMIT 구문을 사용하지 않지만, 더 많은 옵션과 더 나은 일을 할 수 있습니다. 당신은 여기에 전체 구문을 찾을 수 있습니다. (또한 더는이 대답에 오라클 내부적으로 어떻게 작동하는지에 대한 읽기).

    오라클 12C R1 (12.1)에서 시작, 절을 제한 행이있다. 그것은 익숙한 LIMIT 구문을 사용하지 않지만, 더 많은 옵션과 더 나은 일을 할 수 있습니다. 당신은 여기에 전체 구문을 찾을 수 있습니다. (또한 더는이 대답에 오라클 내부적으로 어떻게 작동하는지에 대한 읽기).

    원래의 질문에 대답하기 위해, 여기에 쿼리는 다음과 같습니다

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

    (이전 오라클 버전의 경우,이 질문에 다른 답변을 참조하시기 바랍니다)

    다음 예는 링크 부패 방지의 희망에 링크 된 페이지에서 인용되었다.

    CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

    SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

    SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

    SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

    SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

    SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

    SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.
  2. ==============================

    2.당신은 이런 식의 하위 쿼리를 사용할 수 있습니다

    당신은 이런 식의 하위 쿼리를 사용할 수 있습니다

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

    또한 자세한 내용은 오라클 / AskTom에서 ROWNUM에 주제 및 제한 결과에서보세요.

    최신 정보: 상황이 조금 더 부풀어 얻을 모두 하부 및 상부 경계로 결과를 제한하려면

    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 : 오라클 12C 시작 (12.1) 행을 제한 또는 오프셋에서 시작 사용할 수있는 구문이있다.

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

    더 많은 예제이 답변을 참조하십시오. 힌트에 대한 Krumia 감사합니다.

  3. ==============================

    3.다음은 접근을 위해 나는 몇 가지 성능 테스트를했다 :

    다음은 접근을 위해 나는 몇 가지 성능 테스트를했다 :

    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 만 개 기록을했다 :

    첫 번째 선택 10 행했다 :

    100,000 및 100,010 사이에 행을 선택 :

    9,000,000 및 9,000,010 사이에 행을 선택 :

  4. ==============================

    4.단 하나의 중첩 된 쿼리를 가진 분석 솔루션 :

    단 하나의 중첩 된 쿼리를 가진 분석 솔루션 :

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

    순위는 () ROW_NUMBER를 대체 할 수있다 ()하지만 이름에 대한 중복 값이있는 경우 당신이 기대하는 것보다 더 많은 레코드를 반환 할 수도 있습니다.

  5. ==============================

    5.오라클 12C에 (행 SQL 참조에서 절을 제한 참조)

    오라클 12C에 (행 SQL 참조에서 절을 제한 참조)

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

    6.주문과 매김 쿼리는 오라클에서 정말 까다로운입니다.

    주문과 매김 쿼리는 오라클에서 정말 까다로운입니다.

    오라클은 ROWNUM 의사 열을 제공하는 반환 데이터베이스 테이블이나 조인 뷰의 집합에서 행을 선택하는 순서를 나타내는 숫자입니다.

    ROWNUM 문제로 많은 사람들을 유도 할 수있는 의사 열이다. ROWNUM 값은 영구적 (이것은 일반적인 오해) 행에 할당되지 않는다. ROWNUM 값이 실제로 할당 될 때 그것은 혼동 할 수있다. 이 조회하지만 질의 응집 분류 술어 전에 필터를 통과 한 후 ROWNUM 값은 행에 할당된다.

    무엇보다하는 ROWNUM 값이 할당 된 후에 만 ​​증가합니다.

    다음 쿼리 행이 반환하지 않습니다 이유입니다 :

     select * 
 from (select *
       from some_table
       order by some_column)
 where ROWNUM <= 4 and ROWNUM > 1;

    ROWNUM은 이러한 이유로 2로 증가하지 않도록 쿼리 결과의 첫 번째 행, ROWNUM> 1 술어를 통과하지 못한, 더 ROWNUM 값은 1보다 큰, 결과적으로 쿼리가 반환하는 행을 얻을 수 없습니다.

    올바르게 정의 된 쿼리는 다음과 같아야합니다 :

    select *
from (select *, ROWNUM rnum
      from (select *
            from skijump_results
            order by points)
      where ROWNUM <= 4)
where rnum > 1;

    Vertabelo 블로그에 내 기사의 페이지 매김 쿼리에 대한 자세한 내용을 알아보십시오 :

  7. ==============================

    7.이 글에서 설명한 바와 같이, SQL은 2008 표준은 SQL 결과 집합을 제한 할 수있는 다음과 같은 구문을 제공합니다 :

    이 글에서 설명한 바와 같이, SQL은 2008 표준은 SQL 결과 집합을 제한 할 수있는 다음과 같은 구문을 제공합니다 :

    SELECT
    title
FROM
    post
ORDER BY
    id DESC
FETCH FIRST 50 ROWS ONLY

    상위-N의 기록을 가져올 수, 버전 12C 이전에는 파생 테이블과 ROWNUM 의사 열을 사용했다 :

    SELECT *
FROM (
    SELECT
        title
    FROM
        post
    ORDER BY
        id DESC
)
WHERE ROWNUM <= 50
  8. ==============================

    8.적은 SELECT 문. 또한, 적은 성능이 많이 소요. anibal@upf.br 님의 크레딧

    적은 SELECT 문. 또한, 적은 성능이 많이 소요. anibal@upf.br 님의 크레딧

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

    9.허용 대답의 확장으로 오라클은 내부적으로 ROW_NUMBER / RANK 함수를 사용합니다. FETCH OFFSET 구문은 구문 설탕입니다.

    허용 대답의 확장으로 오라클은 내부적으로 ROW_NUMBER / RANK 함수를 사용합니다. FETCH OFFSET 구문은 구문 설탕입니다.

    그것은 DBMS_UTILITY.EXPAND_SQL_TEXT 절차를 사용하여 관찰 할 수있다 :

    샘플을 준비 :

    CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;
COMMIT;

    질문:

    SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

    정규이다 :

    SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
               ROW_NUMBER() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rownumber" 
      FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$$_rownumber"<=5 ORDER BY "A1"."rowlimit_$_0" DESC;

    DB <> 바이올린 데모

    확장 된 SQL 텍스트를 가져 오기 :

    declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS ONLY',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

    과의 관계는 RANK로 확장됩니다

    declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS WITH TIES',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
              RANK() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rank" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$$_rank"<=5 ORDER BY "A1"."rowlimit_$_0" DESC

    오프셋 :

    declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/


SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
             ROW_NUMBER() OVER ( ORDER BY "A2"."VAL") "rowlimit_$$_rownumber" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
       WHERE "A1"."rowlimit_$$_rownumber"<=CASE  WHEN (4>=0) THEN FLOOR(TO_NUMBER(4)) 
             ELSE 0 END +4 AND "A1"."rowlimit_$$_rownumber">4 
ORDER BY "A1"."rowlimit_$_0"
  10. ==============================

    10.당신이 오라클 12C에 있지 않은 경우, 당신은 아래와 같이 TOP N 쿼리를 사용할 수 있습니다.

    당신이 오라클 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;

    여기에 실제로 우리는 인라인 뷰를 생성하고 rnum로 ROWNUM의 이름을 변경합니다. 당신은 필터 기준으로 메인 쿼리에 rnum 사용할 수 있습니다.

  11. ==============================

    11.I'v은 12C에 대해 검증, 오라클 1z0-047 시험을 준비하기 시작 그것을 위해 준비하고있는 동안 나는 'FIRST 사전 추출'로 알려진 12C 향상을 가로 질러왔다 그것은 당신이 사용자의 편의에 따라 행 / 제한 행을 인출 할 수 있습니다. 몇 가지 옵션은 사용할 수 있습니다

    I'v은 12C에 대해 검증, 오라클 1z0-047 시험을 준비하기 시작 그것을 위해 준비하고있는 동안 나는 '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
  12. ==============================

    12.

    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
  13. ==============================

    13.쿼리에 의해 반환 된 각 행의 경우, ROWNUM 의사 열 순서를 나타내는 숫자를 반환하는 테이블이나 조인 행 세트에서하는 오라클 선택 행이다. 선택된 행의 제 1의 ROWNUM을 가지며, 상기 제 2 있고, 등등.

    쿼리에 의해 반환 된 각 행의 경우, ROWNUM 의사 열 순서를 나타내는 숫자를 반환하는 테이블이나 조인 행 세트에서하는 오라클 선택 행이다. 선택된 행의 제 1의 ROWNUM을 가지며, 상기 제 2 있고, 등등.

      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

    나는 오라클 서버에서 이것을 구현 한 11.2.0.1.0

  14. ==============================

    14.오라클에서

    오라클에서

    SELECT val FROM   rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY;

        10
    10
     9
     9
     8

    5 행 선택.

    SQL>

  15. ==============================

    15.이 같은 (테스트되지 않은) 어떤 일을 할 수 있습니다

    이 같은 (테스트되지 않은) 어떤 일을 할 수 있습니다

    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

    분석적 기능 순위는에 의해 순서로 사용할 수도있다.

  16. ==============================

    16.수정과 위의 동일합니다. 하지만 확실히 꽤 작동합니다.

    수정과 위의 동일합니다. 하지만 확실히 꽤 작동합니다.

       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

    솔직히, 더 이상 답변을 사용할 수 있습니다.

    17. from https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 빠른 600K 행에서 10 개 임의의 행을 선택 MYSQL  (0) 2020.03.06
[SQL] 삽입, PostgreSQL의 중복 업데이트에 대한?  (0) 2020.03.06
[SQL] 오류 MySQL의에 쿼리를 실행할 때 ONLY_FULL_GROUP_BY 관련  (0) 2020.03.06
[SQL] 어떻게 매개 변수화 된 SQL 쿼리를 만들려면 어떻게해야합니까? 왜해야 내가?  (0) 2020.03.06
[SQL] 존재하는 경우 MySQL의 테이블 또는 업데이트에 삽입  (0) 2020.03.06
,

티스토리툴바