力扣618(MySQL)-学生地理信息报告(困难)

发布时间 2023-04-05 22:48:48作者: 我不想一直当菜鸟

题目:

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

该表没有主键。它可能包含重复的行。
该表的每一行表示学生的名字和他们来自的大陆。

一所学校有来自亚洲、欧洲和美洲的学生。

示例:

student:

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

 解题思路:

行转列---需要使用CASE...WHEN...

①行转列,会出现很多null值

1 select 
2    case when continent ='America' then name else null end as America,
3    case when continent ='Asia' then name else null end as Asia,
4    case when continent ='Europe' then name else null end as Europe
5 from student_618;

 ②使用max()或min()保留出姓名的最大值或最小值,但是每一列只能保存下一个值

1 select 
2    min(case when continent ='America' then name else null end) as America,
3    min(case when continent ='Asia' then name else null end) as Asia,
4    min(case when continent ='Europe' then name else null end) as Europe
5 from student_618;

③通过将studnet 表以continent分组name值进行排序;

1 select name ,continent, row_number() over(partition by continent order by name) as rnk
2 from student_618

最后再按序号进行分组聚合,就能得到每个序号的最小值了,这样所有值都能保留下来。

1 select 
2    min(case when continent ='America' then name else null end) as America,
3      min(case when continent ='Asia' then name else null end) as Asia,
4      min(case when continent ='Europe' then name else null end) as Europe
5 from (
6 select name ,continent, row_number() over(partition by continent order by name) as rnk
7 from student_618
8 ) as temp
9 group by rnk;

 小知识:

①rank() over():相同的序号一样,下一个不同数跳跃序数,例如:1,2,2,4

dense_rank() over():相同的序号一样,下一个不同数连续序数,例如:1,2,2,3

row_number() over():无论相同与否,按顺序排序,例如:1,2,3,4

②case...when...语法: