618. 学生地理信息报告（难度：困难）

题目描述

（通过次数2,464 | 提交次数3,656，通过率67.40%）

表：student
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| name        | varchar |
| continent   | varchar |
+-------------+---------+
该表没有主键。它可能包含重复的行。
该表的每一行表示学生的名字和他们来自的大陆。
一所学校有来自亚洲、欧洲和美洲的学生。

写一个查询语句实现对大洲（continent）列的透视表操作，使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲（America）、亚洲（Asia）和欧洲（Europe）。
测试用例的生成使得来自美国的学生人数不少于亚洲或欧洲的学生人数。

查询结果格式如下所示。
示例 1:
输入: 
Student table:
+--------+-----------+
| name   | continent |
+--------+-----------+
| Jane   | America   |
| Pascal | Europe    |
| Xi     | Asia      |
| Jack   | America   |
+--------+-----------+
输出: 
+---------+------+--------+
| America | Asia | Europe |
+---------+------+--------+
| Jack    | Xi   | Pascal |
| Jane    | null | null   |
+---------+------+--------+

进阶：如果不能确定哪个大洲的学生数最多，你可以写出一个查询去生成上述学生报告吗？

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/students-report-by-geography

--测试数据
Create table If Not Exists Student (name varchar(50), continent varchar(7));

insert into Student (name, continent) values ('Jane', 'America');
insert into Student (name, continent) values ('Pascal', 'Europe');
insert into Student (name, continent) values ('Xi', 'Asia');
insert into Student (name, continent) values ('Jack', 'America');

解题思路

题目要求实现“对大洲（continent）列的透视表操作”。可能很多小伙伴对什么叫列的透视表操作比较疑惑。

说实在话，强哥第一次看到题目中这句描述的时候，也是一脸懵的。

经过一番确认，其实本质上就是一个行转列的操作。

源表student保存了每个学生对应的洲。题目要求，将每洲作为一个字段，把学生按姓名排序，放在对应的字段中。

对于行转列操作，一般比较常见的实现方法是使用group by + max。但使用group by，就需要最终在同一行返回的学生具有一个相同的值。这样才能把这些学生在同一行返回。

而student表中并不存在这样的值。那么，就需要根据现有数据进行构造。

从结果反推，最终在同一行返回的学生，实际上他们在所在洲下的所有学生中，排名序号是相同的。

于是，问题转化为根据洲分组，再按学生姓名排序取序号，最后按序号分组处理。而取每个学生分组后的序号，可以使用开窗函数轻易的实现。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

select
    max(case continent when 'America' then name else null end) America,
    max(case continent when 'Asia' then name else null end) Asia,
    max(case continent when 'Europe' then name else null end) Europe
from (
    select
        *,
        row_number() over(partition by continent order by name) as rn
    from student
) a
group by rn;