MySQL高阶1783-大满贯数量

标题

找出每一个球员赢得大满贯角逐的次数。结果不包含没有赢得角逐的球员的ID 。
结果集 无次序要求 。
准备数据

1. Create table If Not Exists Players (player_id int, player_name varchar(20));
2. Create table If Not Exists Championships (year int, Wimbledon int, Fr_open int, US_open int, Au_open int);
3. Truncate table Players;
4. insert into Players (player_id, player_name) values ('1', 'Nadal');
5. insert into Players (player_id, player_name) values ('2', 'Federer');
6. insert into Players (player_id, player_name) values ('3', 'Novak');
7. Truncate table Championships;
8. insert into Championships (year, Wimbledon, Fr_open, US_open, Au_open) values ('2018', '1', '1', '1', '1');
9. insert into Championships (year, Wimbledon, Fr_open, US_open, Au_open) values ('2019', '1', '1', '2', '2');
10. insert into Championships (year, Wimbledon, Fr_open, US_open, Au_open) values ('2020', '2', '1', '2', '2');

复制代码

1. Championships表

复制代码

1. Players表

复制代码

 分析数据

   类型是行转列 一样平常要使用union（all）
   第一步:将几行转成一列,使用union all

1. select Wimbledon from Championships
2. union all
3. select Fr_open from Championships
4. union all
5. select US_open from Championships
6. union all
7. select Au_open from Championships;

复制代码

第二步:将两张表进行关联

1. select player_id,player_name,count(*) as grand_slams_count
2. from players join
3.     (select Wimbledon from Championships
4.       union all
5.       select Fr_open from Championships
6.       union all
7.       select US_open from Championships
8.       union all
9.       select Au_open from Championships) t1
10.     on t1.Wimbledon = player_id
11. group by player_id, player_name;

复制代码

总结

• 若是行转列,使用union all
  
• 若是列转行, 使用if函数将数据拉宽
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。