mysql - SQL对多个组进行排名

对于一个项目，我被要求为一个小型奥运会设计一个数据库。这是我为数据库制作的ER图。



对于必需的查询之一，我需要列出每个人的运动项目，赛事，名称，国家/地区和结果（奖牌）。到目前为止，我唯一遇到的问题是仅基于同一事件中的其他结果对每个结果进行排名（分配GOLD，SILVER或BRONZE）。

到目前为止我的查询：

SELECT cy.countryname COUNTRY,
       c.firstname || ' ' || c.lastname "COMPETITOR",
       s.sportname,
       e.eventname EVENT,
       rs.result
   FROM COUNTRY cy
   JOIN COMPETITOR c ON cy.COUNTRYID = c.COUNTRYID
   JOIN RESULT rs ON c.competitorid = rs.competitorid
   JOIN EVENT e ON rs.eventid = e.eventid
   JOIN SPORT s ON e.sportid = s.sportid
   ORDER BY e.eventname, rs.result;

产生：

UNITED STATES   NORRIS HOLMWOOD SWIMMING        100 METER BUTTERFLY 49.82
ITALY           LEANDRO ROCCO   SWIMMING        100 METER BUTTERFLY 50.65
ARGENTINA       ORFEO SILVA     SWIMMING        100 METER BUTTERFLY 50.69
IRAN            HAMUND NAMAD    SWIMMING        100 METER BUTTERFLY 51.2
CHINA           MU KWOK         SWIMMING        100 METER BUTTERFLY 51.32
RUSSIA          MITRODFAN KRUPIN SWIMMING       100 METER BUTTERFLY 52.01
ARGENTINA       NICOLAO VARELA  TRACK AND FIELD 100 METER DASH      9.76
ITALY           STEFANO PAVONI  TRACK AND FIELD 100 METER DASH      9.98
UNITED STATES   ROBBY TURNBULL  TRACK AND FIELD 100 METER DASH      10.1
RUSSIA          IRINEY POLZIN   TRACK AND FIELD 100 METER DASH      10.35
CHINA           TU JIANG        TRACK AND FIELD 100 METER DASH      10.54
IRAN            SAKHR NAGI      TRACK AND FIELD 100 METER DASH      10.56
UNITED STATES   SCARLETT NOWELL SWIMMING        200 METER BACKSTROKE 116.32
IRAN            MALIKA NEJEM    SWIMMING        200 METER BACKSTROKE 116.88
etc...

如何仅根据同一事件中的其他结果为每个竞争对手分配金牌，银牌，铜牌或无牌？

最佳答案

使用两个变量

SELECT cy.countryname COUNTRY, c.firstname || ' ' || c.lastname "COMPETITOR", s.sportname, e.eventname EVENT, rs.result,
if(@curGame !=s.sportname,@curRank :=0,@curRank)  AS  setrank, @curRank := @curRank + 1 as rank,@curGame :=s.sportname as game_name
FROM COUNTRY cy
JOIN COMPETITOR c ON cy.COUNTRYID = c.COUNTRYID
JOIN RESULT rs ON c.competitorid = rs.competitorid
JOIN EVENT e ON rs.eventid = e.eventid
JOIN SPORT s ON e.sportid = s.sportid,(SELECT @curRank := 0,@curGame := "") r
ORDER BY e.eventname, rs.result;