—Known: Two ranking methods (partitioned and unpartitioned): with and without partition
—Two calculation methods (continuous and discontinuous), corresponding functions: dense_rank, rank
Query the original data: student number, name, subject name and grade
select * from t_score
S_ID | S_NAME | SUB_NAME | SCORE |
1 | 张3 | 语文 | 80.00 |
2 | 李4 | 数学 | 80.00 |
1 | 张3 | 数学 | 0.00 |
2 | 李4 | 语文 | 50.00 |
3 | 张3丰 | 语文 | 10.00 |
3 | 张3丰 | 数学 | |
3 | 张3丰 | 体育 | 120.00 |
4 | 杨过 | JAVA | 90.00 |
5 | mike | c++ | 80.00 |
3 | 张3丰 | Oracle | 0.00 |
4 | 杨过 | Oracle | 77.00 |
2 | 李4 | Oracle | 77.00 |
Query each student subject for Oracle ranking (simple ranking) select sc.s_id,sc.s_name,sub_name,sc.score, Ranking of rank () over (order by score desc) from t_score sc where sub_name=‘Oracle’
S_ID | S_NAME | SUB_NAME | SCORE | 名次 |
4 | 杨过 | Oracle | 77.00 | 1 |
2 | 李4 | Oracle | 77.00 | 1 |
3 | 张3丰 | Oracle | 0.00 | 3 |
Comparison: rank () vs. dense_rank (): Discontinuous and continuous (both simple) rankings select sc.s_id,sc.s_name,sub_name,sc.score, Ranking of dense_rank () over (order by score desc) from t_score sc where sub_name=‘Oracle’
S_ID | S_NAME | SUB_NAME | SCORE | 名次 |
4 | 杨过 | Oracle | 77.00 | 1 |
2 | 李4 | Oracle | 77.00 | 1 |
3 | 张3丰 | Oracle | 0.00 | 2 |
Inquire about the ranking of each student in each subject (regional ranking) select sc.s_id,sc.s_name,sub_name,sc.score, rank() over (partition by sub_name order by score desc) from t_score sc
S_ID | S_NAME | SUB_NAME | SCORE | 名次 |
4 | 杨过 | JAVA | 90.00 | 1 |
4 | 杨过 | Oracle | 77.00 | 1 |
2 | 李4 | Oracle | 77.00 | 1 |
3 | 张3丰 | Oracle | 0.00 | 3 |
5 | mike | c++ | 80.00 | 1 |
3 | 张3丰 | 数学 | 1 | |
2 | 李4 | 数学 | 80.00 | 2 |
1 | 张3 | 数学 | 0.00 | 3 |
3 | 张3丰 | 体育 | 120.00 | 1 |
1 | 张3 | 语文 | 80.00 | 1 |
2 | 李4 | 语文 | 50.00 | 2 |
3 | 张3丰 | 语文 | 10.00 | 3 |
Inquire the top 2 in each subject (ranking by district)
Similar: News table, seek the top 3 news with click-through rate. Commodity list, seeking the top 10 commodities in sales of various categories.
select * from (
select sc.s_id,sc.s_name,sub_name,sc.score,
dense_rank() over
(partition by sub_name order by score desc) Ranking
from t_score sc
) x
where x. Ranking <=2S_ID | S_NAME | SUB_NAME | SCORE | 名次 |
4 | 杨过 | JAVA | 90.00 | 1 |
4 | 杨过 | Oracle | 77.00 | 1 |
2 | 李4 | Oracle | 77.00 | 1 |
3 | 张3丰 | Oracle | 0.00 | 2 |
5 | mike | c++ | 80.00 | 1 |
3 | 张3丰 | 数学 | 1 | |
2 | 李4 | 数学 | 80.00 | 2 |
3 | 张3丰 | 体育 | 120.00 | 1 |
1 | 张3 | 语文 | 80.00 | 1 |
2 | 李4 | 语文 | 50.00 | 2 |
Check the total score of each student select s_id,s_name,sum(score) sum_score from t_score group by s_id,s_name
S_ID | S_NAME | SUM_SCORE |
1 | 张3 | 80.00 |
2 | 李4 | 207.00 |
3 | 张3丰 | 130.00 |
4 | 杨过 | 167.00 |
5 | mike | 80.00 |
Inquire about the ranking of each student according to the total score select x.*, Ranking of rank () over (order by sum_score desc) from ( select s_id,s_name,sum(score) sum_score from t_score group by s_id,s_name ) x
S_ID | S_NAME | SUM_SCORE | 名次 |
2 | 李4 | 207.00 | 1 |
4 | 杨过 | 167.00 | 2 |
3 | 张3丰 | 130.00 | 3 |
1 | 张3 | 80.00 | 4 |
5 | mike | 80.00 | 4 |
Syntax: rank () over (order by sort field order) rank () over (partition by Packet Field order by Sort Field Order) 1. Sequence: ascdesc ranking is business-related: Example: Looking for excellent students: Grade: Descending Late Times: Ascending 2. Partition field: According to what field to partition. Question: What is the difference between partitioning and grouping? Partitioning only ranks the original data (the number of records remains unchanged), Grouping is to aggregate statistics of original data (the number of records becomes smaller, and each group returns one). Note: Aggregation.
Script:
create table t_score
(
autoid number primary key,
s_id number(3),
s_name char(8) not null,
sub_name varchar2(20),
score number(10,2)
);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (8, 1, ' Zhang 3 ', ' Language ', 80);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (9, 2, ' Li 4 ', ' Mathematics ', 80);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (10, 1, ' Zhang 3 ', ' Mathematics ', 0);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (11, 2, ' Li 4 ', ' Language ', 50);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (12, 3, ' Zhang 3 Abundance ', ' Language ', 10);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (13, 3, ' Zhang 3 Abundance ', ' Mathematics ', null);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (14, 3, ' Zhang 3 Abundance ', ' Sports ', 120);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (15, 4, ' Yang Guo ', 'java', 90);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (16, 5, 'mike ', 'c++', 80);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (3, 3, ' Zhang 3 Abundance ', 'oracle', 0);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (4, 4, ' Yang Guo ', 'oracle', 77);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (17, 2, ' Li 4 ', 'oracle', 77);
commit;