学生地理信息报告:实现对大洲(continent)列的透视表操作
题目来源:Leetcode 618
什么叫做对continent列的透视表操作?
即,按照continent列对学生信息进行汇总,
期望输出结果:
对照原数据表,可以明确一个大致的解题思路:行转列
① 尝试行转列
SELECT (CASE WHEN continent='America' THEN name ELSE Null END) AS 'America',
(CASE WHEN continent='Asia' THEN name ELSE Null END) AS 'Asia',
(CASE WHEN continent='Europe' THEN name ELSE Null END) AS 'Europe'
FROM student
输出结果:
看来行转列的思路没错,可是比我们的预期结果要多了那么一些null...
② 尝试聚合行
SELECT MAX((CASE WHEN continent='America' THEN name ELSE Null END)) AS 'America',
MAX((CASE WHEN continent='Asia' THEN name ELSE Null END)) AS 'Asia',
MAX((CASE WHEN continent='Europe' THEN name ELSE Null END)) AS 'Europe'
FROM student
输出结果:
Umm...好像比我们的预期结果又少了点什么....
MAX只能取到最大值,所以Jack...丢了...
③ 尝试以序列来分组聚合
SELECT name,continent,
IF(@con=continent,@row_num:=@row_num+1,@row_num:=1) rank,
@con:=continent
FROM student,(SELECT @con:=null,@row_num:=1) a
ORDER BY continent,name
输出结果:
Jack and Jane,有点完美。(千万记得order by,不然...)
④ 完整步骤
SELECT MAX((CASE WHEN continent='America' THEN name ELSE Null END)) AS 'America',
MAX((CASE WHEN continent='Asia' THEN name ELSE Null END)) AS 'Asia',
MAX((CASE WHEN continent='Europe' THEN name ELSE Null END)) AS 'Europe'
FROM (
SELECT name,continent,
IF(@con=continent,@row_num:=@row_num+1,@row_num:=1) rank,
@con:=continent
FROM student,(SELECT @con:=null,@row_num:=1) a
ORDER BY continent,name) b
GROUP BY rank
输出结果:
DONE !