1 题目描述

表: student

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| name        | varchar |
| continent   | varchar |
+-------------+---------+

该表可能包含重复的行该表的每一行表示学生的名字和他们来自的大陆一所学校有来自亚洲, 欧洲和美洲的学生

编写解决方案实现对大洲 (continent) 列的透视表操作, 使得每个 学生 按照姓名的字母顺序依次排列在对应的大洲下面. 输出的标题应依次为 美洲 (America), 亚洲 (Asia) 和欧洲 (Europe)

测试用例的生成保证来自美国的学生人数不少于亚洲或欧洲的学生人数

2 测试用例

输入: Student table:

+--------+-----------+
| name   | continent |
+--------+-----------+
| Jane   | America   |
| Pascal | Europe    |
| Xi     | Asia      |
| Jack   | America   |
+--------+-----------+

输出:

+---------+------+--------+
| America | Asia | Europe |
+---------+------+--------+
| Jack    | Xi   | Pascal |
| Jane    | null | null   |
+---------+------+--------+

3 解题思路

使用 row_number() 对每个大洲分组统计, 按照学生名字进行升序排序

SELECT name,
       continent,
       ROW_NUMBER() OVER (PARTITION BY continent ORDER BY name ASC) AS rk
FROM Student

查询结果

+------+---------+--+
|name  |continent|rk|
+------+---------+--+
|Jack  |America  |1 |
|Jane  |America  |2 |
|Xi    |Asia     |1 |
|Pascal|Europe   |1 |
+------+---------+--+

使用 case when 输出透视表的列

SELECT CASE WHEN continent = 'America' THEN name END AS America,
       CASE WHEN continent = 'Asia' THEN name END    AS Asia,
       CASE WHEN continent = 'Europe' THEN name END  AS Europe,
       rk
FROM (SELECT name,
             continent,
             ROW_NUMBER() OVER (PARTITION BY continent ORDER BY name) AS rk
      FROM Student) AS t;

查询结果

+-------+----+------+--+
|America|Asia|Europe|rk|
+-------+----+------+--+
|Jack   |null|null  |1 |
|Jane   |null|null  |2 |
|null   |Xi  |null  |1 |
|null   |null|Pascal|1 |
+-------+----+------+--+

对 rk 进行分组统计, 分别对 America, Asia, Europe 列的数据使用 max() 找出这个有值的数据例如, 当 rk = 1 时, 需要分别找出所在列的学生姓名, 数据如下

+-------+----+------+--+
|America|Asia|Europe|rk|
+-------+----+------+--+
|Jack   |null|null  |1 |
|null   |Xi  |null  |1 |
|null   |null|Pascal|1 |
+-------+----+------+--+

rk=1 处理后的结果

+-------+----+------+--+
|America|Asia|Europe|rk|
+-------+----+------+--+
|Jack   | Xi |Pascal|1 |
+-------+----+------+--+

最终的 sql

select max(case when continent = 'America' then name end) as America,
       max(case when continent = 'Asia' then name end)    as Asia,
       max(case when continent = 'Europe' then name end)  as Europe
from (select *, row_number() over (partition by continent order by name) rk
      from Student) t
group by rk

查询结果

+-------+----+------+
|America|Asia|Europe|
+-------+----+------+
|Jack   |Xi  |Pascal|
|Jane   |null|null  |
+-------+----+------+

LeetCode--618. 学生地理信息报告

1 题目描述

2 测试用例

3 解题思路