108，SQL训练之，力扣，618. 学生地理信息报告

凡梦_leo

于 2024-09-28 00:06:49 发布

阅读量829

点赞数 7

分类专栏： SQL实战训练文章标签： leetcode sql python pandas 大数据数据库算法

本文链接：https://blog.csdn.net/qq_55006020/article/details/142604799

版权

SQL实战训练专栏收录该内容

150 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新
练习：知识的实践应用

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

表： 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;