SQL statement + select top usage of "Fetch 10th to 20th records from a data table", 10th SQL statements

SQL statement + select top usage of "Fetch 10th to 20th records from a data table", 10th SQL statements

1. First, select top usage:

For more information, see the differences between select top n * from and select * from.

Select * from table -- retrieve all data and return unordered Sets

Select top n * from table -- obtains the first n records based on the data storage sequence in the table and returns the unordered set.

Select * from table order by id desc -- retrieve all data and return the ordered list in reverse order by id

Select top n * from table order by id desc -- first sort by id, then obtain the first n, and return an ordered set sorted by id[NOTE: When sorting by an attribute, it is best that the values of the data column in this sorting attribute are not repeated. If there are duplicate values, the order of the rows with the same sorting attribute values in the result set cannot be determined beforehand]

Chestnuts are as follows ~

 

We use pid as the sorting attribute value. The pid values of 16th rows, 19th rows, and 20th rows are equal. Now the last five records sorted by pid are obtained:

Connection con = new SQLConnection (). getConnection ();
String SQL = "select top 5 * from test order by pid desc ";

System. out. println ("select begins ...");

Statement statement = con. createStatement ();
ResultSet result = statement.exe cuteQuery (SQL );
While (result. next ()){
System. out. println (result. getInt (1) + "," + result. getString (2) + "," + result. getString (3 ));
}
System. out. println ("select ends ...");
Con. close ();
Statement. close ();
Result. close ();
Con = null;
Statement = null;
Result = null;

Result:

Select begins...
3, as, 9
16, tt, 8 [the order of the three cannot be determined beforehand]
19, gh, 8
20, jk, 8
6, bb, 7
Select ends...

2. SQL statement writing similar to "querying 10th to 20th records" === It is often used in paging display.
1) String SQL = "select top 10 * from (select * from test where id <21) m order by m. id desc "; // note that id is the primary key. The first 20 records are retrieved from the subquery, and the first 10 records are obtained from the primary query in descending order, but the results are in descending order, therefore, pay attention
2) query the numbers from m to n:
String SQL = "select top n-m + 1 * from test where (id not in (select top M-1 id from test ))"; // It can be written from the m to n records in the normal order. It is recommended ~
3) [I don't know where the error is. I wrote it out. If a visitor knows it, please leave a message ~]String SQL = "select top 10 * from (select top 20 * from test) a order by. id desc "; I tried the data in the preceding table. The result is: [why is it from 12th to 21st rows? Do not understand] 21, kl, 100
20, jk, 8
19, gh, 8
18, aas, 18
17, qw, 19
16, tt, 8
15, ww, 15
14, hh, 13
13, gg, 16
12, ui, 11

3) I am confused. I found this article in my blog: "Let's Talk About SQL database" simple "SELECT TOP-may have details you have never noticed."

------------------------------- Start of reference -----------------------------------

The data table is as follows:

ID EMPNO NAME AGE

1 26929 Jerome 28
2 28394 Quince 27
3 20983 Green 30
4 27189 Mike 30
5 23167 Arishy 30
6 26371 Yager 29

I wrote an SQL statement to get 3rd or 4 pieces of data and test paging.

Select top 2 * from (select top 4 * from Member) m order by m. RowID desc

Subquery in the middle section: select top 4 * from Member

Obtained:

1 26929 Jerome 28
2 28394 Quince 27
3 20983 Green 30
4 27189 Mike 30

However, the results of the entire SQL statement are: [I have encountered such a problem, but I don't know why ....]

5 23167 Arishy 30
6 26371 Yager 29

Select top 2 * from (select top 4 * from table) m order by m. id desc ----- after scanning the table, first in descending order and then take 2 rows in the 4 rows [a bit of doubt, not after scanning the table -- Take 4 rows -- in descending order -- Take 2 rows ??]

Select top 2 * from (select top 4 * from table order by id asc) m order by m. id desc ----- after scanning the table, take four rows in ascending order and then two rows in descending order.

The problem involves subqueries in SQL:

The table that appears in the from clause is called a derived table. The derived table is virtual and is not physically specific, that isWhen compiling

(Select top 2 * from (select top 4 * from table) m order by m. id

Desc), external queries and internal queries are merged, and a plan is generated.

(Note:Generally, order by is not allowed in a derived table unless top is specified,That is to say, select top

2 * from (select * from zhuisuo order by id asc) m order by m. id desc statement is not

Executable ). A derived table is a virtual table to be referenced outside,Order by returns a cursor instead of a table.. Therefore, only use order by is restricted.However, why can I use top and order by again? This is because top can select a specified number from the cursor returned by order by to generate a table and return it.

Next, let's give an example of top-level precautions.

1. Using top to return random rows, many people will think of using the RAND function to obtain such a statement.

Select top 4 id, name from table order by rand ();

After multiple queries, you will be disappointed to find that it does not return random rows. This is because each query only calls it once instead

Line call it once.

2. Pay attention to the use of top in insert. The correct method for inserting top in flashback should be:

Insert into table

Select top (4) * from table order by id desc

------------------------------ End of reference ----------------------------

For details, refer to the original blog. I still haven't figured out the details about top. I'll go over it later-I'll summarize it with more practices.