888

Có cách nào để làm cho một Oracletruy vấn hoạt động giống như nó chứa một MySQL limitmệnh đề không?

Trong MySQL, tôi có thể làm điều này:

select * 
from sometable
order by name
limit 20,10

để có được hàng thứ 21 đến 30 (bỏ qua 20 hàng đầu tiên, cho 10 hàng tiếp theo). Các hàng được chọn sau order by, vì vậy nó thực sự bắt đầu vào tên thứ 20 theo thứ tự abc.

Trong đó Oracle, điều duy nhất mọi người đề cập đến là rownumcột giả, nhưng nó được đánh giá trước order by đó, có nghĩa là:

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

sẽ trả về một tập hợp ngẫu nhiên gồm mười hàng được sắp xếp theo tên, thường không phải là thứ tôi muốn. Nó cũng không cho phép chỉ định bù.

|
433

Bắt đầu từ Oracle 12c R1 (12.1), có một hàng khoản hạn chế . Nó không sử dụng LIMITcú pháp quen thuộc , nhưng nó có thể thực hiện công việc tốt hơn với nhiều tùy chọn hơn. Bạn có thể tìm thấy cú pháp đầy đủ ở đây .

Để trả lời câu hỏi ban đầu, đây là truy vấn:

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

(Đối với các phiên bản Oracle trước đó, vui lòng tham khảo các câu trả lời khác trong câu hỏi này)


Ví dụ:

Các ví dụ sau được trích dẫn từ trang được liên kết , với hy vọng ngăn chặn sự thối rữa liên kết.

Thiết lập

CREATE TABLE rownum_order_test (
  val  NUMBER
);

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

COMMIT;

Có gì trong bảng?

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.

Nhận Nhàng đầu tiên

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

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

5 rows selected.

Nhận Nhàng đầu tiên , nếu hàng Nthứ có quan hệ, hãy lấy tất cả các hàng được buộc

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.

x% Hàng đầu

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

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

4 rows selected.

Sử dụng một phần bù, rất hữu ích cho phân trang

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.

Bạn có thể kết hợp bù với tỷ lệ phần trăm

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.
|
746

Bạn có thể sử dụng một truy vấn con cho điều này như

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

Cũng đã xem chủ đề Trên ROWNUM và giới hạn kết quả tại Oracle / AskTom để biết thêm thông tin.

Cập nhật : Để giới hạn kết quả với cả giới hạn dưới và giới hạn, mọi thứ sẽ tăng thêm một chút với

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;

(Sao chép từ bài viết AskTom được chỉ định)

Cập nhật 2 : Bắt đầu với Oracle 12c (12.1), có một cú pháp có sẵn để giới hạn các hàng hoặc bắt đầu tại offset.

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

Xem câu trả lời này để biết thêm ví dụ. Cảm ơn Krumia cho gợi ý.

|
  • 1

    Đây chắc chắn là cách để làm điều đó, nhưng hãy lưu ý (như bài viết hỏi tom nói) hiệu suất truy vấn giảm khi mức tối đa của bạn tăng lên. Đây là một giải pháp tốt cho kết quả truy vấn, nơi bạn chỉ muốn xem một vài trang đầu tiên, nhưng nếu bạn đang sử dụng điều này như một cơ chế để mã trang qua toàn bộ bảng, bạn sẽ tốt hơn khi tái cấu trúc mã của mình

    – Hồ Nhân Văn 12:30:53 27/08/2009
  • 1

    +1 phiên bản thấp hơn / cao hơn của bạn thực sự đã giúp tôi giải quyết một vấn đề trong đó một điều khoản rownum giới hạn trên chỉ làm chậm đáng kể truy vấn của tôi.

    – Ngô Trí Thắng 22:21:18 09/08/2011
  • 1

    "Giải pháp phân tích Leigh Riffel chỉ có một truy vấn lồng nhau" là giải pháp.

    – Hoàng Viễn Phương 23:22:07 27/03/2012
  • 1

    Bài viết AskTom có ​​một gợi ý tối ưu hóa cũng sử dụng SELECT / * + FIRST_lawS (n) / a. , rownum rnum Dấu gạch chéo đóng trước phải có dấu hoa thị. SO đang chà nó ra.

    – Ngô Xuân Hạnh 15:34:10 05/03/2013
  • 1

    Lưu ý rằng đối với Oracle 11, một CHỌN bên ngoài với ROWNUM sẽ ngăn bạn gọi xóaRow trên UpdizableResultSet (với ORA-01446) - mong muốn thay đổi R1 12c!

    – Hồ Xuân Thảo 08:34:27 11/05/2015
172

Tôi đã thực hiện một số thử nghiệm hiệu suất cho các phương pháp sau:

Hỏi

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

Phân tích

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

Thay thế ngắn

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

Các kết quả

Bảng có 10 triệu bản ghi, sắp xếp trên một hàng thời gian chưa được xác định:

  • Giải thích kế hoạch cho thấy cùng một giá trị cho cả ba lựa chọn (323168)
  • Nhưng người chiến thắng là AskTom (có phân tích theo sát phía sau)

Chọn 10 hàng đầu tiên đã thực hiện:

  • AskTom: 28-30 giây
  • Phân tích: 33-37 giây
  • Thay thế ngắn: 110-140 giây

Chọn các hàng trong khoảng từ 100.000 đến 100.010:

  • AskTom: 60 giây
  • Phân tích: 100 giây

Chọn các hàng trong khoảng từ 9.000.000 đến 9.000.010:

  • AskTom: 130 giây
  • Phân tích: 150 giây
|
53

Một giải pháp phân tích chỉ với một truy vấn lồng nhau:

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

Rank()có thể được thay thế Row_Number()nhưng có thể trả về nhiều hồ sơ hơn bạn mong đợi nếu có các giá trị trùng lặp cho tên.

|
  • 1

    Tôi thích phân tích. Bạn có thể muốn làm rõ sự khác biệt trong hành vi sẽ là gì giữa Xếp hạng () và Row_Number ().

    – Dương Phúc Hưng 16:53:57 23/01/2009
  • 1

    Thật vậy, không chắc tại sao tôi không nghĩ về các bản sao. Vì vậy, trong trường hợp này nếu có các giá trị trùng lặp cho tên thì RANK có thể cung cấp nhiều bản ghi hơn bạn mong đợi do đó bạn nên sử dụng Row_Number.

    – Hoàng Phượng Uyên 14:11:03 26/01/2009
  • 1

    Nếu đề cập đến rank()nó cũng đáng lưu ý dense_rank(), điều này có thể hữu ích hơn cho việc kiểm soát đầu ra vì số sau không "bỏ qua" các số, trong khi rank()có thể. Trong mọi trường hợp cho câu hỏi row_number()này là phù hợp nhất. Một điều khác không phải là kỹ thuật này có thể áp dụng cho bất kỳ db nào hỗ trợ các chức năng được đề cập.

    – Bùi Hiếu Phong 00:34:39 29/10/2017
28

Trên Oracle 12c (xem mệnh đề giới hạn hàng trong tham chiếu SQL ):

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

    Và tất nhiên, họ đã phải sử dụng một cú pháp hoàn toàn khác so với mọi người cho đến nay

    – Dương Phúc Hưng 01:12:42 25/09/2013
  • 1

    Rõ ràng sau khi ngồi xuống với tất cả các nhà cung cấp khác để đồng ý LIMITtrong SQL: 2008, sau đó họ phải rút một lá khỏi cuốn sách của Microsoft và phá vỡ tiêu chuẩn.

    – Hoàng Phượng Uyên 01:39:16 25/09/2013
  • 1

    Nó điên rồi. Tại sao không bám sát tiêu chuẩn!

    – Bùi Hiếu Phong 10:19:56 19/12/2013
  • 1

    @Derek: Có, không tuân theo tiêu chuẩn là đáng tiếc. Nhưng chức năng mới được giới thiệu trong 12cR1 mạnh hơn chỉ LIMIT n, m(Xem câu trả lời của tôi). Sau đó, một lần nữa, Oracle nên thực hiện LIMIT n, mdưới dạng đường cú pháp, vì nó tương đương với OFFSET n ROWS FETCH NEXT m ROWS ONLY.

    – Lý Công Án 13:54:21 26/09/2014
  • 1

    @ Derek: Thực ra, tôi chỉ nhận thấy nhận xét này trong PostgreSQL thủ postgresql.org/docs/9.0/static/sql-select.html#AEN69535 "Các điều khoản LIMIT và OFFSET là cú pháp PostgreSQL cụ thể, cũng được sử dụng bởi MySQL SQL. : Tiêu chuẩn 2008 đã giới thiệu các mệnh đề OFFSET ... FETCH {FIRST | NEXT} ... cho cùng chức năng ". Vì vậy, GIỚI HẠN không bao giờ là một phần của tiêu chuẩn.

    – Bùi Trí Dũng 00:58:42 21/05/2015
11

Các truy vấn phân trang với thứ tự thực sự khó khăn trong Oracle.

Oracle cung cấp một bút danh ROWNUM trả về một số chỉ ra thứ tự mà cơ sở dữ liệu chọn hàng từ một bảng hoặc tập hợp các khung nhìn được nối.

ROWNUM là một giả danh khiến nhiều người gặp rắc rối. Giá trị ROWNUM không được gán vĩnh viễn cho một hàng (đây là một sự hiểu lầm phổ biến). Nó có thể gây nhầm lẫn khi giá trị ROWNUM thực sự được gán. Giá trị ROWNUM được gán cho một hàng sau khi nó vượt qua các biến vị ngữ bộ lọc của truy vấn nhưng trước khi tổng hợp hoặc sắp xếp truy vấn .

Hơn nữa, giá trị ROWNUM chỉ được tăng lên sau khi được gán.

Đây là lý do tại sao truy vấn followin không trả về hàng nào:

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

Hàng đầu tiên của kết quả truy vấn không vượt qua vị từ ROWNUM> 1, do đó ROWNUM không tăng lên 2. Vì lý do này, không có giá trị ROWNUM nào lớn hơn 1, do đó, truy vấn không trả về hàng nào.

Truy vấn được xác định chính xác sẽ trông như thế này:

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

Tìm hiểu thêm về truy vấn phân trang trong các bài viết của tôi trên blog của Vertabelo :

|
6

Báo cáo CHỌN ít hơn. Ngoài ra, tiêu thụ hiệu suất ít hơn. Tín dụng cho: anibal@upf.br

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

(chưa được kiểm tra) một cái gì đó như thế này có thể thực hiện công việc

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

Ngoài ra còn có thứ hạng chức năng phân tích, mà bạn có thể sử dụng để đặt hàng theo.

|
  • 1

    Điều này sẽ không trả về một hàng đơn vì ROWNUM là một cột trên tập kết quả để điều kiện WHERE cuối cùng sẽ luôn là sai. Ngoài ra, bạn không thể sử dụng ROWNUM và ĐẶT HÀNG B ORNG ĐẶT HÀNG đảm bảo.

    – Lý Bảo Toàn 12:20:36 08/09/2013
  • 1

    Xuất sắc. Hãy để điều này ở đây như một lời cảnh báo cho những người khác.

    – Trịnh Quý Khánh 15:33:25 21/01/2014
3

Trong nhà tiên tri

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

GIÁ TRỊ

    10
    10
     9
     9
     8

5 hàng được chọn.

SQL>

|
  • 1

    Bạn nên xác định rằng điều này áp dụng bắt đầu từ Oracle 12c và bạn sao chép / dán nó từ đâu đó - vui lòng luôn trích dẫn nguồn của bạn.

    – Lý Bảo Toàn 09:55:49 21/08/2014
  • 1

    Nguồn là này @Mat. Và Rakesh, vui lòng cố gắng ít nhất để điều chỉnh câu trả lời cho câu hỏi ban đầu. Tôi cũng đã cung cấp một câu trả lời trích dẫn cùng một nguồn, nhưng tôi đã cố gắng để được toàn diện và trích dẫn nguồn gốc.

    – Trịnh Quý Khánh 14:03:34 26/09/2014
2
select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
 FROM EMP ) EMP  where ROWID=5

lớn hơn thì giá trị tìm ra

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

ít hơn các giá trị tìm ra

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

    Downvote là ROW_NUMBER()giải pháp dựa trên đã được đăng bởi Leigh Riffel. Trong nghiện có lỗi cú pháp trong mã được hiển thị.

    – Lý Bảo Toàn 08:33:12 13/03/2019
2

Nếu bạn không ở trên Oracle 12C, bạn có thể sử dụng truy vấn TOP N như bên dưới.

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

Bạn thậm chí có thể di chuyển điều này từ mệnh đề trong với mệnh đề như sau

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

Ở đây thực sự chúng tôi đang tạo ra một chế độ xem nội tuyến và đổi tên rownum thành rnum. Bạn có thể sử dụng rnum trong truy vấn chính làm tiêu chí lọc.

|
1

Tôi đã bắt đầu chuẩn bị cho kỳ thi Oracle 1z0-047, được xác thực dựa trên 12c Trong khi chuẩn bị cho nó, tôi đã tìm thấy một cải tiến 12c được gọi là 'FETCH FIRST' Nó cho phép bạn tìm nạp các hàng / giới hạn hàng tùy theo sự thuận tiện của bạn. Một số tùy chọn có sẵn với nó

- 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

Thí dụ:

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

Đối với mỗi hàng được trả về bởi một truy vấn, giả danh ROWNUM trả về một số chỉ ra thứ tự mà Oracle chọn hàng từ một bảng hoặc tập hợp các hàng đã nối. Hàng đầu tiên được chọn có ROWNUM là 1, hàng thứ hai có 2, v.v.

  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 

Tôi đã thực hiện điều này trong oraclemáy chủ11.2.0.1.0

|
  • 1

    downvote khi câu hỏi hỏi về việc giới hạn các hàng được đặt hàng và bạn thậm chí không có thứ tự

    – Lý Bảo Toàn 08:23:20 13/03/2019
  • 1

    @PiotrDobrogost Hiểu rằng đó không phải là một nhiệm vụ lớn, từ khóa đặt hàng là phổ biến cho tất cả các rdbms chỉ giới hạn có thay đổi.

    – Trịnh Quý Khánh 08:48:40 13/03/2019

Câu trả lời của bạn (> 20 ký tự)

Bằng cách click "Đăng trả lời", bạn đồng ý với Điều khoản dịch vụ, Chính sách bảo mật and Chính sách cookie của chúng tôi.

Không tìm thấy câu trả lời bạn tìm kiếm? Duyệt qua các câu hỏi được gắn thẻ hoặc hỏi câu hỏi của bạn.