row_number()over(partition by col1 order by col2) means to sort according to col2 within the group, and the value calculated by this function represents the order number (the consecutive unique within the group) after each group is sorted internally. The difference from rownum is that when using rownum for sorting, you add a shoddy rownum to the result set and then sort it, while row_number() sorts first and then calculates the row number after including the sorting clause.
1. Rownum in the oracle
Used to number the rows returned from the query, the first row returned is assigned a 1, the second row is a 2, and so on, this pseudofield can be used to limit the total number of rows returned by the query, and the rownum cannot be prefixed with the name of any table.
1. rownum for query conditions equal to a certain value
If you want to find the first student in the student table, you can use rownum=1 as a condition. However, if you want to find the information of the second student in the student table, you cannot find the data using rownum=2. Because rownum starts from 1, but natural numbers above 1 are considered false when rownum is judged to be equal to yes, so it is impossible to find rownum = n(n>1 natural numbers).
2. Rownum for query conditions greater than a certain value
If you want to find the records from the second row onwards, when using rownum>2 cannot find the records, you can use the following subquery methods to solve the problem. Note that the rownum in the subquery must have an alias, otherwise the record will not be found, because the rownum is not a column of a table, and if there is no alias, it is impossible to know whether the rownum is a column of the subquery or a column of the main query.
3. rownum for query conditions less than a certain value
If you want to find the previous record of the third record, you can get two records when using rownum<3. Obviously, rownum thinks that the condition of rownum < n (natural number of (n>1) is true, so you can find the record.
4. rownum and ordering
The rownum in Oracle is the sequence number generated when retrieving the data, so if you want to specify the rowmun row data for the specified sorted data, you must pay attention to it.
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
3 200003 Li San
2 200002 Wang Er
1 200001 Zhang Yi
4 200004 Zhao Si
It can be seen thatThe rownum is not a sequence number that is generated by the name column。 The system assigns the number of the row of records in the order in which they were inserted, and the rowid is also assigned in order. To solve this problem, subqueries must be used
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200003 Li San
2 200002 Wang Er
3 200001 Zhang Yi
4 200004 Zhao Si
2. Oracle row_number ()
1、row_number() over (order by col_1[,col_2 ...])
According to col_1[,col_2 ...] Sort, returns the sorted result set, and returns a different value for each row.
2、row_number() over (partition by col_n[,col_m ...] order by col_1[,col_2 ...])
First, according to col_n[,col_m ... grouping, and then in each grouping according to the col_1[,col_2 ...] sort (ascending) and finally return the sorted result set
Oracle row_number() instance
1. Use the row_number() function to number, such as
Principle: Sort by PSD first, and after sorting, number each piece of data.
2. Sort the order in ascending order of price, and sort each record with the following code:
3. Count the number of orders placed by each customer recently.
4. When using window opening functions such as over, the execution of grouping and sorting in over is later than the execution of "where, group by, order by".
The above code executes the where clause first, and then numbers each record after execution.
3. The difference between row_number () and rownum
When sorting with rownum, you add a shoddy rownum to the result set and then sort it, while row_number() sorts first and then calculates the row number after including the sorting clause. |
Posted on 9/25/2017 4:00:21 PM
|
|
|
