本文共 2506 字,大约阅读时间需要 8 分钟。
问:我们开发了一个应用程序,用来记录高尔夫巡回赛中的分数排名。其中我正在使用SQL来写一段代码,以根据分数来对选手进行前三名的排序。
假设表包含以下内容:
create table player(player_id number,tournament_id, score number);
create table (player_id number, handicap number);
create tournament (tournament_id number, 1st_place_dollars number(5,2), 2nd_place_dollars number(5,2), 3rd_place_dollars number(5,2));
选手数可以是从4-100的范围。
得分可以是任意的组合,例如:
Player1, 68 wins 1st
Player2, 69 wins 2nd
Player3, 70 wins 3rd
Player4, 72
etc.
但问题是如果出现分数相同的情况,比如:
example 1
player1 68 wins 1st
player2 69 wins 1/2 of (second + thrid place)
player3 69 wins 1/2 of (second + third place)
player4 70
etc
example 2
player1 68 wins 1st
player2 69 wins 1/3 of (second + thrid place)
player3 69 wins 1/3 of (second + third place)
player4 69 wins 1/3 of (second + third place)
player5 70
etc
example 3
player1 68 wins 1st
player2 69 wins 1/4 of (second + thrid place)
player3 69 wins 1/4 of (second + third place)
player4 69 wins 1/4 of (second + third place)
player5 69 wins 1/4 of (second + third place)
player6 70
etc
example 4
player1 68 wins 1st
player2 69 wins 1/5 of (second + thrid place)
player3 69 wins 1/5 of (second + third place)
player4 69 wins 1/5 of (second + third place)
player5 69 wins 1/5 of (second + third place)
player6 69 wins 1/5 of (second + third place)
player7 70
etc
我现在想把SQL放入到一个存储过程,这倒不难实现,使用IF、ELSEIF、ELSE或者CASE就可以,但是代码量实在太大了。是否有简单的排序方法,能够使用最少的代码就能实现上述功能呢?
答:你可以使用分析排序函数,例如:
create table player(player_id number,tournament_id number, score number);
insert into player values (1,1,68);
insert into player values (2,1,69);
insert into player values (3,1,69);
insert into player values (4,1,69);
insert into player values (5,1,70);
insert into player values (6,1,70);
insert into player values (7,1,71);
insert into player values (8,1,72);
insert into player values (9,1,72);
insert into player values (10,1,73);
insert into player values (11,1,73);
insert into player values (12,1,73);
insert into player values (13,1,74);
insert into player values (14,1,75);
commit;
SQL> select player_id, rank() over (partition by tournament_id order by score) as place
2 from player
3 order by place, player_id ;
PLAYER_ID PLACE
---------- ----------
1 1
2 2
3 2
4 2
5 5
6 5
7 7
8 8
9 8
10 10
11 10
12 10
13 13
14 14
dense_rank函数不会跳过排位,但它允许多个人处于一个排位,依次向后排列,如下所示:
SQL> select player_id, dense_rank() over (partition by tournament_id order by score) as place
2 from player
3 order by place, player_id ;
PLAYER_ID PLACE
---------- ----------
1 1
2 2
3 2
4 2
5 3
6 3
7 4
8 5
9 5
10 6
11 6
12 6
13 7
14 8
转载地址:https://blog.csdn.net/weixin_32290249/article/details/116293976 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!