1, Analysis function

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 figure is an example of three ranking methods

--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;

 

 

 

 

2, Set operation

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!!