SQL面试必考——行转列

力扣618 学生地理信息报告

https://leetcode-cn.com/problems/students-report-by-geography/

题目:写一个查询语句实现对大洲(continent)列的 透视表 操作,使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲(America)、亚洲(Asia)和欧洲(Europe)。
student表

namecontinent
JackAmerica
PascalEurope
XiAsia
JaneAmerica

输出:

AmericaAsiaEurope
JackXiPascal
Jane

思路:使用 if 或者 case when 可以实现 行转列,但是会出现大量null。如下:

select case when continent = 'America' then name else null end America,
       case when continent = 'Asia' then name else null end Asia,
       case when continent = 'Europe' then name else null end Europe
from student;

得到

AmericaAsiaEurope
Janenullnull
nullnullPascal
nullXinull
Jacknullnull

使用聚合函数 max 或 min 可以消除null,但只能保留每列的最大或最小值,如下:

select max(case when continent = 'America' then name else null end) America,
       max(case when continent = 'Asia' then name else null end) Asia,
       max(case when continent = 'Europe' then name else null end) Europe
from student;

得到

AmericaAsiaEurope
JaneXiPascal

因此,如果要得到所有结果,可以对student的name进行分组排序,然后再按序列号进行分组聚合。分组排序过程如下:

select row_number() over(partition by continent order by name) rn, name,continent from student

得到

rnnamecontinent
1JackAmerica
2JaneAmerica
1PascalEurope
1XiAsia

再对此表的序号rn进行分组聚合,就能够得到每个序号的最大值了,即所有值都能保留下来,如下:

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

得到

AmericaAsiaEurope
JackXiPascal
Jane
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页