Analysis function of oracle rookie learning-ranking

The Analytical function of oracle Rookie Learning-sorting function

1.row_number: returns a continuous sort, regardless of whether the values are equal or not

2.rank: the order is the same with equal value, and the ordinal value then jumps

3.dense_rank: with equality, the rows are sorted in the same order, and the sequence number is consecutive.

Experimental tables: create table chengji (sno number,km varchar2 (10), score number); insert into chengji values (1 recorder YWP 60); insert into chengji values (2 Med YWP 70); insert into chengji values (2 Med YWP 70); insert into chengji values (3 Med YWH 80); SQL > select * from chengji SNO KM SCORE- 1 YW 60 1 SX 60 1 YY 60 2 YW 70 2 SX 70 3 YW 80 1 YW 60 1 SX 60 1 YY 60 2 YW 70 2 SX 70 SNO KM SCORE- 3 YW 8012 rows selected.SQL > row_number

Format: row_number () over ()

Sorting is similar to ranking. If the values of An and B are both 100, then the sort of An is 1 and the sort of B is 2.

SQL > select sno,km,score,row_number () over (order by score desc) from chengji SNO KM SCORE ROW_NUMBER () OVER (ORDERBYSCOREDESC)-3 YW 80 1 3 YW 80 2 2 YW 70 3 2 YW 70 4 2 SX 70 5 2 SX 70 6 1 SX 60 7 1 YY 60 8 1 SX 60 9 1 YW 60 10 1 YY 60 11 SNO KM SCORE ROW_NUMBER () OVER (ORDERBYSCOREDESC)- -1 YW 60 1212 rows selected.SQL > rank

Ranking is similar to ranking. If the values of An and B are both 100, then the order of An is 1 Magi B, the order of 1 Magi C is 3.

SQL > select sno,km,score,rank () over (order by score desc) from chengji SNO KM SCORE RANK () OVER (ORDERBYSCOREDESC)-3 YW 80 1 3 YW 80 1 2 YW 70 3 2 YW 70 3 2 SX 70 3 2 SX 70 3 1 SX 60 7 1 YY 60 7 1 SX 60 7 1 YW 60 71 YY 60 7 SNO KM SCORE RANK () OVER (ORDERBYSCOREDESC)-1 YW 60 712 rows selected.SQL > dense_rank

Sorting is similar to ranking. If the values of An and B are both 100, then An is sorted as 1 Magi B, and 1 Magi C is sorted as 2.

SQL > select sno,km,score,dense_rank () over (order by score desc) from chengji SNO KM SCORE DENSE_RANK () OVER (ORDERBYSCOREDESC)-3 YW 80 1 3 YW 80 1 2 YW 70 2 2 YW 70 2 2 SX 70 2 2 SX 70 2 1 SX 60 3 1 YY 60 3 1 SX 60 3 1 YW 60 3 1 YY 60 3 SNO KM SCORE DENSE_RANK () OVER (ORDERBYSCOREDESC)- -1 YW 60 312 rows selected.SQL >