LeetCode 618.学生地理信息报告

数据准备

drop table Student;

Create table If Not Exists Student (name varchar(50), continent varchar(7));
Truncate table Student;
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）列的透视表 操作，
使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。
输出的标题应依次为美洲（America）、亚洲（Asia）和欧洲（Europe）。

输入

输出

方式一:
有缺陷,查询结果会有大量空值

select
       if(continent='America' , name, null) as 'America',
       if(continent='Asia' , name, null) as 'Asia',
       if(continent='Europe' , name, null) as 'Europe'
from Student;

方式二:

1. 对continent进行窗口排序

-- 对continent进行窗口排序
select *,
       row_number() over (partition by continent) as rn1
from Student;

2. 分组聚合

-- 对上一步的新建的列进行分组
-- 使用sum函数聚合,求出每一组中每一列的数据(用来取出空值)
with t1 as (
    select *,
       row_number() over (partition by continent) as rn1
    from Student
)
select
       max(if(continent='America' , name, null)) as 'America',
       max(if(continent='Asia' , name, null)) as 'Asia',
       max(if(continent='Europe' , name, null)) as 'Europe'
from t1
group by rn1;