Question

618. 学生地理信息报告

English Version

题目描述

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

 

进阶：如果不能确定哪个大洲的学生数最多，你可以写出一个查询去生成上述学生报告吗？

解法

方法一：窗口函数 + GROUP BY

我们可以使用窗口函数 row_number() 来为每个大洲的学生编号，然后使用 GROUP BY 来将同一编号的学生聚合到一行中。

# Write your MySQL query statement below
WITH
  T AS (
    SELECT
      *,
      ROW_NUMBER() OVER (
        PARTITION BY continent
        ORDER BY name
      ) AS rk
    FROM Student
  )
SELECT
  MAX(IF(continent = 'America', name, NULL)) AS 'America',
  MAX(IF(continent = 'Asia', name, NULL)) AS 'Asia',
  MAX(IF(continent = 'Europe', name, NULL)) AS 'Europe'
FROM T
GROUP BY rk;