mysql - MySQL中基于排名和权重的选择

Question

如何通过测量下表中金牌 (3)、银牌 (2)、铜牌 (1) 的数量来根据赢得的奖牌数量来选择国家排名:

+-----------+-------------+--------+-----------------+----------------------------+-------------------------------+-----------------------------------------+---------------------------------+-------------------------+
| idRESULTS | STATUS      | MEDALS | EVENTS_idEVENTS | EVENTS_ATHLETES_idATHLETES | EVENTS_ATHLETES_TEAMS_idTEAMS | EVENTS_ATHLETES_TEAMS_COUNTRY_idCOUNTRY | EVENTS_VARIOUS_SPORTS_SPORTS_ID | awarded_medals_medal-id |
+-----------+-------------+--------+-----------------+----------------------------+-------------------------------+-----------------------------------------+---------------------------------+-------------------------+
| results1  | DID-NOT-WIN | SILVER | TEN             | MS                         | RUS-WTA                       | RUS                                     | WOMENS_TENNIS                   |                       2 |
| results1  | WON         | GOLD   | TEN             | VW                         | USA-WTA                       | USA                                     | WOMENS_TENNIS                   |                       3 |
| results2  | DID-NOT-WIN | BRONZE | ATH             | JG                         | USA-TF-MEN                    | USA                                     | TRACK-AND-FIELD                 |                       3 |
| results2  | WON         | GOLD   | ATH             | UB                         | JAM-TF-MEN                    | JAM                                     | TRACK-AND-FIELD                 |                       1 |
| results3  | WON         | GOLD   | TEN-DOUBLE      | SW                         | USA-WTA                       | USA                                     | WOMENS_TENNIS                   |                       3 |
| results3  | WON         | GOLD   | TEN-DOUBLE      | VW                         | USA-WTA                       | USA                                     | WOMENS_TENNIS                   |                       1 |
+-----------+-------------+--------+-----------------+----------------------------+-------------------------------+-----------------------------------------+---------------------------------+-------------------------+

我有下面的代码，但这不会产生预期的输出？

select EVENTS_ATHLETES_TEAMS_COUNTRY_idCOUNTRY,MEDALS
 from results
 group by EVENTS_ATHLETES_TEAMS_COUNTRY_idCOUNTRY

Answer 1

试试这个!

select EVENTS_ATHLETES_TEAMS_COUNTRY_idCOUNTRY, rank() over (Order by Score desc) as Ranking
from
(
    select EVENTS_ATHLETES_TEAMS_COUNTRY_idCOUNTRY, sum(
        case when MEDALS='GOLD' then 3 --assigning gold a value of 3
        when MEDALS='SILVER' then 2 --assigning silver a value of 2
        when MEDALS='BRONZE' then 1 --assigning bronze a value of 1
        end) as Score
    group by 1
) Scores

您在第一个中所做的主要事情只是拉动 EVENTS_ATHLETES_TEAMS_COUNTRY_idCOUNTRY 和 MEDALS 的所有独特组合，这不会分配分数。如果您有兴趣分别对每一项进行排名，您可以相应地更改案例中的值。