Detailed explanation of analysis function and set operation in Oracle

1. RANK the same value RANK the same, RANK jump
2. DENSE_RANK the same value rank the same, rank consecutive
3. ROW_NUMBER returns a sequential ranking, regardless of whether the values are equal or not

--The following three analysis functions can be used for ranking.

(1)RANK Same value rank same, rank jump (Example 1,2,2,2,The next five is a direct jump) --Demand: Yes T_ACCOUNT Tabular usenum The fields are sorted. The same values rank the same, ranking jumps select rank() over(order by ac.usenum desc) ,ac.usenum from t_account ac;

--(2)DENSE_RANK The same value rank is the same, ranking is continuous(Same name,Different order down 1,2,2,3,3,4) --Demand: Yes T_ACCOUNT Tabular usenum The fields are sorted. The same values rank the same and rank continuously select dense_rank() over(order by ac.usenum desc),ac.usenum from t_account ac;

--(3)ROW_NUMBER Returns a sequential ranking, whether or not the values are equal (Direct sort order,1,2,3,4,5) --Demand: Yes T_ACCOUNT Tabular usenum Field to sort and return consecutive rankings, regardless of whether the values are equal or not select row_number() over(order by ac.usenum desc),ac.usenum from t_account ac; --use row_number()The paging query implemented by the analysis function is much simpler than the three-tier nested subquery(understand): select * from ( select row_number() over(order by ac.usenum desc)rownumber,ac.usenum from t_account ac )where rownumber>10 and rownumber<=20;

Set operation: combining two result sets into one result set

--(1) What is set operation: combining two result sets into one result set

• --Union all: returns all records of each query without de duplication, including duplicate records.
• --Union: returns all records of each query, excluding duplicate records.
• --Intersect: get the common part and return the records shared by two queries.
• --Minus (subtraction set); the first item minus the common part returns the remaining records after subtracting the records retrieved by the second query from the records retrieved by the first query.
  

Practical examples:

--Prepare data --1.query id Less than 7 select * from t_owners ow where ow.id<=7; --2 query iD Greater than 5 select * from t_owners ow where ow.id >=5; --1.Union ,Allow repetition UNION ALL select * from t_owners ow where ow.id<=7 union all select * from t_owners ow where ow.id >=5; --2.Union,duplicate removal union select * from t_owners ow where ow.id<=7 union select * from t_owners ow where ow.id >=5; --3.intersection , common parts intersect select * from t_owners ow where ow.id<=7 intersect select * from t_owners ow where ow.id >=5; --4.Difference set ,Article 1 minus common parts select * from t_owners ow where ow.id<=7 minus select * from t_owners ow where ow.id >=5;

Note: for set operation, it is allowed to query different tables. As long as the number of fields in the two tables is consistent with the field type, it is OK (the field name is not required to be consistent)

After watching, Congratulations, and know a little bit!!!

The more you know, the more you don't know

~Thank you for reading. Your support is the biggest driving force for my study! Come on, strangers work together and encourage together!!

2 June 2020, 11:46 | Views: 9224