- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
目录
一,原题力扣链接
二,题干
表:
student
+-------------+---------+ | Column Name | Type | +-------------+---------+ | name | varchar | | continent | varchar | +-------------+---------+ 该表可能包含重复的行。 该表的每一行表示学生的名字和他们来自的大陆。一所学校有来自亚洲、欧洲和美洲的学生。
编写解决方案实现对大洲(continent)列的 透视表 操作,使得每个
学生
按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲(America)、亚洲(Asia)和欧洲(Europe)。
测试用例的生成保证来自美国的学生人数不少于亚洲或欧洲的学生人数。
返回结果格式如下所示。
示例 1:
输入: Student table: +--------+-----------+ | name | continent | +--------+-----------+ | Jane | America | | Pascal | Europe | | Xi | Asia | | Jack | America | +--------+-----------+ 输出: +---------+------+--------+ | America | Asia | Europe | +---------+------+--------+ | Jack | Xi | Pascal | | Jane | null | null | +---------+------+--------+进阶:如果不能确定哪个大洲的学生数最多,你可以写出一个查询去生成上述学生报告吗?
回答:当然可以呀 count呀
三,建表语句
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');
select * from student;
四,分析
题解:
表:地区表
字段:姓名,地区
求:不同地区的学生姓名
mysql的逻辑:
第一步:开一个新列 排序列 rn
with t1 as (
select
*,
row_number() over (partition by continent order by name) as rn
from student
第二步:开三个新列 分别if判断 如果是name就是name 如果不是就是null
with t1 as (
select
*,
row_number() over (partition by continent order by name) as rn
from student
)
# select * from t1;
,t2 as (
select
name, continent,rn,
if(continent='America',name ,null) as 'America',
if(continent='Asia',name ,null) as 'Asia',
if(continent='Europe',name ,null) as 'Europe'
from t1
)
select * from t2;
第三步:以rn分组,取三个地区列的最大值
五,SQL解答
with t1 as (
select
*,
row_number() over (partition by continent order by name) as rn
from student
)
# select * from t1;
,t2 as (
select
name, continent,rn,
if(continent='America',name ,null) as 'America',
if(continent='Asia',name ,null) as 'Asia',
if(continent='Europe',name ,null) as 'Europe'
from t1
)
# select * from t2;
select
max(America) as America,
max(Asia) as Asia,
max(Europe) as Europe
from t2 group by rn;
六,验证
七,知识点总结
- 开窗函数的运用
- if判断函数的运用 转换~
- 分组求最大值 mysql对null值 可以直接分组依次去除改列的多个值
- 这个是一个典型的行列转换的题
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用