oracle 排序的分析函数,Oracle SQL：使用分析排序函数

问：我们开发了一个应用程序，用来记录高尔夫巡回赛中的分数排名。其中我正在使用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