# $1783. 大满贯数量
# https://leetcode-cn.com/problems/grand-slam-titles/

# SQL架构
Create table If Not Exists Players (player_id int, player_name varchar(20));
Create table If Not Exists Championships (year int, Wimbledon int, Fr_open int, US_open int, Au_open int);
Truncate table Players;
insert into Players (player_id, player_name) values ('1', 'Nadal');
insert into Players (player_id, player_name) values ('2', 'Federer');
insert into Players (player_id, player_name) values ('3', 'Novak');
Truncate table Championships;
insert into Championships (year, Wimbledon, Fr_open, US_open, Au_open) values ('2018', '1', '1', '1', '1');
insert into Championships (year, Wimbledon, Fr_open, US_open, Au_open) values ('2019', '1', '1', '2', '2');
insert into Championships (year, Wimbledon, Fr_open, US_open, Au_open) values ('2020', '2', '1', '2', '2');

# Write your MySQL query statement below
select
    p.player_id,
    p.player_name,
    count(*) as grand_slams_count
from
    Players p
    inner join (
        select
            wimbledon
        from
            Championships
        union
        all
        select
            fr_open
        from
            Championships
        union
        all
        select
            us_open
        from
            Championships
        union
        all
        select
            au_open
        from
            Championships
    ) tmp on p.player_id = tmp.Wimbledon
group by
    p.player_id,
    # sql_mode=only_full_group_by
    p.player_name;

# clean-up
drop table Players;
drop table Championships;