What is Oracle's interval query statement?

Oracle's interval query statement: 1. "select * from table name where rownum

The operating environment of this tutorial: Windows 10 system, Oracle version 12c, Dell G3 computer.

What is Oracle’s interval query statement?

Oracle uses the rownum keyword to implement this query:

First we Suppose there is a regional information table area, and its table structure is as shown in the figure below:

The data in the table is as shown in the figure below (the result of the select * from area statement) :

1) Query the first 8 records in the table

select * from area where rownum <= 8

The query results are as follows:

2) Query the 2nd to 8th records

For this form of query, Oracle is not as convenient as mysql. It must be implemented using subqueries or set operations. We can use the following three methods to achieve this:

A: select id,province,city,district from (select id,province,city,district,rownum as num from area) where num between 2 and 8;

First get a temporary table based on select id, province, city, district, rownum as num from area. There is a rownum in this temporary table. Column (a pseudo column, similar to

rowid, but different from rowid, because rowid is a physically existing column, that is to say, any table in Oracle has a rowid column, and rownum does not exist physically),

Then query in the temporary table.

B: select * from area where rownum <= 8 minus select * from area where rownum < 2;

Use the set minus operator minus, which returns records that appear in the first select but not in the second select.

C: select id,province,city,district from (select id,province,city,district,rownum as num from area) where num >=2
intersect

select * from area where rownum

Using the set intersection operator intersect, there is a twist here (but this twist realizes that rownum is greater than a certain Number query), it first uses A's method to query to obtain all records with

rownum greater than 2, and then performs an intersection operation with the record set with rownum less than or equal to 8. The results obtained by the three operations are the same, as shown in the figure below:

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of What is Oracle's interval query statement?. For more information, please follow other related articles on the PHP Chinese website!