一、题目
一所美国大学有来自亚洲、欧洲和美洲的学生，他们的地理信息存放在如下 student 表中。
--------- ------------
| name | continent |
--------- ------------
| Jack | America |
| Pascal | Europe |
| Xi | Asia |
| Jane | America |
--------- ------------
写一个查询语句实现对大洲（continent）列的透视表操作，使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲（America）、亚洲（Asia）和欧洲（Europe）。数据保证来自美洲的学生不少于来自亚洲或者欧洲的学生。
对于样例输入，它的对应输出是：
代码语言：javascript复制 ---------- ------- ---------
| America | Asia | Europe |
---------- ------- ---------
| Jack | Xi | Pascal |
| Jane | NULL | NULL |
---------- ------- ---------
**进阶：**如果不能确定哪个大洲的学生数最多，你可以写出一个查询去生成上述学生报告吗？
二、分析
本题属于行转列的题目，但是本题有意思的点在于没有行id，也不关注顺序。因为没有id所以没有办法关联，这也是这个题目中的难点
维度 | 评分 |
|---|---|
题目难度 | ⭐️⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️ |
三、SQL
1.常规解法-开窗函数
使用row_number开窗，给出每个大洲不同学生一个id，每个大洲从1开始计数,这个id也是行id。根据该id分组可以计算出所有的行。对于进阶问题 则可以顺利解决。人数最多的大洲id最大。按照rn分组，进行行转列，但不查询rn即可得到最终结果 执行SQL
代码语言：javascript复制select max(if(continent = 'America', name, null)) America,
max(if(continent = 'Asia', name, null)) Asia,
max(if(continent = 'Europe', name, null)) Europe
from (select *, row_number() over (partition by continent order by name) rn
from t1_student) t
group by rn
SQL结果
代码语言：javascript复制 ---------- ------- ---------
| America | Asia | Europe |
---------- ------- ---------
| Jack | Xi | Pascal |
| Jane | NULL | NULL |
---------- ------- ---------
2.pivot解法
与上面类似，需要使用row_number开窗得到行id，但是行转列的时候使用透视表pivot的方式，不是if或者case when的方式。
这里pivot中非判断条件、非聚合的列即group by的列。
执行SQL
代码语言：javascript复制select America,Asia,Europe
from (select *, row_number() over (partition by continent order by name) rn
from t1_student) t pivot(
max(name) for continent in('America' as America,'Asia' as Asia,'Europe' as Europe)
)
SQL结果
代码语言：javascript复制 ---------- ------- ---------
| America | Asia | Europe |
---------- ------- ---------
| Jack | Xi | Pascal |
| Jane | NULL | NULL |
---------- ------- ---------
四、建表语句和数据插入
代码语言：javascript复制--建表语句
CREATE TABLE t1_student(
name string,
continent string
) COMMENT '学生信息表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY 't'
;
-- 插入数据
insert into t1_student(name,continent)
values
('Jack','America'),
('Pascal','Europe'),
('Xi','Asia'),
('Jane','America');
