1194.锦标赛优胜者

Players 玩家表

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| player_id   | int   |
| group_id    | int   |
+-------------+-------+
player_id 是此表的主键(具有唯一值的列)。
此表的每一行表示每个玩家的组。

Matches 赛事表

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| first_player  | int     |
| second_player | int     | 
| first_score   | int     |
| second_score  | int     |
+---------------+---------+
match_id 是此表的主键(具有唯一值的列)。
每一行是一场比赛的记录，first_player 和 second_player 表示该场比赛的球员 ID。
first_score 和 second_score 分别表示 first_player 和 second_player 的得分。
你可以假设，在每一场比赛中，球员都属于同一组。

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

编写解决方案来查找每组中的获胜者。

返回的结果表单 没有顺序要求 。

返回结果格式如下所示。

示例 1:

输入：
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            |
+------------+--------------+---------------+-------------+--------------+
输出：
+-----------+------------+
| group_id  | player_id  |
+-----------+------------+ 
| 1         | 15         |
| 2         | 35         |
| 3         | 40         |
+-----------+------------+

代码实现：

        

-- 方法1
select distinct group_id  ,player_id
from
(select *,rank() over (partition by group_id order by scores desc,player_id)rn from
    (select
    *,sum(score) over(partition by player_id ,group_id) scores
from
(select distinct
    player_id ,group_id,sum(first_score) over(partition by p.player_id) score
from
    Players p
right join
    Matches m on p.player_id=m.first_player
union all
select distinct
    player_id ,group_id,sum(second_score ) over(partition by p.player_id) score
from
    Players p
right join
    Matches m on p.player_id=m.second_player) t1
)t2) t3
where rn=1;

-- 方法2
with t1 as (
    select
    group_id  ,player_id  ,
    sum(case
        when p.player_id = m.first_player then first_score
        when p.player_id = m.second_player then second_score
    end) as total_score
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 total_score desc,group_id
),
t2 as(
    select *,row_number() over (partition by group_id order by total_score desc,player_id asc ) rn
    from t1
    )
select
    group_id  ,player_id
from t2
where rn=1