美文网首页
Leetcode1194. 锦标赛优胜者(困难)

Leetcode1194. 锦标赛优胜者(困难)

作者: kaka22 | 来源:发表于2020-07-19 14:39 被阅读0次

    题目
    Players 玩家表

    +-------------+-------+
    | Column Name | Type  |
    +-------------+-------+
    | player_id   | int   |
    | group_id    | int   |
    +-------------+-------+
    

    玩家 ID 是此表的主键。
    此表的每一行表示每个玩家的组。
    Matches 赛事表

    +---------------+---------+
    | Column Name   | Type    |
    +---------------+---------+
    | match_id      | int     |
    | first_player  | int     |
    | second_player | int     | 
    | first_score   | int     |
    | second_score  | int     |
    +---------------+---------+
    

    match_id 是此表的主键。
    每一行是一场比赛的记录,第一名和第二名球员包含每场比赛的球员 ID。
    第一个玩家和第二个玩家的分数分别包含第一个玩家和第二个玩家的分数。
    你可以假设,在每一场比赛中,球员都属于同一组。

    每组的获胜者是在组内得分最高的选手。如果平局,player_id 最小 的选手获胜。

    编写一个 SQL 查询来查找每组中的获胜者。

    查询结果格式如下所示

    Players 表:

    +-----------+------------+
    | player_id | group_id   |
    +-----------+------------+
    | 15        | 1          |
    | 25        | 1          |
    | 30        | 1          |
    | 45        | 1          |
    | 10        | 2          |
    | 35        | 2          |
    | 50        | 2          |
    | 20        | 3          |
    | 40        | 3          |
    +-----------+------------+
    

    Matches 表:

    +------------+--------------+---------------+-------------+--------------+
    | match_id   | first_player | second_player | first_score | second_score |
    +------------+--------------+---------------+-------------+--------------+
    | 1          | 15           | 45            | 3           | 0            |
    | 2          | 30           | 25            | 1           | 2            |
    | 3          | 30           | 15            | 2           | 0            |
    | 4          | 40           | 20            | 5           | 2            |
    | 5          | 35           | 50            | 1           | 1            |
    +------------+--------------+---------------+-------------+--------------+
    

    Result 表:

    +-----------+------------+
    | group_id  | player_id  |
    +-----------+------------+ 
    | 1         | 15         |
    | 2         | 35         |
    | 3         | 40         |
    +-----------+------------+
    

    审题
    本题就是要求得每个运动员的分数累加 再找出每组分数累加最高的运动员 如果两个运动员分数一致 则选id更小的一个
    要想把所有分数累加 但运动员可能是first_player也可能是second_player 则要把两个都选出用 union all(这里不应该去重)

    (select M.first_player as p_id, M.first_score as p_score
    from Matches as M)
    union all
    (select M.second_player as p_id, M.second_score as p_score
    from Matches as M)
    

    得到如下结果

    +-----------+-------+
    |    p_id   | score |
    +-----------+-------+
    |        15 |     3 |
    |        30 |     1 |
    |        30 |     2 |
    |        40 |     5 |
    |        35 |     1 |
    |        45 |     0 |
    |        25 |     2 |
    |        15 |     0 |
    |        20 |     2 |
    |        50 |     1 |
    +-----------+-------+
    

    然后将上边与Players表连接 得到每个id的分组 再对p_id进行分组 对score进行累计求和

    select P.group_id, P.player_id, sum(tmp.score) as score
    from Players as P
    join ((select M.first_player as p_id, M.first_score as p_score
    from Matches as M)
    union all
    (select M.second_player as p_id, M.second_score as p_score
    from Matches as M)) as tmp
    on P.player_id = tmp.id
    group by tmp.p_id
    -- 应用一下排序  先按组排序  然后按照分数降序排序  再按play_id升序排序(这样相同的分数 则小的id在前)
    order by P.group_id, score desc, P.player_id asc;
    

    得到的结果如下

    +----------+-----------+-------+
    | group_id | player_id | score |
    +----------+-----------+-------+
    |        1 |        15 |     3 |
    |        1 |        30 |     3 |
    |        1 |        25 |     2 |
    |        1 |        45 |     0 |
    |        2 |        35 |     1 |
    |        2 |        50 |     1 |
    |        3 |        40 |     5 |
    |        3 |        20 |     2 |
    +----------+-----------+-------+
    

    其实也可以利用定义变量按照上述方法进行排序后给定rank 把rank为1取出即可(这种方法便于取出指定名次的结果)

    但接下来其实对group_id进行分组 取出group_id, player_id即可

    select A.group_id, A.player_id
    from (select P.group_id, P.player_id, sum(tmp.score) as score
    from Players as P
    join ((select M.first_player as p_id, M.first_score as p_score
    from Matches as M)
    union all
    (select M.second_player as p_id, M.second_score as p_score
    from Matches as M)) as tmp
    on P.player_id = tmp.id
    group by tmp.p_id
    -- 应用一下排序  先按组排序  然后按照分数降序排序  再按play_id升序排序(这样相同的分数 则小的id在前)
    order by P.group_id, score desc, P.player_id asc) as A
    group by A.group_id;
    

    类似的

    先通过一次关联查询把每组每个player的总分查出来

    SELECT 
        group_id,
        player_id,
        SUM(
          (
            CASE
              WHEN player_id = first_player 
              THEN first_score 
              WHEN player_id = second_player 
              THEN second_score 
            END
          )
        ) AS totalScores 
      FROM
        Players p,
        Matches m 
      WHERE p.player_id = m.first_player 
        OR p.player_id = m.second_player 
      GROUP BY group_id,
        player_id 
      ORDER BY group_id,
        totalScores DESC,
        player_id
    

    再通过一次查询得到每组得分最高的,得分一样的取id最小的

    SELECT 
      group_id,
      player_id 
    FROM
      (SELECT 
        group_id,
        player_id,
        SUM(
          (
            CASE
              WHEN player_id = first_player 
              THEN first_score 
              WHEN player_id = second_player 
              THEN second_score 
            END
          )
        ) AS totalScores 
      FROM
        Players p,
        Matches m 
      WHERE p.player_id = m.first_player 
        OR p.player_id = m.second_player 
      GROUP BY group_id,
        player_id 
      ORDER BY group_id,
        totalScores DESC,
        player_id) AS temp 
    GROUP BY group_id 
    ORDER BY group_id,
      totalScores DESC,
      player_id 
    

    相关文章

      网友评论

          本文标题:Leetcode1194. 锦标赛优胜者(困难)

          本文链接:https://www.haomeiwen.com/subject/lqelkktx.html