LeetCode 【困难】数据库-第1194:锦标赛优胜者

题目

LeetCode 【困难】数据库-第1194:锦标赛优胜者

数据

LeetCode 【困难】数据库-第1194:锦标赛优胜者

结果

LeetCode 【困难】数据库-第1194:锦标赛优胜者

解答

1.因为分了两场,所以把两场连接为一场

select first_player player_id,first_score score from matches #
union all
select second_player player_id,second_score score from matches

LeetCode 【困难】数据库-第1194:锦标赛优胜者

2. 连接players表,按组别和玩家 分组计算得分

select group_id,players.player_id,sum(score) score1 
from players
right join(
            select first_player player_id,first_score score from matches #
            union 
            select second_player player_id,second_score score from matches
           ) a
on a.player_id = players.player_id
group by group_id,player_id
order by group_id

LeetCode 【困难】数据库-第1194:锦标赛优胜者

3.按照组别分组,按分数排名(排名的时候,按照分数和用户id排名)!!!!!!

select group_id,player_id,score1,
       rank() over (partition by group_id order by score1 desc,player_id) rn 
from(
        select group_id,players.player_id,sum(score) score1 
        from players
        right join(
                  select first_player player_id,first_score score from matches #
                  union 
                  select second_player player_id,second_score score from matches
                   ) a
        on a.player_id = players.player_id
        group by group_id,player_id
        order by group_id
    ) b 

LeetCode 【困难】数据库-第1194:锦标赛优胜者

4.筛选出来第一名的人,(按照分数和id)

select group_id,player_id ,rn
from(
    select group_id,player_id,score1,
        rank() over (partition by group_id order by score1 desc,player_id) rn 
    from(
        select group_id,players.player_id,sum(score) score1 
        from players
        right join(
            select first_player player_id,first_score score from matches #
            union 
            select second_player player_id,second_score score from matches
        ) a
        on a.player_id = players.player_id
        group by group_id,player_id
				order by group_id
    )b 
)c
where rn = 1

LeetCode 【困难】数据库-第1194:锦标赛优胜者

上一篇:Python入门基础教程-基础数据类型


下一篇:数字游戏函数自定义版