1194. Tournament Winners---取每组中的最大值所在的记录

5 篇文章 0 订阅

Table: Players

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| player_id   | int   |
| group_id    | int   |
+-------------+-------+
player_id is the primary key of this table.
Each row of this table indicates the group of each player.

Table: Matches

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| first_player  | int     |
| second_player | int     | 
| first_score   | int     |
| second_score  | int     |
+---------------+---------+
match_id is the primary key of this table.
Each row is a record of a match, first_player and second_player contain the player_id of each match.
first_score and second_score contain the number of points of the first_player and second_player respectively.
You may assume that, in each match, players belongs to the same group.

The winner in each group is the player who scored the maximum total points within the group. In the case of a tie, the lowest player_id wins.

Write an SQL query to find the winner in each group.

The query result format is in the following example:

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

Matches table:
+------------+--------------+---------------+-------------+--------------+
| 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 table:
+-----------+------------+
| group_id  | player_id  |
+-----------+------------+ 
| 1         | 15         |
| 2         | 35         |
| 3         | 40         |
+-----------+------------+

解题思路:

1、通过union all 得到每个player的计分

SELECT DISTINCT player, 
SUM(score) OVER (PARTITION BY player) AS sum_score
FROM
(SELECT first_player AS player, first_score AS score
FROM matches
UNION ALL
SELECT second_player AS player, second_score AS score
FROM matches) t1

2、求每组的最大值,可以通过row_number()排序,取每组中的第一行数据。本例中,join players table,根据每队的分数对player进行排序,使用函数row_number()

SELECT players.group_id, players.player_id, sum_score,
ROW_NUMBER() OVER (PARTITION BY players.group_id ORDER BY sum_score DESC) AS num
FROM tmp, players
WHERE tmp.player = players.player_id

3、最后选出每组row_number() = 1

WITH tmp AS (SELECT DISTINCT player, 
SUM(score) OVER (PARTITION BY player) AS sum_score
FROM
(SELECT first_player AS player, first_score AS score
FROM matches
UNION ALL
SELECT second_player AS player, second_score AS score
FROM matches) t1),
tmp1 AS (SELECT players.group_id, players.player_id, sum_score,
ROW_NUMBER() OVER (PARTITION BY players.group_id ORDER BY sum_score DESC) AS num
FROM tmp, players
WHERE tmp.player = players.player_id)
SELECT group_id, player_id
FROM tmp1
WHERE num = 1

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值