SQL：锦标赛优胜者

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

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

每一行是一场比赛的记录， 
first_player 和 second_player 表示该场比赛的球员 id。
first_score 和 second_score 分别表示 first_player 和 second_player 的得分。
你可以假设，在每一场比赛中，球员都属于同一组。

CREATE TABLE players ( player_id INT, group_id INT );
CREATE TABLE matches (
	match_id INT,
	first_player INT,
	second_player INT,
	first_score INT,
	second_score INT 
);

REPLACE INTO players ( player_id, group_id ) VALUES
( 10, 2 ),( 15, 1 ),( 20, 3 ),( 25, 1 ),( 30, 1 ),( 35, 2 ),( 40, 3 ),( 45, 1 ),( 50, 2 );

REPLACE INTO matches ( match_id, first_player, second_player, first_score, second_score ) VALUES
( 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 );

方法1

求每场比赛，每个选手累计得分，然后筛选出最高分是这个组的最高分

SELECT
	group_id,
	player_id
from 
	(
	SELECT
		group_id,
		player_id,
		rank() over ( PARTITION BY group_id ORDER BY sum( IF ( player_id = first_player, first_score, second_score ) ) DESC, player_id ) rn 
	FROM
		matches a
		LEFT JOIN players b ON a.first_player = b.player_id 
		OR a.second_player = b.player_id 
	GROUP BY
		group_id,
		player_id
	) t
WHERE rn =1