Ranking function
1. Row Ou number: returns a sequential sort, regardless of whether the values are equal or not
2.rank: rows with equal values have the same order, and the order value then jumps
3. Deny u rank: rows with equal values are ordered the same, and the sequence number is consecutive
Experiment table
create table chengji(sno number,km varchar2(10),score number);
insert into chengji values(1,'YW',60);
insert into chengji values(1,'SX',60);
insert into chengji values(1,'YY',60);
insert into chengji values(2,'YW',70);
insert into chengji values(2,'SX',70);
insert into chengji values(3,'YW',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 80
12 rows selected.
SQL> row_number
Format: row Hou number() over()
Sorting is similar to ranking. If the values of A and B are both 100, then A is sorted as 1, and B is sorted as 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 12
12 rows selected.
SQL> rank
Sorting is similar to ranking. If the values of A and B are 100, then A is 1, B is 1, and 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 7
1 YY 60 7
SNO KM SCORE RANK()OVER(ORDERBYSCOREDESC)
---------- ------ ---------- ----------------------------
1 YW 60 7
12 rows selected.
SQL> dense_rank
Sorting is similar to ranking. If the values of A and B are 100, then A is 1, B is 1, and C is 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 3
12 rows selected.
SQL>