我正在使用 Apache Derby 10.10。
我有一个参与者列表,并想要计算他们在其所属国家的排名,如下:
| Country | Participant | Points | country_rank |
|----------------|---------------------|--------|--------------|
| Australia | Bridget Ciriac | 1 | 1 |
| Australia | Austin Bjorklun | 4 | 2 |
| Australia | Carrol Motto | 7 | 3 |
| Australia | Valeria Seligma | 8 | 4 |
| Australia | Desmond Miyamot | 27 | 5 |
| Australia | Maryjane Digma | 33 | 6 |
| Australia | Kena Elmendor | 38 | 7 |
| Australia | Emmie Hicke | 39 | 8 |
| Australia | Kaitlyn Mund | 50 | 9 |
| Australia | Alisia Vitaglian | 65 | 10 |
| Australia | Anika Bulo | 65 | 11 |
| UK | Angle Ifil | 2 | 1 |
| UK | Demetrius Buelo | 12 | 2 |
| UK | Ermelinda Mell | 12 | 3 |
| UK | Adeline Pee | 21 | 4 |
| UK | Alvera Cangelos | 23 | 5 |
| UK | Keshia Mccalliste | 23 | 6 |
| UK | Alayna Rashi | 24 | 7 |
| UK | Malinda Mcfarlan | 25 | 8 |
| United States | Gricelda Quirog | 3 | 1 |
| United States | Carmina Britto | 5 | 2 |
| United States | Noemi Blase | 6 | 3 |
| United States | Britta Swayn | 8 | 4 |
| United States | An Heidelber | 12 | 5 |
| United States | Maris Padill | 21 | 6 |
| United States | Rachele Italian | 21 | 7 |
| United States | Jacquiline Speake | 28 | 8 |
| United States | Hipolito Elami | 45 | 9 |
| United States | Earl Sayle | 65 | 10 |
| United States | Georgeann Ves | 66 | 11 |
| United States | Conchit Salli | 77 | 12 |
该模式看起来像这样(sqlfiddle):
create table Country(
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
name varchar(255),
PRIMARY KEY (id)
);
create table Team(
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
country_id int not null,
PRIMARY KEY (id),
FOREIGN KEY (country_id) REFERENCES Country(id)
);
create table Participant(
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
team_id int not null,
name varchar(100),
points int,
PRIMARY KEY (id),
FOREIGN KEY (team_id) REFERENCES Team(id)
);
这是我尝试过的方法:
select
Country.name,
Participant.name,
Participant.points,
ROW_NUMBER() OVER(order by Country.name, Participant.points) as country_rank
from Country
join Team
on Country.id = Team.country_id
join Participant
on Team.id = Participant.team_id;
但根据Apache Derby文档,OVER()语句不接受任何参数。
是否有人知道如何实现国家排名?