LeetCode618学生地理信息报告

Question：
一所美国大学有来自亚洲、欧洲和美洲的学生，他们的地理信息存放在如下student表中。

create table student618(
name varchar(20),
continent varchar(20));

insert into student618 VALUES 
('Jack','America'),
('Pascal','Europe'),
('Xi','Asia'),
('Jane','America');

写一个查询语句实现对大洲（continent）列的 透视表 操作，使得每个学生按照姓名的字母顺序依次排列
在对应的大洲下面。如图：

image.png

方法一：利用变量@+3次join

select America,Asia,Europe
from 
        (select @num:=@num+1 as id,name as America
         from student618,(select @num:=0) t1
         where continent='America'
         order by name) a 
left join 
         (select @num1:=@num1+1 as id,name as Asia
         from student618,(select @num1:=0) t1
         where continent='Asia'
         order by name) b
on a.id = b.id
left join 
         (select @num2:=@num2+1 as id,name as Europe
         from student618,(select @num2:=0) t1
         where continent='Europe'
         order by name) c
on a.id = c.id

方法二：利用开窗函数row_number和3次join

select America,Asia,Europe
from 
        (select row_number() over(partition by continent order by name) as amid,name as America
        from student618
        where continent='America') a
left join
        (select row_number() over(partition by continent order by name) as asid,name as Asia
        from student618
        where continent='Asia') b
on a.amid = b.asid
left join
        (select row_number() over(partition by continent order by name) as euid,name as Europe
        from student618
        where continent='Europe') c
on a.amid = c.euid

此题的难点：
1、第一次看到输出结果时，第一反应是用case when来作答，但是用如下语句运行后：

select case continent when 'America' then name end as America,
             case continent when 'Europe' then name end as Europe,
             case continent when 'Asia' then name end as Asia
from student618

image.png

在原表中没有多余的列进行group by来对case when后的字段进行聚合，所以，依据case when 一行一行的来执行，就会得到如图所示的结果，非所需答案。

2、在进行变量@解答时，left join后的前后两个表的变量设置不能用同一个变量名称@num，如果用同一个@num的话，join后面的表会在前面的基础上进行变量增加，无法得到想要的自增序列。