题目链接：点击打开链接
题目大意：略。
解题思路：解决方案(1) 最后只需要再使用一次 FROM 子句和 GROUP BY 取出每个组的第一条数据即可。
AC 代码
--解决方案(1) SELECTgroup_id, player_idFROM ( SELECTgroup_id, player_id, SUM(score) ASscoreFROM ( --每个用户总的first_scoreSELECTPlayers.group_id, Players.player_id, SUM(Matches.first_score) ASscoreFROMPlayersJOINMatchesONPlayers.player_id=Matches.first_playerGROUPBYPlayers.player_idUNIONALL--每个用户总的second_scoreSELECTPlayers.group_id, Players.player_id, SUM(Matches.second_score) ASscoreFROMPlayersJOINMatchesONPlayers.player_id=Matches.second_playerGROUPBYPlayers.player_id ) sGROUPBYplayer_idORDERBYscoreDESC, player_id) resultGROUPBYgroup_id--解决方案(2) WITHt1AS(SELECTplayer, SUM(score) sumnFROM ( SELECTfirst_playerplayer, first_scorescoreFROMMatchesUNIONALLSELECTsecond_player, second_scoreFROMMatches) tGROUPBYplayer), t2AS(SELECTgroup_id, playerplayer_id, ROW_NUMBER() OVER(PARTITIONBYgroup_idORDERBYsumnDESC, player) rwFROMPlayerspJOINt1ONp.player_id=t1.player) SELECTgroup_id, player_idFROMt2WHERErw=1
