表: 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 | +---------+------+--------+
进阶:如果不能确定哪个大洲的学生数最多,你可以写出一个查询去生成上述学生报告吗?
解题思路
透视表+派生表+MAX
有点抽象。
先是行转列。我还没学到PIVOT那块,不过用CASE WHEN也能实现行转列。
然后先对各大陆的学生row_number排序,再按id分组的话就能得到每一行的学生,主要是因为输出结果是三列,相当于元组取值是(name,null,null)~(name,name,name)。
再用MAX取最大值。说实话这个我是真没想到。如果元组是(name,null,null),那么用max去空之后就能得到name的值。如果元组是(name,name,name),那么也是用max根据这个name对应的continent再去选出对应的name。
# Write your MySQL query statement below
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 name,continent,
row_number()over(partition by continent order by name ASC) AS rk
FROM Student
)t
GROUP BY t.rk